Over Flow

[s1police]

Dear All,

I tried to run a variable to accumulate the value for the same country, but it prompts me that it is over flow. I have try to round() the value within the query or in the VBA, but fails.

Please help how I can solve the problem. Below are the coding, highlighting is the error.


********************************************************
Set query = CurrentDb.OpenRecordset("qry_ABC_Country_ByValue")

CumTotDemand = 0

query.MoveFirst

OldCountry = " "

Do Until query.EOF = True

'Reset Cumulative Total Demand for new country
If OldCountry <> query![Country] Then
CumTotDemand = 0
End If

'Always accumulate Total Demand for same country
CumTotDemand = query![ValueRatio] + CumTotDemand

'Update the table with [Article Value], [Country Value],
'[Value Ratio], [Accumulated Ratio]
'Key: Country Article
updSql = "UPDATE ABC_Country SET " & _
"ABC_Country.Article_Value = " & query![articlevalue] & ", " & _
"ABC_Country.Country_Value = " & query![CountryTotalValue] & ", " & _
"ABC_Country.Value_Ratio = " & query![ValueRatio] & ", " & _
"ABC_Country.CumulativeValue = " & CumTotDemand & _
" WHERE ABC_Country.[WE - Country] = '" & query![Country] & "' " & _
" AND ABC_Country.[Material] = '" & query![Article] & "' "

dbs.Execute updSql
OldCountry = query![Country]
query.MoveNext

Loop

query.Close

********************************************************

 

[pbaldy]

What is the data type of that variable, and what types of values might it contain (how big a number)?

 

[s1police]

I found that there is 1 txn is 0/0, and the value in the query is #Error. So, when VBA read the query![ValueRatio], it always display Overflow.

May I ask, how I can bypass this #Error and move to next record? Please advise.

 

[pbaldy]

Try using an IIf() function to test for zero and replace the formula with zero when it will occur.

 

[CyberLynx]

CumTotDemand = Nz(query![ValueRatio], 0) + CumTotDemand

?????

.

 

[Guus2005]

I think you have to solve #Error first. Doesn't sound healthy to me.

If everything else fails: On Error Resume Next.
You didn't hear it from me!

(Don't add to my reputation, don't click the scales!)

 

[s1police]

As said before, some of my txn(s) are 0 divided by 0, as a result, it appear "#Error" in query.

I have tried to check let see it is error or not by using IsError(), and hope it return 0 or 1. However, #Error still appears for the query. So, I think MS Access do not think this is error, 0 divided by 0 will be overflow, not an error....

Now, I am using a tricky solution to resolve this, however I would like to ask, is there any good suggestion in the action query to resolve this problem?

My solution is, if the division is "0", then the result = 0, else = original formula.

Please kindly advise.

 

[pbaldy]

That's what I suggested - an IIf() to test for zero - so I think that's the way to go.