Welcome to WebmasterWorld Guest from 184.108.40.206 , register , free tools , login , search , pro membership , help , library , announcements , recent posts , open posts Become a Pro Member
Access Query Problem wingnut msg:1578802 2:28 pm on Sep 6, 2005 (gmt 0) I have created an query in Access combing two related tables (Rented Properties and booking dates which contains a price for any given date). I have created an extra field which finds the minimium price from the booking dates table and divides that by the property occupancy to give me a price per person.
In the expression builder I get:
This works fine. However, some booking dates will be per person which is indicated by a yes/no box in the booking dates table. So my question is how to put a an if /else statement into the query like:
MinPrice: If tblPrices.PricePerPerson= True do this calculation else do this one.
Hope this makes sense.
txbakers msg:1578803 4:23 pm on Sep 6, 2005 (gmt 0)
I'm not sure if Access supports the CASE SQL function.
The CASE function allows you to run if/then type operations in a query.
Graham msg:1578804 5:07 pm on Sep 6, 2005 (gmt 0)
You can use the
IIF function in MS Access
IIf(expr, truepart, falsepart)
MinPrice: IIF(tblPrices.PricePerPerson = True, CalculationForTruePart, CalculationForFalsePart)
wingnut msg:1578805 6:05 pm on Sep 6, 2005 (gmt 0)
Thanks Graham works a treat:
Min(IIF([tblPrices.PricePerPerson] = False,[tblPrices.PriceStandard]/[tblProperties.PropOccupancy],[tblPrices.PriceStandard]))