If then statements in Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

In my Query I have a field that lists the price for each hotel. I want to
group my hotels by price range. So, I'm thinking that I can make a new field
in the query that assigns the name of hotel price range according to the
price listed in the price column. The Query will end up looking this in
theory:

Price Price Range
11 $11-$20
12 $11-$20
52 $50-$100

I want to make an If-Then statement in the new field 'Price Range' saying
something like "If [Price] >=1 AND <=10 Then [Price_Range] = "$1-$10", If
[Price] >= 11 AND <=20 Then [Price_Range] = "$11-$20" ...... Else
[Price_Range] = "More than $100] End If.

Obviously I don't know how to write this code and for the life of me I
cannot find directions on how to do this. Can this be done in a query like
I'm thinking? Is it possible? How? Are there any better recommendations for
grouping records based on parameters that are not symetric or regular
intervals (i.e. my grouping will not necessarily going in intervals of $10,
but may jump to intervals of $50 for expensive hotels)?

PLEASE HELP!

PaulinLaos
 
Consider creating a table of price ranges with:
MinPrice MaxPrice PriceRange

You can then add this to your query and set the criteria under your price
field to
Between MinPrice And MaxPrice

Add the PriceRange field to your query to display the PriceRange title.
 
Duane's idea is probably best; however, you might be able to do it in a
query. Access supports IIf statements and they can be nested. They can get
slow and sloppy when running. You could also create a Case statement in a
module and pass your data though it.
 
Back
Top