Replace empty entries in LEFT JOIN query with '0'

[fboehlandt]

Hello everyone,
I would like to run the following query from two tables in my database:

SELECT RETURNS.Fundcode, RETURNS.Fundname, RETURNS.MM_DD_YYYY, RETURNS.ROI, ASSETS.AUM
FROM RETURNS LEFT JOIN ASSETS
ON RETURNS.Fundcode = ASSETS.Fundcode AND
RETURNS.MM_DD_YYYY = ASSETS.MM_DD_YYYY;

Where <Fundcode> - <MM_DD_YYYY> is the composite primary key in both tables.

The tables look like this:
<Fundcode> <Fundname> <MM_DD_YYYY> <ROI>
and:
<Fundcode> <Fundname> <MM_DD_YYYY> <AUM>

This query works as intended. Since not all funds report Assets under Management (AUM) but all of them report Return on Investments (ROI),
the new column AUM in the query contains a few empty entries (the left join is intended, this should not be an inner join!). I would rather have these entries replaced with the value '0'. How do I go about doing this?
Any ideas?

 

[neileg]

Try a calculated field:
NewAUM: Nz([ASSETS.AUM],0) in the query grid
or
Nz([ASSETS.AUM],0) AS NewAUM in the SQL

 

[fboehlandt]

That did the trick! Many thanks