converting excel 2000 formulas to access 2000

[vaughn]

=IF(A5="",0,IF(E5>50,2*(E5*(1/500)*1.76+(A5*B5)*(1/9600)*3+(0.0575+D5^2*0.0425)+C5*1.76),(E5*(1/500)*1.76+(A5*B5)*(1/9600)*3+(0.0575+D5^2*0.0425)+C5*1.76)))

This is a working formula in Excel 2000 to calculate bending time, but I can't get it to work in Access 2000 on a form or in a query. I've tried changing IF to IIF, but that did not do it. The ^ means the field is squared, and I changed it to [field1]*[field1] in access.
This calculates the time it takes to bend material on a press. The various cell references are:

A = length
B= width
C= thickness
D = # of bends
E = weight
F = bending time
I can send the excel spreadsheet if you send your email. On weekends you can reach me at Outman2@Juno.com

Thanks,

Vaughn

 

[Pat Hartman]

The If() needs to be IIf(). Are you referencing the correct column names? The row/column cell reference style does not exist when referencing a table? You might also need to change -
A5="" to IsNull(A5)

 

[neileg]

This seems to work
F: IIf(IsNull([A]),0,IIf([E]>50,2*([E]*(1/500)*1.76+([A]*)*(1/9600)*3+(0.0575+Sqr([D])*0.0425)+[C]*1.76),([E]*(1/500)*1.76+([A]*)*(1/9600)*3+(0.0575+Sqr([D])*0.0425)+[C]*1.76)))
Where the fields holding the data are called A,B,C,D,E in a table and F is a calculated field in a query.

 

[namliam]

I personaly HATE long calculations like that....

Suggestion: Create a function in Excel that will replace the formula (you can check/test in Excel if your function is correct)

Copy that function to Access and call it from your query. Presto !

Regards

-----

I even took the time to do your funtion for ya....

Function CalcBendTimes(Var1 As Variant, _
                       Var2 As Double, _
                       Var3 As Double, _
                       Var4 As Double, _
                       Var5 As Double)

'Var1 = A5
'Var2 = B5
'Var3 = C5
'Var4 = D5
'Var5 = E5

    If IsNull(Var1) Then CalcBendTimes = 0: Exit Function
    If Var5 > 50 Then
        CalcBendTimes = 2 * (Var5 * (1 / 500) * 1.76 + (Var1 * Var2) * (1 / 9600) * 3 + (0.0575 + Var4 ^ 2 * 0.0425) + Var3 * 1.76)
    Else
        CalcBendTimes = 1 * (Var5 * (1 / 500) * 1.76 + (Var1 * Var2) * (1 / 9600) * 3 + (0.0575 + Var4 ^ 2 * 0.0425) + Var3 * 1.76)
    End If
End Function

This should produce the same results as your If thing... (allthough its not verry "Good practice" Because you have 2 formula's matching allmost exactly, below is the same but shorter...

=IF(A5="",0,IF(E5>50,2,1)*(E5*(1/500)*1.76+(A5*B5)*(1/9600)*3+(0.0575+D5^2*0.0425)+C5*1.76))

 

[vaughn]

thank you very much for your help!