VBA code for probability

[Sierra Mike]

Dear all,

I have a table with three fields FR (Field Type Double Scientific), Time (Field type long integer general), Quantity (Field Type long integer general).
Now i have made a form which has all the fields of the table bound to it. in this form i have made a button called calculate.

what i want is .......
when i enter the FR and Time values and click the calculate button it should display the x value in the quantity field on the form. the formula is

summation (from x=1 to n)[ (FR*Time)^x * (e^(-FR*time))/ x!] >= 0.9
(sigma function)

basically, the code should return the value of x the moment expression value goes above 0.9.

the code i have used is as follows:

Private Sub Command7_Click()

Dim Prob As Double
Dim x, fact As Long


x = 1
fact = 1




For i = 1 To x
fact = fact * i
Next i


Prob = ((Me.FR * Me.Time) ^ x) * Exp(-(Me.FR * Me.Time)) / fact


Do While Prob < 0.9
x = x + 1


For i = 1 To x
fact = fact * i
Next i


Prob = Prob + ((Me.FR * Me.Time) ^ x) * Exp(-(Me.FR * Me.Time)) / fact


Loop


Me.Quantity = x

End Sub


I am getting an error in the bold underlined line of the code as overflow.

Can anyone help. this is really very urgent and important.

thanks

[pbaldy]

What do the variables contain at the point it errors?

[June7]

Provide examples of FR and Time data. I tested with values 0.1 and 4. The variable fact reaches 597196800 and errors long before Prob could reach 0.9.

[Sierra Mike]

thanks for your help............some of the sample values of Time are 25920 (for all) and FR are

FR

4.21*10^-6
4.16*10^-6
4.80*10^-6
8.66*10^-6

and X value i.e answer should be (approx)
0
0
3
1

[Sierra Mike]

dear June7,
Request if you could plz send me the code you wrote............even i can try at my end....
thanks

[orange]

Mike,

Small points

Dim x, fact As Long

This does not do what you think.

You must explicitly define variables.

Dim x as Long, fact As Long OR

Dim x as Long
Dim fact as Long

Time is a reserved word in Access

You might add a Debug.Print to monitor some variables.

[June7]

All I did was copy/paste your procedure then replace all the Me.FR and Me.Time references to declared variables and set the variables. And changed Me.Quantity = to Debug.Print.