First I wouldn't use a LEFT JOIN as this makes no sense in the context of what
you are doing. Second it is possible that Email Address is not null but is a
zero-length string.
Try
UPDATE [current] INNER JOIN all_whois_ids ON current.SSN = all_whois_ids.SSN
SET [current].[Email Address] = all_whois_ids.[User ID]
WHERE [current].[Email Address] Is Null OR Current.[Email Address] = ""
If that fails there is one more possibility and that is [Email Address]
contains multiple spaces. You can try the following to handle that remote
possibility.
UPDATE [current] INNER JOIN all_whois_ids ON current.SSN = all_whois_ids.SSN
SET [current].[Email Address] = all_whois_ids.[User ID]
WHERE Trim([current].[Email Address] & "") = ""
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
UserID is a text field.
Here’s the SQL statement:
UPDATE [current] LEFT JOIN all_whois_ids ON current.SSN = all_whois_ids.SSN
SET [current].[Email Address] = all_whois_ids.[User ID]
WHERE (((current.[Email Address]) Is Null));
One table is named “current†and the other is “all_whois_ids†– I’m trying
to put the “User ID†from the “all_whois_ids†field into the “Email Addressâ€
field in the “current†table (without overwriting any that are already in
there).
The criteria field that I mentioned is when you have the query open in
Design View. When I type in “Null†and move out of the field, it changes it
to “Is Nullâ€.
Bob Barrows said:
Dave wrote:
I created an update query in an Access database and I want it to take
the information in a specific field ("User ID")
Text or numeric? This is critical information.
from one table and
put it into a field in another table, but only if that field is null
in the second table. I put "Is null" in the "criteria" field of the
query but when I run it, nothing happens. If I take that out of the
criteria field,
Which criteria field? The field in the grid column of the field to be
updated? Or the field in grid column for the field containing the source
of the user id?
It would have helped if you had shown us the sql statement. Just switch
to SQL View to see the sql statement.
it puts all the User IDs from the first table into
the second, but overwrites anything already in that second table
(which I do not want it to do).
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.