Dlookup for previous record

[sargon]

I try to fill the empty records whit the last record that in not null. For this I use this formula:

Expr1: IIf([Rate] Is Null;DLookUp("Rate";"tblRate");[Rate])

But at the third record, it fills not from second record (0.00), but from first (0.20)

 

[GaP42]

Where there are more than one record satisfying the criteria in DLOOKUP, the first record is returned - not the immediate prior record
Or a random value from the field: see DLOOKUP
You might need to use some sort of sort, to then get the value from the previous record based upon the sort.

 

[sargon]

I think the only sort could be SD (date format)...

Expr1: IIf([Rate] Is Nul;DLookUp("Rate";"Rate";"SD < #" & Format$([SD];"dd.mm.yyyy") & "#");[Rate])

 

[arnelgp]

Your SD is String.
You need to convert it to Date (CDate() function).
Also your Format$() is wrong.
Either it is in American date format or ISO date format.
I already give you the query before, you just need to modify it.

 

[cheekybuddha]

... "SD < #" & Format$([SD];"dd.mm.yyyy") & "#") ...

Make this:

... "SD < #" & Format$([SD];"yyyy\-mm-\dd") & "#") ...

It does not matter that you use dd.mm.yyyy as your local date format. SQL only understands yyyy-mm-dd or mm/dd/yyyy.

 

[RogerCooper]

You should add an autonumber field to your table. That would make figuring out the previous record easy.

 

[cheekybuddha]

You should add an autonumber field to your table. That would make figuring out the previous record easy.

That would make figuring out the previous record easyier.

But it would depend on all records being entered in date order, without missed or 'forgotten' records being entered after others subsequent ones have already been entered - which can't always be guaranteed!

 

[sargon]

I took into account your advices and I succeeded in this way:

1) I create ID (autonumbering):
ID: DCount("Start_date";"Query1";"Query1.Start_Date < #" & Format$([Query1].[Start_Date];"yyyy-mm-dd") & "#")+1

2) xv: IIf([Rate] Is Nul;DLookUp("Rate";"Query2";"[ID]=" & [ID]-1);[Rate]), but this left null rows after two consecutive null Rate,

3) and I solve this problem using:
xy: IIf([xv] Is Nul;DLookUp("xv";"Query2");[xv])

Can those three be compressed into one big code line?

 

[Gasman]

Yes. Create a simple function that use all three.