Ms-access – Setting a default value in a blank field in an Access query

default-valuems-access

This may have been answered elsewhere, but I can't find it!

I'm combining 2 queries from different sources onto a single query for calculation and reporting purposes. Fields which are not common to both sources show as blank if there is no data, and can't then be used in arithmetic.

I want to set a default of zero where blank, instead of having to knife and fork the query into another table and run an update query over all the blank fields.

There's got to be something simpler! HELP!

Best Answer

Give this a try:

SELECT 
nz(value1, 0), nz(value2,0), nz(value3,0)
FROM table1 left outer join table2 on table1.column = table2.column

unless your query resembles what John answered with (a cartesian) then his answer is more appropriate.... (cartesians are dangerous if not used correctly... depending on how big the individual tables are allowed to become you can kill an access application by using them)