Update query to fill blank fields with corresponded value

[George82]

I am relatively new to Access and I want to make an update query that will fill blank records of a field with the corresponded value based on another field.

Let me give an example to understand what I mean.

Table:
ID COLOR
0025 black
0036 white
0074 red
0025 "blank"
0025 "blank"
0036 "blank"

If I enter in “ID” field the same value as above, see “0025 & 0036” I want the expected result to be:

Table:
ID COLOR
0025 black
0036 white
0074 red
0025 black
0025 black
0036 white

I tried to do this with the following:
DLookUp("COLOR";"table name";"ID=" & [ID])
But sometimes it fills blank records with values and sometimes it turns records with values to blanks.
How can I ensure that only blank records will be field and not the other way around?

Any help will be much appreciated.
Thanks.

 

[vbaInet]

Look into the Nz() function:

http://www.techonthenet.com/access/functions/advanced/nz.php

Nz(DLookUp("COLOR";"table name";"ID=" & [ID]))

and you will need to add a second criteria to say Color is not null.

Plus, Color is a reserved keyword for Access/VBA adn shouldn't be used as field name.

 

[George82]

Thank you for your reply.

I used Nz function and set “Color” is NOT NULL, but what it does now is turn the records with values to blanks.

Ps. “Color” I used to describe my example. Those are not the actual names of my fields.

 

[vbaInet]

What I gave you wasn't the exact syntax. It was only to point you in the right direction:

Nz([Color], DLookUp("COLOR";"table name";"ID=" & [ID] & " AND Not [Color] Is Null"))

 

[Privateer]

George82,
For this to work you need to organize your data into two tables. The color table will have "ColorID as an autonumber field, "ColorCode" as text because of the leading zeros, and "Color" which has the names. Something like this.
ColorID ColorCode Color
1 0025 Black
2 0036 White
In the other table that has the data, you should have a ColorID field as a long integer with the 1,2,3, numbers there. Then, a simple query with both tables in it and a line drawn between the two ColorID fields, relating them on that field and you pull the color field. Now open the query and the colors names will be there. In general, you don't store text in a table if you can normalize it into another table. Hope this helps,
Privateer

 

[George82]

Thanks you very much for your replies!
It worked with the “[FONT=&quot]AND Not[Color]Is Null” statement. [/FONT]

 

[vbaInet]

Glad we could help.