Start Free Trial
Come for the solution, stay for everything else.
Start Free Trial
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.
8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today
7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
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
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] = ""
Thanks for your suggestions.
All fields in questions are numeric. They came into numeric fields as blank when the data was brought in.
Simple and worked great.
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
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.
Thanks for the advise. I will do it one field at a time. I was beginning to try more than one but was skittish.
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:
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
to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Thanks so much.
You multi-field code works great without the WHERE clause.
The IS Null or ISNUll clause gives the Where clause problems.
glad to help.
Plans and Pricing
Certified Expert Program
© 1996-2022 Experts Exchange, LLC. All rights reserved. Covered by US Patent