insert 0 instead of blank field importing from Excel

[Guest]

I have a table that I frequently update by copying the data from Excell into
the table. The table has 3 fields that I copy over into. Problem is
sometimes 2 out of 3 will have data, the other is blank. How can I set the
table up to insert a 0 in that blank field instead of leaving it blank?
for example if data looks like this
20 25 blank

i want it to do this when copied into table

20 25 0

 

[Douglas J. Steele]

Create a query, and use the Nz function on the three fields:

SELECT Nz(Field1, 0), Nz(Field2, 0), Nz(Field3, 0)
FROM MyTable

Export the query, not the table.

 

[Guest]

I am importing from Excel into Access not from Acess out. When i import the
data into the table I need it to insert a zero instead of a blank field

 

[Douglas J. Steele]

Sorry: misread.

You should still create the same query I recommended, and then use that
query rather than the table in your Application.

Alternatively, you could run an UPDATE query to replace null values with
zeroes.

 

[Guest]

I thought of that but i have 3 fields and I tried to make one update query to
update any blank fields to zero but what happens is if 2 out of the 3 have
amounts and the 3rd is null the query is overwriting all 3 with zeros

 

[Douglas J. Steele]

UPDATE MyTable
SET Field1 = Nz([Field1], 0),
Field2 = Nz([Field2], 0),
Field3 = Nz([Field3], 0)

If there aren't that many nulls, it might be slightly better to use

UPDATE MyTable
SET Field1 = Nz([Field1], 0),
Field2 = Nz([Field2], 0),
Field3 = Nz([Field3], 0)
WHERE Field1 IS NULL
OR Field2 IS NULL
OR Field3 IS NULL