Access table changing fields from null to 0

[Enchantix]

hello,

ive been trying to figure out how to solve this problem with luck,

ive added a new field (column) to my data table in access 2010 and it has about 7300 records in it.

i would like to change all the blank cells in this new field (column) from blank or null to "0".

find and replace doesnt seem to work and im not too sure on the NZ function and how to do it.

ive also copied the data table to excel and changed all the fields to 0 and when i tried to paste it, i was given an error saying clipboard data is damaged and could not be pasted...


if anyone can help that would be greatly appreciated.

 

[Norie]

Couldn't you use an update query with Is Null in the criteria?

Something like this.

UPDATE Table1 SET city.Field1 = "0"
WHERE Table1.Field1 Is Null;

 

[Joe4]

Note. If you want to set it to a value of zero, you don't need the quotes around the zero (quotes are usually reserved for text values, not numeric ones).

 

[Norie]

Joe4

I've assumed it's a text field as I don't think numeric fields can be Null.

 

[Joe4]

I've assumed it's a text field as I don't think numeric fields can be Null.

Sure they can. I have come across many instances where they are.
Just to note, the code will work with the double-quotes in there, even if the field is numeric. Access seems smart enough to coerce it to a number.
But it is always good to understand when to use/not use them (which is why I made that comment, for the OP's benefit).

 

[Norie]

Ach, your right - I was just thinking that a numeric field couldn't be Null because they don't have the Allow Zero Length option.

 

[Enchantix]

Thank you guys for your help. Works great.

cheers