Simple Update Query returns Error Message

[Guest]

Back to my database of records imported from Outlook.

This time I want to update the Department field with some descriptions such
as Real Estate. I enter the Department field into the grid. In the Criteria
row under Department, I enter Is Null, in the Update To row, I want to enter
RE Estate,Appraisals. Access returns two error
messages. The first regards using periods and commas. When I remove the
comma, the second states You can use the Is operator only in an expression
with Is Null or Not Null. Well, I thought this is what I did when I entered
Is Null in the criteria row.

Why is this happening? Would it have something to do with Allow Zero
Length? What do I need to do to fix?

Thank you.

 

[Marshall Barton]

Back to my database of records imported from Outlook.

This time I want to update the Department field with some descriptions such
as Real Estate. I enter the Department field into the grid. In the Criteria
row under Department, I enter Is Null, in the Update To row, I want to enter
RE Estate,Appraisals. Access returns two error
messages. The first regards using periods and commas. When I remove the
comma, the second states You can use the Is operator only in an expression
with Is Null or Not Null. Well, I thought this is what I did when I entered
Is Null in the criteria row.

Why is this happening? Would it have something to do with Allow Zero
Length? What do I need to do to fix?

I think you need to use quotes in the Update To
"RE Estate,Appraisals."

 

[Guest]

I did try this using quotes before posting. Just tried it again. Access
returns the message, "You are about to update 0 rows..." There are thousands
of blank records in this field. Thanks

 

[John W. Vinson]

I did try this using quotes before posting. Just tried it again. Access
returns the message, "You are about to update 0 rows..." There are thousands
of blank records in this field. Thanks

Please post the entire SQL view of the query. Are any of the fields you're
trying to update Lookup fields? If so, they don't contain what they appear to
contain and can cause wierd error messages!

John W. Vinson [MVP]

 

[Marshall Barton]

It could be that you have not dealt with the consequences of
using AllowZeroLength correctly, which comes down to what
you think a "blank" is. Tell us what the field has for its
Required and AllowZeroLength properties along with a
Copy/Paste of your query's SQL view.

 

[Guest]

Thank you for your response.

The SQL View:

UPDATE BusFaxNosTABLE SET BusFaxNosTABLE.Department = "RE Estate, Appraisal"
WHERE (((BusFaxNosTABLE.Department) Is Null));

Required: No
Allow Zero Length: Yes

Thanks again.

 

[John Spencer]

Try testing for null and for zero length strings.

UPDATE BusFaxNosTABLE
SET BusFaxNosTABLE.Department = "RE Estate, Appraisal"
WHERE BusFaxNosTABLE.Department Is Null or BusFaxNosTABLE.Department= ""

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

Thank you for your response.

The SQL View:

UPDATE BusFaxNosTABLE SET BusFaxNosTABLE.Department = "RE Estate, Appraisal"
WHERE (((BusFaxNosTABLE.Department) Is Null));

Required: No
Allow Zero Length: Yes

That's likely the problem. Allow Zero Length is very tricky: a field can be
NULL ("this value is undefined, unspecified") or it can be "" (this value is
precisely defined, it is an empty string"). If the Department field contains
"" then it is NOT null and your query will not return any records.

Try

UPDATE BusFaxNosTABLE SET BusFaxNosTABLE.Department = "RE Estate, Appraisal"
WHERE (((BusFaxNosTABLE.Department)& "" = ""));

That is: concatenate an empty string to Department; if Department is null *or*
an empty string, that concatenation will be an empty string. Compare the
result with an empty string.

If your intention here is to have *two* departments - RE Estate and Appraisal
- you may want to reconsider your table design, though. Storing two different
facts in one field is A Very Bad Idea.

John W. Vinson [MVP]