Setting a default value in a blank field in an Access query

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开发者_如何学C got to be something simpler! HELP!

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)

You'll either want to use Nz() or iif(), depending on whether that "blank" really is just a blank (empty string) or Null.

So your options are:

SELECT Nz(source1.a + source2.b,0) FROM source1, source2

or:

SELECT iif(source1.a + source2.b <> "", source1.a + source2.b, 0) FROM source1, source2