What is the Biggest number I can have in Access ?

  • Thread starter Thread starter Michael Hudston
  • Start date Start date
M

Michael Hudston

I have written this VBA Script to basically calculate the number of spares
based on the Poisson Culumative Distribution. However after a while it
breaks down as the variable FCT goes into overflow. - The number hits in the
region of 1.17E+254 I know this is a massive number but then that is
indicitive of the equation as in theory it could go to infinity

I am wondering what my next step is, and if there is a solution or not.

The Function I have written is below, and as a guide the following set of
values can be used (the first set works, the second doesnt)

1st Set

Probability = 0.9
Lambda = 156.967232876712

2nd Set

Probability = 0.9
Lambda = 131.205435114504
..

Code

Public Function InvPoisson(ByVal Probability As Double, ByVal Lambda As
Double) As Long

' This function is designed to return the number of spares required when
fed with the Availiability, and Mean.
' Written By Michael J Hudston

' Ensure all figures are reset before we start

Dim Fct As Double

Count_A = 1
Complete = 0
Prob = 0
SumProb = 0
Fct = 1
StockOutRisk = 1 - Probability
i = 0
InvPoisson = 0

' Now do the majical maths stuff

Do

' Set the Start point for the Culumative Probability Calculation for
this run.

SumProb = 0
Fct = 1

' Carry out the Culumative Probability Caculation for this run.

For i = 0 To Count_A

' Do stuff for the Factorial.

If i = 0 Then
Fct = 1
Else
Fct = Fct * i
End If

Prob = (Exp(-1 * Lambda) * Lambda ^ i) / Fct
SumProb = SumProb + Prob

Next

' If the Culumative Probability is greater than 1 - the Stock Out
Risk then you dont need to continue
' so set the complete flag

If Complete = 0 Then
If SumProb > 1 - StockOutRisk Then
InvPoisson = Count_A
Complete = 1
Else
' Otherwise add one to the spares count and run the
Culumative calculation again.
Count_A = Count_A + 1
End If

End If

Loop Until Complete = 1

' Repeat the loop until the complete flag has been set
' (Ie 1 - Stock Out Risk is less than the Culumative Probability.

End Function
 
On Wed, 11 Mar 2009 01:14:01 -0700, Michael Hudston

It's in the help file, under "double data type". On the high side
approx 10^304

-Tom.
Microsoft Access MVP
 
hi Michael,

Michael said:
However after a while it
breaks down as the variable FCT goes into overflow.
Click to expand...
The factorial should bail out with an overflow for a start value of i =
171. But beware of the inprecise data type. It is not necessarily the
mathematically correct factorial value.
The Function I have written is below, and as a guide the following set of
values can be used (the first set works, the second doesnt)
Click to expand...
It doesn't compile on my system.
Dim Fct As Double

Count_A = 1
Complete = 0
Prob = 0
SumProb = 0
Fct = 1
StockOutRisk = 1 - Probability
i = 0
InvPoisson = 0
Click to expand...
Where do you declare these variables? What type do they have? Use Option
Explicit and set Variable declaration needed in Tools/Options in the VBA
IDE.



mfG
--> stefan <--
 
It would seem that if the Double Data Type is the largest I can have, then I
have to re-think my entire aproach.

The function doesnt seem to breakdown in Excel, and I have discovered that I
can call the function in Excel from Access, so I may have to resort to that,
but that would mean that both are running at the same time
 
Back
Top