Returning 0 for null values.

[Pjdelchi]

Here is an easy one for you guys. How can I modify this query to return
zeros for any null field that may be returned in this query. I know that it
will probably be an Iff statement or Nz, but I cannot seem to find an example
of this type of right, left, union query, with replacing the resulting null
fields with zeros. I know that I could write another query, but I really
need it to be only one query. Any help would be greatly appreciated.

SELECT A.Category, A.Amount AS AmountA, B.Amount As AmountB
FROM Table1 AS A RIGHT JOIN Table2 AS B
ON A.Category = B.Category
UNION
B.Category, A.Amount AS AmountA, B.Amount As AmountB
FROM Table1 AS A LEFT JOIN Table2 AS B
ON A.Category = B.Category

 

[Douglas J. Steele]

SELECT B.Category, A.Nz(Amount, 0) AS AmountA, B.Amount As AmountB
FROM Table1 AS A RIGHT JOIN Table2 AS B
ON A.Category = B.Category
UNION
A.Category, A.Amount AS AmountA, Nz(B.Amount, 0) As AmountB
FROM Table1 AS A LEFT JOIN Table2 AS B
ON A.Category = B.Category

Note that I change which field is shown as the first field in the query as
well.

If you're using A RIGHT JOIN B, that will give you everything in B, whether
or not there's a matching entry in A. That means you want B.Category
displayed. Similarly, A LEFT JOIN B gives you everything in A, whether or
not there's a matching entry in B, so you want A.Category.

 

[Pjdelchi]

Thanks for your input Eileen and Doug, but I could not get either suggestion
to work. I was hoping that using the simple example before would make it
easier to see the format. Anyway, here is the full sql code that I am
working with:

SELECT BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP, Sum(BOLT.PARVALUE) AS
SumOfPARVALUE, Sum(BOLT.PRINCOST) AS SumOfPRINCOST, Sum(BOLT.BOOKVALUE) AS
SumOfBOOKVALUE, Sum(InTrader.PARVALUE) AS SumOfPARVALUE1,
Sum(InTrader.PRINCOST) AS SumOfPRINCOST1, Sum(InTrader.BOOKVALUE) AS
SumOfBOOKVALUE1
FROM BOLT RIGHT JOIN InTrader ON (BOLT.GRP = InTrader.GRP) AND
(BOLT.BDSKACCT = InTrader.BDSKACCT) AND (BOLT.CUSP = InTrader.CUSP)
GROUP BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP
ORDER BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP
UNION SELECT BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP, Sum(BOLT.PARVALUE) AS
SumOfPARVALUE, Sum(BOLT.PRINCOST) AS SumOfPRINCOST, Sum(BOLT.BOOKVALUE) AS
SumOfBOOKVALUE, Sum(InTrader.PARVALUE) AS SumOfPARVALUE1,
Sum(InTrader.PRINCOST) AS SumOfPRINCOST1, Sum(InTrader.BOOKVALUE) AS
SumOfBOOKVALUE1
FROM BOLT LEFT JOIN InTrader ON (BOLT.GRP = InTrader.GRP) AND (BOLT.BDSKACCT
= InTrader.BDSKACCT) AND (BOLT.CUSP = InTrader.CUSP)
GROUP BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP
ORDER BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP;

I need to have zeros returned for any field that is null except for each of
the two bdskacct, grp, and cust fields. These will require a IIf statement
with a "Not found" put in the Null Fields. Hopefully, I can handle that
one.

Anyway, if either of your guys could help me with this big mess, I would
greatly appreciate it.

Thanks,
Phillip

For any numeric field if you use the nz statement that will work.
For example

SELECT nz([a.category],0), nz([a.amount],0) etc.........

Hope that helps.

Thanks
Eileen

Here is an easy one for you guys. How can I modify this query to return
zeros for any null field that may be returned in this query. I know that
it
will probably be an Iff statement or Nz, but I cannot seem to find an
example
of this type of right, left, union query, with replacing the resulting
null
fields with zeros. I know that I could write another query, but I really
need it to be only one query. Any help would be greatly appreciated.

SELECT A.Category, A.Amount AS AmountA, B.Amount As AmountB
FROM Table1 AS A RIGHT JOIN Table2 AS B
ON A.Category = B.Category
UNION
B.Category, A.Amount AS AmountA, B.Amount As AmountB
FROM Table1 AS A LEFT JOIN Table2 AS B
ON A.Category = B.Category