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

G

Guest

HELP! Following is the original thread I made on 3-29-05, 8:54 AM.
I have used the advise shown in the reply a number of different ways,
without success (shown at the bottom is the actual formula I started with).

Could someone please advise what I am doing wrong? THANKS

Original Post:
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

Reply Received:
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.

Formula I Tried
(I am putting this into the second column of the Query table, in the “Fieldâ€):
Amount we are Invoicing [SF of Blue Tarp Installed]: IIf (SF of Blue Tarp
Installed >=1 and SF of Blue Tarp Installed <101, 300, IIF(SF of Blue Tarp
Installed >=101 and SF of Blue Tarp Installed <201, 400, IIf(SF of Blue Tarp
Installed >= 201 and SF of Blue Tarp Installed <= 300, 500, 999)))

NOTE: Amount we are Invoicing is the calculated field, as it is, field 2
SF of Blue Tarp Installed is the original filed, as it is, field 1

Error message: The expression you entered contains an invalid syntax
You may have entered an operand without an operator
 
J

John Vinson

HELP! Following is the original thread I made on 3-29-05, 8:54 AM.
I have used the advise shown in the reply a number of different ways,
without success (shown at the bottom is the actual formula I started with).
Click to expand...

Given that your fieldnames (unwisely, IMO) contain blanks, you must
enclose them in [square brackets]. It never hurts to do so, but if the
fieldname contains blanks, special characters, or matches an Access
reserved word, it's obligatory. Try


[Amount we are Invoicing [SF of Blue Tarp Installed]]: IIf ([SF of
Blue Tarp Installed] >=1 and [SF of Blue Tarp Installed] <101, 300,
IIF([SF of Blue Tarp Installed] >=101 and [SF of Blue Tarp Installed]
<201, 400, IIf([SF of Blue Tarp Installed] >= 201 and [SF of Blue Tarp
Installed] <= 300, 500, 999)))

This is not necessarily the best technique however; you're embedding
business data - the breakpoints and the amounts - in a complex nested
IIF statement. If the boss changes the rules you'll need to dig into
this code wherever it is found and figure out how to change it. You
might want to consider a slightly more sophisticated table-driven
approach. Create a table named Ranges with three fields, Low, High,
and Amount; fill it with records

0 100 300
100 200 400
200 300 500
300 10000000000 999

Then create a "Non Equi Join" query. Include Ranges in your query, at
first joining [SF Of Blue Tarp Installed] to Low; then go into the SQL
view of the query and change

INNER JOIN Ranges ON [yourtable].[SF of Blue Tarp Installed] =
[Ranges].[Low]

to

INNER JOIN Ranges ON [yourtable].[SF of Blue Tarp Installed] >
[Ranges].[Low] AND [yourtable].[SF of Blue Tarp Installed] <=
[Ranges].[High]

This will pick up the current value of Amount from the Ranges table.

John W. Vinson[MVP]
 
G

Guest

John,
Thanks for your time and consideration. However, I am still running into a
problem. When I put the formula in, I get the following error message,
"[Amount we are invoicing [SF of Blue Tarp Installed]] is not a valid name.
Make sure it does not include invalid characters or punctuation that is too
long".
When I use the exact same name and just do a simple calculation, ie: *10, it
works fine. All the same I changed the field name all the way down to
"Tarp", still without success. I sure would like to know how to make this
work without having to creat another table. Do you have any suggestions?
Thanks

Gregrenae said:
HELP! Following is the original thread I made on 3-29-05, 8:54 AM.
I have used the advise shown in the reply a number of different ways,
without success (shown at the bottom is the actual formula I started with).

Could someone please advise what I am doing wrong? THANKS

Original Post:
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

Reply Received:
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.

Formula I Tried
(I am putting this into the second column of the Query table, in the “Fieldâ€):
Amount we are Invoicing [SF of Blue Tarp Installed]: IIf (SF of Blue Tarp
Installed >=1 and SF of Blue Tarp Installed <101, 300, IIF(SF of Blue Tarp
Installed >=101 and SF of Blue Tarp Installed <201, 400, IIf(SF of Blue Tarp
Installed >= 201 and SF of Blue Tarp Installed <= 300, 500, 999)))

NOTE: Amount we are Invoicing is the calculated field, as it is, field 2
SF of Blue Tarp Installed is the original filed, as it is, field 1

Error message: The expression you entered contains an invalid syntax
You may have entered an operand without an operator
Click to expand...
 
J

John Vinson

John,
Thanks for your time and consideration. However, I am still running into a
problem. When I put the formula in, I get the following error message,
"[Amount we are invoicing [SF of Blue Tarp Installed]] is not a valid name.
Make sure it does not include invalid characters or punctuation that is too
long".
When I use the exact same name and just do a simple calculation, ie: *10, it
works fine. All the same I changed the field name all the way down to
"Tarp", still without success. I sure would like to know how to make this
work without having to creat another table. Do you have any suggestions?
Thanks
Click to expand...

Please post the actual SQL again.

John W. Vinson[MVP]
 
G

Guest

My apologies, "Post the original SQL". Do you mean to copy the info as I
have done below or start a new thread or?
Thanks Again

Original Post:
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]

Formulas Tried (I am putting this into the second column of the Query table,
in the “Fieldâ€)
Amount we are Invoicing [SF of Blue Tarp Installed]: IIf (SF of Blue Tarp
Installed >=1 and SF of Blue Tarp Installed <101, 300, IIF(SF of Blue Tarp
Installed >=101 and SF of Blue Tarp Installed <201, 400, IIf(SF of Blue Tarp
Installed >= 201 and SF of Blue Tarp Installed <= 300, 500, 999)))
[Amount we are Invoicing [SF of Blue Tarp Installed]]: IIf ([SF of
Blue Tarp Installed] >=1 and [SF of Blue Tarp Installed] <101, 300,
IIF([SF of Blue Tarp Installed] >=101 and [SF of Blue Tarp Installed]
<201, 400, IIf([SF of Blue Tarp Installed] >= 201 and [SF of Blue Tarp
Installed] <= 300, 500, 999)))


John Vinson said:
John,
Thanks for your time and consideration. However, I am still running into a
problem. When I put the formula in, I get the following error message,
"[Amount we are invoicing [SF of Blue Tarp Installed]] is not a valid name.
Make sure it does not include invalid characters or punctuation that is too
long".
When I use the exact same name and just do a simple calculation, ie: *10, it
works fine. All the same I changed the field name all the way down to
"Tarp", still without success. I sure would like to know how to make this
work without having to creat another table. Do you have any suggestions?
Thanks
Click to expand...

Please post the actual SQL again.

John W. Vinson[MVP]
Click to expand...
 
P

Pat Hartman

Stay with the original thread. If you haven't gone too far with your db,
you might consider turning on the Name Auto Correct features and getting rid
of those pesky spaces.

Gregrenae said:
My apologies, "Post the original SQL". Do you mean to copy the info as I
have done below or start a new thread or?
Thanks Again

Original Post:
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]

Formulas Tried (I am putting this into the second column of the Query table,
in the "Field")
Amount we are Invoicing [SF of Blue Tarp Installed]: IIf (SF of Blue Tarp
Installed >=1 and SF of Blue Tarp Installed <101, 300, IIF(SF of Blue Tarp
Installed >=101 and SF of Blue Tarp Installed <201, 400, IIf(SF of Blue Tarp
Installed >= 201 and SF of Blue Tarp Installed <= 300, 500, 999)))
[Amount we are Invoicing [SF of Blue Tarp Installed]]: IIf ([SF of
Blue Tarp Installed] >=1 and [SF of Blue Tarp Installed] <101, 300,
IIF([SF of Blue Tarp Installed] >=101 and [SF of Blue Tarp Installed]
<201, 400, IIf([SF of Blue Tarp Installed] >= 201 and [SF of Blue Tarp
Installed] <= 300, 500, 999)))


John Vinson said:
John,
Thanks for your time and consideration. However, I am still running into a
problem. When I put the formula in, I get the following error message,
"[Amount we are invoicing [SF of Blue Tarp Installed]] is not a valid name.
Make sure it does not include invalid characters or punctuation that is too
long".
When I use the exact same name and just do a simple calculation, ie: *10, it
works fine. All the same I changed the field name all the way down to
"Tarp", still without success. I sure would like to know how to make this
work without having to creat another table. Do you have any suggestions?
Thanks
Click to expand...

Please post the actual SQL again.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple calculations on same field in Query, by range of values 2
Text Field IIf Statement 7
search from range in column 1 and print from corresponding values 1
Which formula to bucket range of numbers 6
linked table using SQL Query 8
Pivot table and value range 2
how do I put in a sequence number on yearly basis? 1
Locating values in a range and bringing up the corresponding price 3

Top