Syntax Error using IF in SELECT Statement

[NLR]

Hi,

I'm not sure what's wrong...
I'm trying to replace a null value with $0.00 for the second field in a query. My first try at the SELECT stmt did not contain any solution for a NULL value. The result was that it skipped the record. I need it to show 0.00 because the field is used in another calculated field.

My SQL:

SELECT tblRecovery.CustID, IF((Sum([tblRecovery.RecAmt]) IS NULL, 0.00, (Sum([tblRecovery.RecAmt]))) AS SumOfRecAmt
FROM tblRecovery
GROUP BY tblRecovery.CustID;

This returns
Syntax error (missing operator) in query expression 'IF(( etc.
After clicking "OK", access highlights AS in the statement.
I'm not sure how to deal with the NULL value or fix the error??
I appreciate your help...

NLR

 

[Ranman256]

SELECT tblRecovery.CustID, NZ([tblRecovery.RecAmt]) AS SumOfRecAmt
FROM tblRecovery
GROUP BY tblRecovery.CustID

 

[NLR]

Hi Ranman256,

I entered the following SQL:

SELECT tblRecovery.CustID, Sum(Nz([tblRecovery.RecAmt],0)) AS SumOfRecAmt
FROM tblRecovery
GROUP BY tblRecovery.CustID;

which fixes the error; however, the query only provides a record when there is a number returned. It skips the records with NULL values. How can I get it to show all records and those with NULL values to display $0.00?

Thanks!!
NLR

 

[Brianwarnock]

Try
Nz(Sum(tblrecovery.recamt),0)

Brian

 

[NLR]

Hi Brianwarnock,

I tried your suggestion; still skipping records??
Any ideas?

Thanks,
NLR

 

[marlan]

Hi,

the function is IIF

SELECT tblRecovery.CustID, IIF((Sum([tblRecovery.RecAmt]) IS NULL, 0.00, (Sum([tblRecovery.RecAmt]))) AS SumOfRecAmt
FROM tblRecovery
GROUP BY tblRecovery.CustID;

 

[vbaInet]

Perhaps your expectations can't be met because of the sort of data that you have.

Upload some sample data.