Overflow error 2950 (1 Viewer)

[Joe8915]

Can anyone point out where I can correct the overflow error. My thought it has to do with the WV Fix.

This all came about trying to convert a excel formula to access.

I attach the sample of the file from excel. I put it in a word document, but I think you get the idea what I trying to do.

If anyone has a better way or idea on how to do this please show me how.

SELECT TblEmp.Piece, TblEmp.Gross, TblEmp.PerHr, TblEmp.Employee, Sum(TblWorkdescription.DQty) AS SumOfDQty, TblWorkdescription.DRate, Sum(TblWorkdescription.Dadj) AS SumOfDadj, Sum(TblWorkdescription.PQty) AS SumOfPQty, TblWorkdescription.PRate, Sum(TblWorkdescription.Padj) AS SumOfPadj, Sum(TblWorkdescription.WVQty) AS SumOfWVQty, TblWorkdescription.WVRate, Sum(TblWorkdescription.WVhcadj) AS SumOfWVhcadj, Sum(Fix(100*[dqty]*([dstdhc]*[drate]/[dacthc]-[drate]))/100) AS [Summary detail], Sum(Fix(100*[pqty]*([pstdhc]*[prate]/[pacthc]-[prate]))/100) AS [Summary Partial], Sum(Fix(100*[WVqty]*([WVstdhc]*[WVrate]/[WVacthc]-[WVrate]))/100) AS [Summary WV]FROM TblEmp LEFT JOIN TblWorkdescription ON TblEmp.ID = TblWorkdescription.wrkdescp_ID
GROUP BY TblEmp.Piece, TblEmp.Gross, TblEmp.PerHr, TblEmp.Employee, TblWorkdescription.DRate, TblWorkdescription.PRate, TblWorkdescription.WVRate;


To all thanks for taking the time to look at this

 

[Joe8915]

I have confirm that its the:
Sum(Fix(100*[WVqty]*([WVstdhc]*[WVrate]/[WVacthc]-[WVrate]))/100) AS [Summary WV]

But I don't know how to fix the problem

Any ideas

 

[pr2-eugin]

This is only a try why this error might be.. not sure if it is correct.. My guess is that your arithmetic precedence might be wrong? You are fixing to avoid decimal, then you are also dividing the result by 100 and changing it back to decimal?? Confusing.. As I said I do not know the calculation that you are doing so.. maybe try this..

Sum(Fix((100*([WVqty]*([WVstdhc]*[WVrate]))/([WVacthc]-[WVrate]))/100))

 

[Joe8915]

pr2-eugin, thanks for the quick reply. Any chance did you take a look at the attach file. I tried to show everyone what I had, due to all of this is confusing to me as well. I will use the formula as you posted and see how that works. I will post the out come. Once again thanks for the input.

 

[spikepl]

try this, exactly as written,

Sum(Fix(100#*[WVqty]*([WVstdhc]*[WVrate]/[WVacthc]-[WVrate]))/100) AS [Summary WV]

 

[Joe8915]

SPIKEPL, thanks for chiming in, now on your suggestion I got a "syntex error"

pr2-eugin it calulated out to differen't number all together.

I like to say thanks to the both of you who are helping me on this confusing issue.

I wish I could give you more clear explanation of the problem.

 

[Joe8915]

Spikepli to be a little bit more clear on the syntax error

"the syntax error in date in query expression"

 

[spikepl]

Ok. Try this:

Sum(Fix(CDbl(100)*[WVqty]*([WVstdhc]*[WVrate]/[WVacthc]-[WVrate]))/100) AS [Summary WV]

 

[Joe8915]

spikepl, crap.......... I got the overflow again

 

[spikepl]

What is the data type of each field?

Also, is
WVacthc <>0? If not, use the Nz function

 

[boblarson]

You'd need to do the fix on each integer field I believe. if you multiply any of those together before converting to longs (or double) it would exceed the integer type limit.

 

[Joe8915]

All three is are a Number field and with a field size a Single with format as Currency. Don't tell me that is where the problem is? When Mr larson chime in makes me think what did the heck did I do!!!!!

WVacthc <>0? If not, use the Nz function............... This one will take some time to figure out.
For sure I am getting out of my comfort zone on what I am doing.

Thanks guys for taking the time to look at this

 

[Joe8915]

What I think I better do is give you what I have as my field properties in the table itself. I am just about ready to walk out of the office and I will continue this. I don't want to waste you time, not giving you the full picture.

Thanks guys

 

[boblarson]

Just noticed your field types post just before the last one and a single * single * single can easily exceed the value which it would use a single as default.

so

CDbl([Field]) * CDbl([Field]) * CDbl([Field])

should fix it.

 

[gemma-the-husky]

it probably is becuase the interpreter is treating some of the numbers as longs, and the value at some point is overflowing

one other point is that any division could introduce a div by zero error, which would also manifest as an ovwerflow.

could that be the issue.

you could instead pass all the numbers to a function, and manipulate the values in there. that would at least enable you to step through your calculations.

 

[boblarson]

it probably is becuase the interpreter is treating some of the numbers as longs, and the value at some point is overflowing

More like treating them as INTEGERS and overflowing. It would take some awfully big numbers to overflow a LONG.

 

[gemma-the-husky]

More like treating them as INTEGERS and overflowing. It would take some awfully big numbers to overflow a LONG.

true, but i just meant treating numbers as integer values, rather than real numbers, which introduces the possibility of overflow.


one issue i had a while back - i tried to write something in access to assist me in managing files, and doing a selective back up - it was working great, but the data count reported didn't make sense. I was trying to ignore some large files - such as dvd image files which are 4Gb each - but because an access longint is 2Gb or so, it actually treated the image file as a different size to what it really was, and there seemed no way round this.

thinking about it now, i probably could use currency datatype...

but how do you assign a value of 4billion to a currency data type???


OK - I just tried this, and it added the # after the value - so what does the # do??

Sub tryit()
Dim x As Currency
     x = 4000000000#
     MsgBox (x)
End Sub

 

[Joe8915]

Let me go ahead and upload the db. The qry that is giving me fits is called "QryWorkProduction Summary".

Once again thanks for all who is chiming in