Update Query

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
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:

Code:
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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:
Code:
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
 

Forum statistics

Threads
1,137,204
Messages
5,680,166
Members
419,887
Latest member
Vasokir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top