Access 2010 Update blank fields to zero

Back to basics.


Can any one tell me how to use the a query to update blank fields to zero? I have tried using update query with  ISBLANK, ISNULL, ISEMPTY, and ISMISSING.  They all update all fields to zero.

Timothy
TimothyAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
Theoretically, if this is a numeric field, then you won't have any "blanks", and there is no such thing as an Empty field.

You you are trying to do this in a query, it should look like:

UPDATE yourTable SET [SomeField] = 0 WHERE [SomeField] IS NULL
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
However, if the field is actually a string, and you want to test for NULLs or zero length strings, and replace those with a zero ("0")  then you might try:

UPDATE yourTable SET [SomeField] = "0" WHERE [SomeField] & "" = ""

This syntax appends a zero length string to [SomeField], the result of which will be a zero length string if [SomeField] IS NULL or if [SomeField] = ""
0
TimothyAAuthor Commented:
Thanks for your suggestions.

All fields in questions are numeric.  They came into numeric fields as blank when the data was brought in.

Thanks again.

Timothy
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

TimothyAAuthor Commented:
Simple and worked great.

Thanks

Timothy
0
Dale FyeCommented:
If you are new at this, the best way to do this is one field at a time, with the syntax mentioned above (first example should do it).  

You can do it more than one at time, but the syntax is significantly different, and there is greater potential for overwriting the data that exists in the other records/fields.
0
TimothyAAuthor Commented:
Fyed,

Thanks for the advise.  I will do it one field at a time.  I was beginning to try more than one but was skittish.

Thanks again.

Timothy
0
Dale FyeCommented:
I strongly recommend that you always work with a backup copy of your data.

When you do more than one field at a time, you have to construct the SQL so that it won't replace a value in one field that shouldn't.  By using the NZ( ) function you can pass it the value of a field, and if that field is not NULL, then it will return the value that was passed.  If the field IS NULL then the function will return the value you assigned to the 2nd argument in the function (in this case a zero).

The syntax would look like:

UPDATE yourTable
SET  [Field1] = NZ([Field1], 0)
       ,[Field2] = NZ([Field2], 0)
       ,[Field3] = NZ([Field3], 0)

You don't really need a WHERE clause for this because it will actually replace each value in each of the fields, of every row in your table.

If you have a lot of data, you might add a WHERE clause that looks like:

WHERE [Field1] IS NULL OR [Field2] IS NULL or [Field3] IS NULL
0
TimothyAAuthor Commented:
Fyed,

Thanks so much.

You multi-field code works great without the WHERE clause.

The IS Null or ISNUll clause gives the Where clause problems.

Thanks again.

Timothy
0
Dale FyeCommented:
glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.