Updating Blank Fields

[Guest]

My contact list contains fields including first name, last name, company
name. When input data leaves all three blank, I would like to do an update
query making the first name Any and the last name Employee. How do I do
this? My attempts have failed producing a result set of zero. What I did
was an update query with the words is null in the criteria row. I do not
have training in writing code. Thanks.

 

[John W. Vinson]

My contact list contains fields including first name, last name, company
name. When input data leaves all three blank, I would like to do an update
query making the first name Any and the last name Employee. How do I do
this? My attempts have failed producing a result set of zero. What I did
was an update query with the words is null in the criteria row. I do not
have training in writing code. Thanks.

No code is necessary, in the sense of VBA programming code - only a correct
query. "IS NULL" should have done the job, unless your data was imported from
some other system which put " " (blanks, or zero length strings) in the field
instead of Null.

Please open the query which didn't work in SQL view and post the SQL text
here. That's the real query, and someone should be able to see why it's not
working.

Just off the top of my head, this should work (if you change the table and
field names to match yours):

UPDATE Contacts
SET [First Name] = "Any", [Last Name] = "Employee"
WHERE NZ([First Name], "") = ""
AND NZ([Last Name], "") = ""
AND NZ([Company], "") = "";

Back up your database first of course!

John W. Vinson [MVP]

 

[Guest]

Yes, these contacts were imported from the web. This is the SQL code:

UPDATE LG1LongDistanceTABLE SET LG1LongDistanceTABLE.FirstName = "Any",
LG1LongDistanceTABLE.LastName = "Employee"
WHERE (((LG1LongDistanceTABLE.FirstName) Is Null) AND
((LG1LongDistanceTABLE.LastName) Is Null) AND ((LG1LongDistanceTABLE.Company)
Is Null));

Thank you again.

My contact list contains fields including first name, last name, company
name. When input data leaves all three blank, I would like to do an update
query making the first name Any and the last name Employee. How do I do
this? My attempts have failed producing a result set of zero. What I did
was an update query with the words is null in the criteria row. I do not
have training in writing code. Thanks.

No code is necessary, in the sense of VBA programming code - only a correct
query. "IS NULL" should have done the job, unless your data was imported from
some other system which put " " (blanks, or zero length strings) in the field
instead of Null.

Please open the query which didn't work in SQL view and post the SQL text
here. That's the real query, and someone should be able to see why it's not
working.

Just off the top of my head, this should work (if you change the table and
field names to match yours):

UPDATE Contacts
SET [First Name] = "Any", [Last Name] = "Employee"
WHERE NZ([First Name], "") = ""
AND NZ([Last Name], "") = ""
AND NZ([Company], "") = "";

Back up your database first of course!

John W. Vinson [MVP]

 

[John W. Vinson]

Yes, these contacts were imported from the web. This is the SQL code:

UPDATE LG1LongDistanceTABLE SET LG1LongDistanceTABLE.FirstName = "Any",
LG1LongDistanceTABLE.LastName = "Employee"
WHERE (((LG1LongDistanceTABLE.FirstName) Is Null) AND
((LG1LongDistanceTABLE.LastName) Is Null) AND ((LG1LongDistanceTABLE.Company)
Is Null));

Try

UPDATE LG1LongDistanceTABLE SET LG1LongDistanceTABLE.FirstName = "Any",
LG1LongDistanceTABLE.LastName = "Employee"
WHERE ((NZ((LG1LongDistanceTABLE.FirstName,"")="")) AND
(NZ(LG1LongDistanceTABLE.LastName,"")="") AND
(NZ(LG1LongDistanceTABLE.Company,"")=""));

John W. Vinson [MVP]

 

[Guest]

Please forgive my lack of knowledge here, but please tell me what to do with
the code you've supplied me. How do I enter this into the Update query? For
example, do I put the first line in the Update to: row under FirstName, where
do I put the next several lines? I need to know exactly how to enter this
into the query. Thanks again.

 

[Guest]

Also, tell me why using Is Null in all three fields did not work when I did
it. Thanks.

 

[John W. Vinson]

Please forgive my lack of knowledge here, but please tell me what to do with
the code you've supplied me. How do I enter this into the Update query? For
example, do I put the first line in the Update to: row under FirstName, where
do I put the next several lines? I need to know exactly how to enter this
into the query. Thanks again.

You don't "put that" into the query. That IS the query! SQL is the language of
queries; the query grid *is nothing but a tool* to make it easier to create
SQL.

Create a new Query. Don't add any tables. Select View... SQL. You'll see a big
textbox with

SELECT;

in the upper left. Replace it with this entire SQL text.

I *guess* - I can't see your database so I can't be sure - that these fields
have the "Allow Zero Length" property set, and that they contain an empty
string value "". This is not the same as a NULL, but looks just like a NULL to
the eye - very confusing, and a good reason not to use Allow Zero Length
unless you have very good cause to do so.

John W. Vinson [MVP]

 

[Guest]

Thank you for that information. Yes, I HAVE to use Allow Zero Length set to
Yes in my table setup because I do a lot of append queries in this database.
It's been quite some time since I wrote to these boards regarding the append
problem, but I think it was you who suggested that I set Allow Zero Length to
Yes for this reason. I have had very few Validation Rule problems while
appending tables to one another since. Thanks again.