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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
All fields in questions are numeric. They came into numeric fields as blank when the data was brought in.
Thanks again.
Timothy
ASKER
Simple and worked great.
Thanks
Timothy
Thanks
Timothy
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.
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
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
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
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
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
ASKER
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
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
glad to help.
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] = ""