IIf Then Syntax to perform Calculation

[bethabernathy]

I am trying to generate a calculation on a report. The database tracks a ski areas lockers revenue and member information. I need to calculate the total deposit amount for the lockers. If the field "PPDepPaid" from the Locker table is equal to "Yes", then the field "ParkingDepositPrice" from the Prices table needs to be totalled in the report.

So in the report footer I am using the following syntax in a text box: "=IIf([PPDepPaid]=-1,[ParkingDepositPrice])"

How do I get the syntax to Sum the "ParkingDepositPrice" only when the field "PPDepPaid" (which is a Yes/No field) is equal to Yes or -1?

Any help is really appreciated. Thanks, Beth beth@integratedresourcemgmt.com

 

[CosmoKramer]

Beth,

How about the DSum function? Something like:

DSum("[ParkingDepositPrice]", "Prices", "[PPDepPaid]=-1")

 

[bethabernathy]

Hi Cosmo - I tried that and it isn't working. The strange thing is that I have this calculation working:

"=IIf([PPDepPaid]=-1,[ParkingDepositPrice])"

When it is run per individual record. Any other suggestions?? Thanks and nice hearing from you. Beth beth@integratedresourcemgmt.com

 

[mdthornton]

I think your problem is that you have not defined a value when PPDepPaid is false, in this case you iif returns NULL and if you try to sum across a mix of nulls and numerics you will get NULL. Try this:

=IIf([PPDepPaid]=-1,[ParkingDepositPrice],0)

and then in the footer:


=Sum(IIf([PPDepPaid]=-1,[ParkingDepositPrice],0))

If you don't want to show the zeros you can mask them out
in the format property. Best Regards,
Mike

 

[CosmoKramer]

Sorry Beth, my bad - I forgot the equal sign:

=DSum("[ParkingDepositPrice]", "Prices", "[PPDepPaid]=-1")

This will work in the report footer........