update fields filled with spaces to nulls

[dkingston]

i have several linked tables in access2002 that i use to
import data from an AS/400.
after i update the linked tables i append the data in them
to other tables.
when i import an "empty" field is is filled with spaces.
i am running update queries to update a field Like' *'
to Null.
currently i need a seperate update query for each field.
is there a way to check all fields in a table for all
spaces or all zeros and change the fields to Null?
many thanks for any help you can give me.
please reply via post or to:
d k i n g s t o n (at) j a g l y n n (dot) c o m

 

[Jessestonecedar]

check using the 'iif' function in your update queries. You can define one for
each field and update all at once.

 

[TC]

You realize that you can update many fields in a single Update statement?

UPDATE MyTable
SET ThisField = ...,
ThatField = ...,
TotherField = ...,
etc.

You could even write some code that would take a table name, find the text
fields in that table's structure, and create the UPDATE statement
programatically! If you want to investigate that idea, start with this:

(untested)

dim db as database, fld as field
set db = currentdb()
for each fld in db.tabledefs![MyTable].fields
debug.print fld.name, fld.type
next
set db=nothing

HTH,
TC