Update query & null values

[Dave]

I created an update query in an Access database and I want it to take the
information in a specific field (“User IDâ€) 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, 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).

 

[Jerry Whittle]

Instead of Is Null, just try Null. Also make sure that Access isn't putting
quotation marks around it.

You may need a subquery to get it to work correct.

 

[Bob Barrows [MVP]]

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.

 

[Dave]

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â€.

 

[John Spencer]

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â€.

Text or numeric? This is critical information.

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.




--
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.

 

[Bob Barrows [MVP]]

OK, my guess is the fields that look like null to you actually have
zero-length strings (ZLS) in them. Test it for yourself with this union
query:

select count(*) as Nulls from [current] where [Email Address] Is Null
union all
select count(*) as ZLS from [current] where [Email Address] = ""

If they actually have ZLS, then change the criteria accordingly

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".

Text or numeric? This is critical information.


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.





--
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.

 

[Dave]

Thanks so much John. Your solution worked perfectly and didn't require the
alternative solution using the TRIM function.

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â€.

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.