if >= to, then...

  • Thread starter Thread starter Tara
  • Start date Start date
T

Tara

Hello,

I'm transferring a very complex file from excel into Access, but am having
trouble with one of my formulas. It works in excel, but when I type it in the
query, I either get a syntax error or it tells me I am trying to do too much.
I've tried breaking it down into smaller sections using the criteria
sections, but no luck. This is how my formula looks in excel:

if(X>=3000;(X-3000)*.08;0)
if(X>=2000;MIN(1000;(X-2000))*.12;0)
if(X>=1000;MIN(1000;(X-1000))*.14;0)
if(X<1000;(1000-X)*(-.10);0)

Thank you for any help!
Tara
 
I would create a small user-defined function that can be used in code or
queries or other places. I don't understand your logic and conditions but
you could start by creating something like:
Public Function GetTarasAnswer(dblX As Double) As Double
'if(X>=3000;(X-3000)*.08;0)
' if(X>=2000;MIN(1000;(X-2000))*.12;0)
' if(X>=1000;MIN(1000;(X-1000))*.14;0)
' if(X<1000;(1000-X)*(-.10);0)
Select Case dblX
Case Is >= 3000
GetTarasAnswer = (dblX - 3000) * 0.08
Case Is >= 2000
If dblX - 1000 > 1000 Then
GetTarasAnswer = 1000 * 0.12
Else
GetTarasAnswer = (dblX - 2000) * 0.12
End If
'more case statements
Case Else
End Select

End Function

You can then use this function like most any other function.
 
Thanks for your help. I was hoping to find something a bit simpler, but this
works as well. Tara.
 
Hello,

I'm transferring a very complex file from excel into Access, but am having
trouble with one of my formulas. It works in excel, but when I type it in the
query, I either get a syntax error or it tells me I am trying to do too much.
I've tried breaking it down into smaller sections using the criteria
sections, but no luck. This is how my formula looks in excel:

if(X>=3000;(X-3000)*.08;0)
if(X>=2000;MIN(1000;(X-2000))*.12;0)
if(X>=1000;MIN(1000;(X-1000))*.14;0)
if(X<1000;(1000-X)*(-.10);0)

Thank you for any help!
Tara
Click to expand...

Well... Access and Excel are quite different, even when they appear to be
similar. The Min() operator in Access returns the minimum value of a field
*across multiple rows*, not the lesser/least of its operands.

In this case you can use the Switch() function. It takes pairs of arguments,
evaluates them left to right, and returns the second member of the first pair
for which the first element is true. I'd use it twice:

Switch(X>=3000;x-3000;x>=2000;x-2000;x>=1000;x-1000;true;1000-x) *
Switch(X>=3000;.08;x>=2000;.12;x>=1000;.14;true;-.10)

A possibly better solution would be to have a translation table Factors with
three fields - XLow, XHigh, and Factor and values like

0; 1000; .10
1000; 2000; .14
2000;3000; .12
3000; 2000000000; .08

You could then use a Query like

SELECT x-XHigh * Factor
FROM yourtable
INNER JOIN Factors
ON yourtable.X >= Factors.XLow
AND yourtable.X < Factors.XHigh;


John W. Vinson [MVP]
 
Thanks John. I finally understand why MIN didn't work. The switch function
works well for me, but I don't quite understand how have a "translation table
Factors ". I will stick with the switch function.
Tara.
 
John's solution of a lookup table is the most maintainable and ideal.
Wrapping your calculation in a single function is probably the next best. I
would not create a complex expression of Switch() or IIf()s since the ranges
are bound to change.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm
 
I'm trying insert a lookup table, but I keep getting told that I need to
include parentheses around the sub-query. I have tried inserting them in
several different ways, but then end up with a syntax error?

I have taken out all the spaces out of my fields names, but that did not
change anything either. I am supposed to be writing all this in the field
section of a query, right? What else might I be doing wrong?

Sorry, but I am still very new to access!
 
I'm trying insert a lookup table, but I keep getting told that I need to
include parentheses around the sub-query. I have tried inserting them in
several different ways, but then end up with a syntax error?
Click to expand...

Please open your query (without the subquery); select View... SQL; and post
the SQL text here. Indicate the fieldname for the subquery criterion. We can't
fix what we can't see!

John W. Vinson [MVP]
 
Thank you both for your help. I wasn't switching it to the SQL view. Works
better now! Tara.
 
Back
Top