Multiple calculations on same field in Query, by range of values

[Guest]

OK, I am new to Access, have studied hard and long, but apparently am short
on the long. Trying to write the following Query:
Field One contains a numberic value(ie: 115 or 219 or 320)
I create the following Calculated Field:
Field Two:[Field One]
Now I want to perform the following calculation:
If >=1 AND <=100 put in $300.00
If >=101 AND <=200 put in $400.00
If >=201 AND <=300 put in $500.00

What shoud the rest of the formula be?
Field Two:[Field One]

Thanks for your help,
Greg
Using Access 2003

 

[Pat Hartman]

You would use nested IIf()s
fld2:IIf(fld1 >= 1 and fld1 <= 100, 300, IIf(fld1 >= 101 and fld1 <= 200,
400, IIf(fld1 >= 201 and fld1 <= 300, 500, 999)))
There is a problem with the logic though. Your conditions miss any amount
with a decimal value between 100 and 101, and 200 and 201 and anything less
than 1 or > 300. I assigned 999 but you can replace that with 0 if you
want. You just need to understand what you have omitted. You might want to
change the statement to:
fld2:IIf(fld1 >= 1 and fld1 < 101, 300, IIf(fld1 >= 101 and fld1 < 201, 400,
IIf(fld1 >= 201 and fld1 <= 300, 500, 999))) - this variation takes care of
100 - 100.99 and 200-200.99.

 

[Guest]

Thanks for your help and insight! I greatly appreciate it!!!!

You would use nested IIf()s
fld2:IIf(fld1 >= 1 and fld1 <= 100, 300, IIf(fld1 >= 101 and fld1 <= 200,
400, IIf(fld1 >= 201 and fld1 <= 300, 500, 999)))
There is a problem with the logic though. Your conditions miss any amount
with a decimal value between 100 and 101, and 200 and 201 and anything less
than 1 or > 300. I assigned 999 but you can replace that with 0 if you
want. You just need to understand what you have omitted. You might want to
change the statement to:
fld2:IIf(fld1 >= 1 and fld1 < 101, 300, IIf(fld1 >= 101 and fld1 < 201, 400,
IIf(fld1 >= 201 and fld1 <= 300, 500, 999))) - this variation takes care of
100 - 100.99 and 200-200.99.

OK, I am new to Access, have studied hard and long, but apparently am short
on the long. Trying to write the following Query:
Field One contains a numberic value(ie: 115 or 219 or 320)
I create the following Calculated Field:
Field Two:[Field One]
Now I want to perform the following calculation:
If >=1 AND <=100 put in $300.00
If >=101 AND <=200 put in $400.00
If >=201 AND <=300 put in $500.00

What shoud the rest of the formula be?
Field Two:[Field One]

Thanks for your help,
Greg
Using Access 2003