Run time error 6 (1 Viewer)

[kasmax]

Hi everyone
i need some help please. i am getting runtime error 6 on the following expression

Dim a As String
a = -0.1933 + [height] * [height] * 0.00018642 + age * 0.00064 + [age] * [age] * -0.000269

i tried all variable types eg long int, double etc.

and the error only occurs if the height is 183 or above. age for example can be 60.

ill appritiate if someone can help me out.

thanks

 

[mahenkj2]

I have used your expression in a form's text box control source and it does not give any error.

 

[kasmax]

Hi
Thanks for the reply.
well i am using it in a module function and then calling it in query, and it give the error.
the reason im doing so is because in query when i try to calculate it by using iif conditions the string becomes too long it it trunc it

 

[kasmax]

i have checked it on the form textbox and yes it works fine. so my question is that why does it gives error when using in function?

 

[mahenkj2]

So, can you show your module. Also, how you are calling this function in the query?

 

[kasmax]

ok i have solved the problem although im confused about it.
the module is :
Function CalcPredFVC(age As Long, ethnic As Long, gender As Long, height As Long)
If ([age] >= 20 And [ethnic] = 1 And [gender] = 0) Then
CalcPredFVC = -0.1933 + [height] * [height] * 0.00018642 + [age] * 0.00064 + [age] * [age] * -0.000269
ElseIf ([age] < 20 And [ethnic] = 1 And [gender] = 0) Then
CalcPredFVC = -0.2584 + [height] * [height] * 0.00018642 + [age] * -0.20415 + [age] * [age] * 0.010133
ElseIf ([age] < 18 And [ethnic] = 1 And [gender] = -1) Then
CalcPredFVC = -1.2082 + [height] * [height] * 0.00014814999 + [age] * 0.05916
ElseIf ([age] >= 18 And [ethnic] = 1 And [gender] = -1) Then
CalcPredFVC = -0.356 + [height] * [height] * 0.00014814999 + [age] * 0.0187 + [age] * [age] * -0.000382
ElseIf ([age] < 20 And [ethnic] = 0 And [gender] = 0) Then
CalcPredFVC = -0.4971 + [height] * [height] * 0.00016643001 + [age] * -0.15497 + [age] * [age] * 0.007701
ElseIf ([age] >= 20 And [ethnic] = 0 And [gender] = 0) Then
CalcPredFVC = -0.1517 + [height] * [height] * 0.00016643001 + [age] * -0.01821
ElseIf ([age] < 18 And [ethnic] = 0 And [gender] = -1) Then
CalcPredFVC = -0.6166 + [height] * [height] * 0.00013606 + [age] * -0.04687 + [age] * [age] * 0.003602
ElseIf ([age] > 18 And [ethnic] = 0 And [gender] = -1) Then
CalcPredFVC = -0.3039 + [height] * [height] * 0.00013606 + [age] * 0.00536 + [age] * [age] * -0.000265
ElseIf ([age] < 20 And [ethnic] = 2 And [gender] = 0) Then
CalcPredFVC = -0.7571 + [height] * [height] * 0.00017823 + [age] * -0.0952 + [age] * [age] * 0.006619
ElseIf ([age] >= 20 And [ethnic] = 2 And [gender] = 0) Then
CalcPredFVC = 0.2376 + [height] * [height] * 0.00017823 + [age] * -0.00891 + [age] * [age] * -0.000182
ElseIf ([age] < 18 And [ethnic] = 2 And [gender] = -1) Then
CalcPredFVC = -1.2507 + [height] * [height] * 0.00014246001 + [age] * 0.07501
ElseIf ([age] > 18 And [ethnic] = 2 And [gender] = -1) Then
CalcPredFVC = 0.121 + [height] * [height] * 0.00014246001 + [age] * 0.00307 + [age] * [age] * -0.000237
End If
End Function

and im calling it in query like:
Pred FVC: Val(CalcPredFVC([Age],[Ethnic],[Gender],[Height]))

it is working fine after i changed function from
Function CalcPredFVC(age As integer, ethnic As integer, gender As integer, height As integer)
to
Function CalcPredFVC(age As Long, ethnic As Long, gender As Long, height As Long)

although the values of height for example is 183 which should still wotk with integer.

so im not sure why i get error if i have it as int in the function

 

[MarkK]

My 2c

This ...

If ([age] >= 20 And [ethnic] = 1 And [gender] = 0) Then
CalcPredFVC = -0.1933 + [height] * [height] * 0.00018642 + [age] * 0.00064 + [age] * [age] * -0.000269
ElseIf ([age] < 20 And [ethnic] = 1 And [gender] = 0) Then
CalcPredFVC = -0.2584 + [height] * [height] * 0.00018642 + [age] * -0.20415 + [age] * [age] * 0.010133
ElseIf ([age] < 18 And [ethnic] = 1 And [gender] = -1) Then
CalcPredFVC = -1.2082 + [height] * [height] * 0.00014814999 + [age] * 0.05916
ElseIf ([age] >= 18 And [ethnic] = 1 And [gender] = -1) Then
CalcPredFVC = -0.356 + [height] * [height] * 0.00014814999 + [age] * 0.0187 + [age] * [age] * -0.000382
ElseIf ([age] < 20 And [ethnic] = 0 And [gender] = 0) Then
CalcPredFVC = -0.4971 + [height] * [height] * 0.00016643001 + [age] * -0.15497 + [age] * [age] * 0.007701
ElseIf ([age] >= 20 And [ethnic] = 0 And [gender] = 0) Then
CalcPredFVC = -0.1517 + [height] * [height] * 0.00016643001 + [age] * -0.01821
ElseIf ([age] < 18 And [ethnic] = 0 And [gender] = -1) Then
CalcPredFVC = -0.6166 + [height] * [height] * 0.00013606 + [age] * -0.04687 + [age] * [age] * 0.003602
ElseIf ([age] > 18 And [ethnic] = 0 And [gender] = -1) Then
CalcPredFVC = -0.3039 + [height] * [height] * 0.00013606 + [age] * 0.00536 + [age] * [age] * -0.000265
ElseIf ([age] < 20 And [ethnic] = 2 And [gender] = 0) Then
CalcPredFVC = -0.7571 + [height] * [height] * 0.00017823 + [age] * -0.0952 + [age] * [age] * 0.006619
ElseIf ([age] >= 20 And [ethnic] = 2 And [gender] = 0) Then
CalcPredFVC = 0.2376 + [height] * [height] * 0.00017823 + [age] * -0.00891 + [age] * [age] * -0.000182
ElseIf ([age] < 18 And [ethnic] = 2 And [gender] = -1) Then
CalcPredFVC = -1.2507 + [height] * [height] * 0.00014246001 + [age] * 0.07501
ElseIf ([age] > 18 And [ethnic] = 2 And [gender] = -1) Then
CalcPredFVC = 0.121 + [height] * [height] * 0.00014246001 + [age] * 0.00307 + [age] * [age] * -0.000237
End If

... looks exactly like a table to me.
Performance, scalability, flexibility, maintenance, and simplicity would all be served or improved if this data was in a table. Fields would be...

Age, Ethnicity, Gender, K1, K2, K3, K4

... and so on. Data in the first row would be ...

20, 1 , 0, -0.1933, 0.00018642, 0.00064, -0.000269

Make sense?