copy field above if current field is null

[koboabe]

Is there a way to copy the data from a field above the field I am in (Access 2000 query) only if the current field is empty. And then when the current field has a new value in the field above, copy that data? For example, records as follows, and I want to copy "cats" until it becomes "dogs", then copy "dogs" until it becomes "parrots", etc.

cats blue
black
orange
brown
white
dogs black
brown
parrots green
red
blue

I do not know any programming language, but thought I could do an IF statement in the query, like If animal name is null, Ctrl + ", animal name. Is it possible? Thanks.

[nico5038]

Can be done with a query when using a trick.
1) Add an autonumber ID field with increment +1 to your table
2) Create a query like:

SELECT [ID]+1 AS ID1, Table1.Animal, Table1.Color
FROM Table1;

This will "fool" Access when you JOIN the table by the ID / ID1 field
3) Create a query like:

UPDATE Table1 INNER JOIN qryTable1 ON [Table1].[ID]=[qryTable1].ID1 SET Table1.Animal = [qryTable1].[animal]
WHERE ((([Table1].[Animal]) Is Null));

This will do the work as the animal from the previous row will be replicated.

Getting the idea ?

Nic;o)

[NeoPa]

You have (accidentally) posted this question in the Access Articles section. This is NOT an article.
I'm moving this to the main Access questions forum.

MODERATOR.