homepage Welcome to WebmasterWorld Guest from 54.145.183.169
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Access Query Problem
wingnut

5+ Year Member



 
Msg#: 9 posted 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:

MinPrice: Min(tblPrices.PriceStandard/tblProperties.PropOccupancy)

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.

TIA

 

txbakers

WebmasterWorld Senior Member txbakers us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 9 posted 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

10+ Year Member



 
Msg#: 9 posted 5:07 pm on Sep 6, 2005 (gmt 0)

Wingnut

You can use the IIF function in MS Access

IIf(expr, truepart, falsepart)

EG

MinPrice: IIF(tblPrices.PricePerPerson = True, CalculationForTruePart, CalculationForFalsePart)

wingnut

5+ Year Member



 
Msg#: 9 posted 6:05 pm on Sep 6, 2005 (gmt 0)

Thanks Graham works a treat:

The expression:

Min(IIF([tblPrices.PricePerPerson] = False,[tblPrices.PriceStandard]/[tblProperties.PropOccupancy],[tblPrices.PriceStandard]))

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved