Update Query across entire table

[Allen Blevins]

I want to replace all "null" values in my table with "N/A".
It looks like you can only update 1 field with a update query, but if you do
a find for "null" and replace it with "N/A" it works just fine.

Is there a solution other than using the Find and Replace? I'm trying to
automate this with a macro.

Thanks

 

[John W. Vinson]

I want to replace all "null" values in my table with "N/A".

Ummmm....

WHY!?

You can very easily *display* nulls as N/A using an appropriate expression or
format, without wasting three bytes per field; your N/A will require that all
your fields be of Text type (since you can't store N/A in a Number or Date
field); other problems will arise.

It looks like you can only update 1 field with a update query, but if you do
a find for "null" and replace it with "N/A" it works just fine.

It is certainly NOT the case that you can update only one field. What gives
you that impression?

Create an Update query, and update each field (well, each Text type field) to

NZ([fieldname], "N/A")

This will update the field to itself if it's not NULL and to N/A if it is.

Is there a solution other than using the Find and Replace? I'm trying to
automate this with a macro.

Again... WHY? It sounds like you'll want to do this again and again; why not
prevent the insertion of NULL in the first place by setting the field's
Default Value to "N/A" and make it Required?