Creating Null fields in Access

**NBVC** · 01-13-2009, 09:47 AM

Hi All...

Probably a simple solution but I can't get it to work...

I have a database that I want to use an Update Query on to change a specific field (Location_ID) that has text in it to a Null (ie. nothing in it).

I tried the double quotes, single quotes and the Null command, but all return a "0 records will be updated" comment....

Am I doing this right?

Please see attached screenshot of my Update Query design.

Thanks.

BTW: My Select Query with the same criteria shows that I have 11 records that should be updated

**Richard Schollar** · 01-13-2009, 10:10 AM

Hi Vittorio

Doing exactly what you have works for me in Access 2002.

Richard

**Dav** · 01-13-2009, 10:10 AM

null should work

If the query is changed to a select query and run, it should return the rows that will be updated. Do you get any rows returned when you try this?

Otherwise I am not sure what the "is not null" condition achieves, it can probably be excluded

Regards

Dav

**Ron Coderre** · 01-13-2009, 10:12 AM

If the target is an Access table, try using Dbl-Quotes (") for text and an asterisk (*) for wildcards.
Example:

Please Login or Register  to view this content.

If it's not an MSA table, the issue maybe syntax specific to the database you're linking to.

Does that help?

**NBVC** · 01-13-2009, 10:22 AM

Thanks for the responses guys...

It seems like it might be the syntax that Ron is talking about that causes the issue....

If I change the criteria of the first field to 'FS0587' (no wildcarding), then it will say it is going to update the 11 records...

But I do want to wildcard it because sometimes the string may be contained within other strings and I want to update those too.

The Table is a link to our database that is on an Oracle server.... using the same syntax and running a Select Query works and shows all records...so why not the Update Query?

**Dav** · 01-13-2009, 10:28 AM

Well if it is the selection try

Like "FS0587"&"*"

Does that work

Regards

Dav

**NBVC** · 01-13-2009, 10:29 AM

Well guys it seems that it should be: Like 'FS0587*'

That one worked as a wildcard.... strange that the % worked on the Select but not the Update...

Thanks to All for the help.

**Richard Schollar** · 01-13-2009, 11:55 AM

Under Tools>Options you should be able to specify ANSI standard SQL in your queries in which case the % should work. I presume it will work even if it is a linked table to Oracle (but I do not know that for sure).