Update Query and Empty Cells

[batwings]

Hi there,

I have an update query that will not update fields that were previously blank. Is it possible to populate these blank fields in the query grid criteria field to "-" and then run my update query?

 

[ajetrumpet]

to update blank fields, have you tried the following syntax in the query SQL?:

UPDATE table SET [myfield] = "whatever"
   WHERE [mycriteriafield] = NULL

 

[namliam]

Why not simply make it update fields that are blank??

 

[batwings]

namliam
that is my problem I did not think it was possible, how would I do that?
In the meantime I will try ajetrumpet's solution
thanks to you both.

 

[namliam]

Well you can update anything... any time any where... given the right statement...

Problem with NULL values is that they never match anything...
ie.
Select * from yourtable where SomeColumn <> "Something"

You might expect that to give you the null values as well... as NULL <> "Something"...
This is not true... this will not return the null values... The only way you can do anything with Null values is in the "special" syntax also NOT used by Adam!!!!

If you want the null values you have to use either:
- IS NULL
- IS NOT NULL

Where the former will return the NULL/Blank fields and the latter the NON-Null/NON-Blank fields....

So...
Select * from yourtable where SomeColumn <> "Something" or SomeColumn IS NULL

Will return the blank values....

Hope that helps....

 

[batwings]

namlia

Thanks again,
I've just found out my problem is also compounded by the linked excel spreadsheet. It has hidden apostrophies in it, all tags are pre-fixed with ' but the update query does seem to work for all fields that originally had data regardless off the hidden apostrophies.

Do you know how to remove them from inside access? I would rather not have to remove them in excel everytime after I have run the application that outputs my link file.

 

[namliam]

Run an update query to remove these ' from the columns you import.

 

[batwings]

Namliam

Sounds great but how?
The field in my Excel linked spreadsheet is named "Date_Complete" and contains Dates in the form of Text.

Example '01/07/2009 for entries with a completed date and just ' in all of the blank excel cells that do not have a date.

How do I write an update query that removes the ' and changes the field to "-" in Access?

 

[namliam]

if its a date, dont you want to convert it to a proper date???

something like:
mid(yourfield,2)

Will pick up all except your first character, Check out the Left, Right, Mid and Dateserial functions... see how you get on...