Update Query

[bschulze]

I have about 80 fields that I need to update. The query is a basic IsNull to Zero type query. I thought that there was a way to do this using the SQL feature however I have had little luck thus far. Here is what I attempted:

UPDATE TAX2000 SET TAX2000.TaxableState=0 WHERE (((TAX2000.TaxableState) Is Null))
UPDATE TAX2000 SET TAX2000.TaxableCounty=0 WHERE (((TAX2000.TaxableCounty) Is Null))
UPDATE TAX2000 SET TAX2000.TaxableCity=0 WHERE (((TAX2000.TaxableCity) Is Null))
UPDATE TAX2000 SET TAX2000.TaxableDistrict=0 WHERE (((TAX2000.TaxableDistrict) Is Null))
UPDATE TAX2000 SET TAX2000.TaxState=0 WHERE (((TAX2000.TaxState) Is Null))
UPDATE TAX2000 SET TAX2000.TaxCounty=0 WHERE (((TAX2000.TaxCounty) Is Null))
UPDATE TAX2000 SET TAX2000.TaxCity=0 WHERE (((TAX2000.TaxCity) Is Null))
UPDATE TAX2000 SET TAX2000.TaxDistrict=0 WHERE (((TAX2000.TaxDistrict) Is Null))

Access does not seem to like this. It is not as bad as it seams as I used excel to "Concatenate" the string together. Is there a way to update multiple fields at once? I would hate to have to write 80 separate update queries. Any help would be great.

 

[giacomo]

It looks like you were trying to execute multiple SQL statements in a single query, Access won't let you do that. You would have to either break those out into individual queries or maybe try something like this:

UPDATE 
TAX2000 
SET 
TaxableState = nz(TaxableState,0),
TaxableCounty = nz(TaxableCounty,0),
TaxableCity = nz(TaxableCity,0),
TaxableDistrict = nz(TaxableDistrict,0),
TaxState = nz(TaxState,0),
TaxCounty = nz(TaxCounty,0),
TaxCity = nz(TaxCity,0), 
TaxDistrict = nz(TaxDistrict,0);

this is air code so try it on a temp table

hth,
Giacomo

 

[bschulze]

this is exactly what I am looking for...thanks for your help