By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,711 Members | 2,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,711 IT Pros & Developers. It's quick & easy.

copy field above if current field is null

P: 1
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
dogs black
parrots green

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.
Jul 3 '07 #1
Share this Question
Share on Google+
2 Replies

Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT [ID]+1 AS ID1, Table1.Animal, Table1.Color
  2. FROM Table1;
This will "fool" Access when you JOIN the table by the ID / ID1 field
3) Create a query like:
Expand|Select|Wrap|Line Numbers
  1. UPDATE Table1 INNER JOIN qryTable1 ON [Table1].[ID]=[qryTable1].ID1 SET Table1.Animal = [qryTable1].[animal]
  2. WHERE ((([Table1].[Animal]) Is Null));
This will do the work as the animal from the previous row will be replicated.

Getting the idea ?

Jul 3 '07 #2

Expert Mod 15k+
P: 31,489
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.

Jul 3 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.