Previous values

K

KentuckyDiane

Is it possible to have a field like a date field and in a report you need to use the previous records date value in a formula in current record.

Example
Line Date Field
1 2/6/04
2 4/6/04

Report would somehow show If line 2's date is greater than line 1's date, then line 2's date otherwise line 1.

Thanks
KentuckyDiane
 
A

Allen Browne

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/?id=210504


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

KentuckyDiane said:
Is it possible to have a field like a date field and in a report you need
Click to expand...
to use the previous records date value in a formula in current record.
Example
Line Date Field
1 2/6/04
2 4/6/04

Report would somehow show If line 2's date is greater than line 1's date,
Click to expand...
then line 2's date otherwise line 1.
 
K

KentuckyDiane

Thank you so much, this may be exactly what I need to get this going.
Thanks - Thanks - Thanks
 
M

Michael Noblet

I have read the support article. Will this work for the
following situation:

I have aexcel output from a software package. The output
leaves lines blank where data is the same.
Patient Age Diagnosis
Dave 12 cold
Stuffed up

Basically I have 2 lines for dave because he has 2
dianosis, but the excel spreadsheet has blank cells except
for the dignosis field. Can I use this type of function
to populate the blank fields within a query?

Thanks in advance for the help.

Mike
 
A

Allen Browne

Michael, I think there are too many things that could go wrong with that.

What we do is to import the data into a temp table with an autonumber. This
guarantees the records can be sorted in the desired order. We then open a
DAO recordset, and loop through the records, copying the data from the
previous record into the subsequent one(s) where the data is known to be the
same (i.e. not validly Null). Once that is done, you can then append the
data to the real Access table. That involves a bit of work, but in the end
you know you have a reliable result.
 
M

Michael Noblet

That makes sense. Since this is slightly new to me can
you push me down the right road on the loop portion of
code?

Mike
 
A

Allen Browne

Michael, there is quite a bit of work in developing that code, and it will
depend on the column names and data from Excel and the field names and data
types of the target columns in Access. Can't write all that for free, so
email me (from the address below) if you do want us to do some work for you.
 
M

Michael Noblet

Unfortunately I do not have the budget for that.

However I have tested the Microsoft article mehtod and I
belive I am on the right track. I wrote a macro that
takes the linked excel spreadsheet and runs an append
query that populates a table with ID numbers. I then
wrote a query to use the dlookup method described in the
article.

Where I am having a problem is that the first line of
blank data populates fine, but some records have more than
1 blank line. I need a little advice on having the
dlookup function look at the previous record until it
finds a non null field. I assume I might be able to do
this at the report level via VB script but am not sure if
we get into the complicated programing we discussed
earlier.

I would appreciate some help but understand if this is to
much for a forum and is better done via a consulting
engagement.

Mike
 
Top