Can Anyone Help with this Query code?

[Nina2001]

SELECT tblSWLDetails.AUTHAMOUNT AS SumOfAUTHAMOUNT_TOTAL
FROM tblSWLDetails
WHERE ((tblSWLDetails.FUNCTIONAL_MANAGER)="grantja"

I have a table (tblSWLDetails) that pulls from a data warehouse. If there is not a functional manager with the id "grantja" in the data warehouse it does not give my table (tblSWLDetails) a row for that manager. So in my query for my subform I have the above select statement. But I want to say if there's no row for "grantja" in the tblSWLDetails fill SumOfAUTHAMOUNT_TOTAL with a "$0.00". I tried an ELSE statement but that didn't work. Does anyone know how to do this?

Nina

 

[jgpasko]

Hi Nina,

When you say "doesn't give that manager a row in the table" does that mean that the FUNCTIONAL_MANAGER field in their recordset is null or 0 value?

Thanks. John Pasko
john@rts-sd.com
"No matter where you go, there you are."

 

[Nina2001]

Kind-of. I have a query that makes the table and sometimes there won't be a charge for each functional manager. So in the tbl there's no recordset for the functional manager. In fact, that functional manager won't even be in the table. So what happens is the text box that would have the $$ amount ends up being grayed out since the table does not have a field for that functional manager. I was just wondering if there was a way to tell it to put a $0.00 in that text box if it doesn't find a entry for that functional manager in the table it's looking at.

Thanks!

 

[jgpasko]

Nina,

Have you tried setting the default value for the txt box to 0 and the format to Currency? Then, if your query returns a value for the SumOfAUTHAMOUNT_TOTAL it will become the value, otherwise, $0.00.

From what you described, this should work.

Good Luck!

John John Pasko
john@rts-sd.com
"No matter where you go, there you are."

 

[cmmrfrds]

You could try.
SELECT nz(tblSWLDetails.AUTHAMOUNT,0.00) AS SumOfAUTHAMOUNT_TOTAL
FROM tblSWLDetails
WHERE ((tblSWLDetails.FUNCTIONAL_MANAGER)="grantja"