Add data from previous record if blank

[rose dawsen]

I have a table with "Date" and "Rate" fields. Not every record contains a value in the "Rate" field. is there a query that would insert the value from previous record in case the "Rate" field is null?
The sample table looks like this:

Date;Rate
20070101;100
20070102;
20070103;
20070104;200
20070105;205
20070106;206
20070107;
20070108;195

The result schould look like this:

Date;Rate
20070101;100
20070102;100
20070103;100
20070104;200
20070105;205
20070106;206
20070107;206
20070108;195

i use Microsoft Office Access 2003
Thank you

[Richard Rost]

You could use the DMAX function to figure out the rate for the previous date that's less than the current rate.

MaxRate: DMAX("Rate","MyTable","Date<$" & Date & "$")

^^ Those dollar signs "$" are supposed to be pound (number) signs - you know, SHIFT-3, but the editor isn't letting me type them in for some reason.

That will give you the rate from the last date, which you could then fill in to the NULL fields using an update query and an IIF statement.

Richard Rost
AccessLearningZone.com