Avatar of TimothyA
TimothyA asked on

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
Microsoft Access

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Dale Fye

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] = ""
ASKER
TimothyA

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
ASKER
TimothyA

Simple and worked great.

Thanks

Timothy
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Dale Fye

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.
ASKER
TimothyA

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
Dale Fye

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
TimothyA

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
Dale Fye

glad to help.