How to turn Excel Formulas into an Access Query?

[Teresa Barnacle]

Can't for the life of me work these 2 formula into an Access query !

Any help would be much appreciated ....

L5 is [Miles]



=IF(L5>150,130*3.8,(IF((AND(L5>19.9,L5<150.1)),(L5-20)*3.8,)))

=IF(L5>150,(L5-150)*2.4,0)

[Jonathan Lei]

Hi Teresa,

You may try the following solutions.

Also, you may change the names of Expr1 ,Expr2 and Table1 accordingly.

Fields Formula:
Expr1: IIf([Miles]>150,130*3.8,IIf([Miles]>19.9 And [Miles]<150.1,([Miles]-20)*3.8))

Expr2: IIf([Miles]>150,([Miles]-150)*2.4,0)

Access Query:
SELECT Table1.Miles, IIf([Miles]>150,130*3.8,IIf([Miles]>19.9 And [Miles]<150.1,([Miles]-20)*3.8)) AS Expr1, IIf([Miles]>150,([Miles]-150)*2.4,0) AS Expr2
FROM Table1;

Jonathan

[ADezii]

1. Sample SQL Statement:
   

   SELECT A.Miles, fCalcWhatever([Miles]) AS Expr
   FROM A
   WHERE A.Miles Is Not Null;

2. Function Definition:
   

   Public Function fCalcWhatever(sngMiles As Single) As Variant
   If sngMiles >= 150 Then
     fCalcWhatever = (130 * 3.8)
   ElseIf sngMiles > 19.9 And sngMiles < 150 Then
     fCalcWhatever = ((sngMiles - 20) * 3.8)
   Else        'sngMiles <= 19.9
     fCalcWhatever = sngMiles      'Not sure
   End If
   End Function

3. Sample Date:
   

   Miles
   20.1
   149
   174
   17
   14.3

4. Results based on Sample Data:
   

   Miles    Expr
   20.1       0.380001449584961
   149      490.2
   174      494
   17        17
   14.3      14.3

[Teresa Barnacle]

IT WORKS !!!

THANK YOU SOOOOOOO much to all who helped.

Regards

One very happy person

[Teresa Barnacle]

Need help again,sorry

B: IIf([Miles]>150,130*3.8,IIf([Miles]>19.9 And [Miles]<150.1,([Miles]-20)*3.8))

How do I put in this "if null put me a 0 in" ?

My other sums don't work if I don't put a zero in, sometimes I could scream !

Regards, Teresa

[ADezii]

Try:

IIf(IsNull([Miles]),0,IIf([Miles]>150,130*3.8,IIf([Miles]>19.9 And [Miles]<150.1,([Miles]-20)*3.8)))

[NeoPa]

Teresa, your formula doesn't specify any value for when [Miles] not greater than 19.9.

If we assume that you are intending to say 20 and above, and that less than 20 returns a value of zero too, then try :

IIf(IsNull([Miles]),0,IIf([Miles]>150,130,IIf([Miles]<20,0,[Miles]-20))) * 3.8

[Teresa Barnacle]

Done and works perfect, you're all brilliant !

Thanks, Teresa

[NeoPa]

A pleasure Teresa.

To be fair, I think ADezii's version would have worked well too.