Remove record from count

[MDDDS]

I have a database that has a field for a procedure code and a field for the amount paid for the procedure. We can pay for the procedure-(+$), not pay for the procedure ($0), or backcharge (-$) for a procedure. I have a query that lists all the procedure codes that were done for the month and the +-or 0 paid. I have a second query that lists the total count of procedures and the total amount to be paid for each procedure code for the month. I can remove the $0 from the procedure count, but I would like to remove the -$ from the amount due AND from the total count of procedures done. Any help would be appreciated.

 

[Pat Hartman]

You can use the IIf() to create several calculated fields:

Select IIf(YourAmt < 0, YourAmt, 0) As NegAmt, IIf(YourAmt < 0, 1, 0) As NegCount, IIf(YourAmt = 0, YourAmt, 0) As ZeroAmt, IIf(YourAmt = 0, 1, 0) As ZeroCount, IIf(YourAmt > 0, YourAmt, 0) As PosAmt, IIf(YourAmt > 0, 1, 0) As PosCount, .....

Then use this query as the RecordSource for a report or use it as the basis for a totals query.

 

[MDDDS]

Thanks for the advice, but being a novice I am not clear where this code should go. Everywhere I have tried it I get an improper syntax error message. Where should these calculated fields be created and what should they be called?

 

[Pat Hartman]

Use the IIf()'s in a query. Make sure that you use your own field names.