Update null records in a table with the value of the previous reco

[Guest]

For example, if I have a table with the foloowing 3 fields: ID, city, state.
There are no null values for the Id and city fields. But there are some
null values in the state field. If any record contains a null value in the
state field, I would like to populate it with the state value of the record
right above it in the table. Is it possible to do this with a update query?
Or would this require some code? I appreciate any suggestions! Thanks.

 

[John Vinson]

For example, if I have a table with the foloowing 3 fields: ID, city, state.
There are no null values for the Id and city fields. But there are some
null values in the state field. If any record contains a null value in the
state field, I would like to populate it with the state value of the record
right above it in the table. Is it possible to do this with a update query?
Or would this require some code? I appreciate any suggestions! Thanks.

There is no such thing as "right above" in a table. Data in a table is
like potatoes in a sack - there is no usable order of records.

If your ID values are sequential, or at least numeric and ascending,
you can do something snarky like this Update query:

UPDATE mytable
SET State = DLookUp("[State]", "[MyTable]", "[ID] = " & DMax("ID",
"[MyTable]", "[ID] < " & [ID] & " AND [State] IS NOT NULL"))
WHERE State IS NULL;


John W. Vinson[MVP]