The total of multiplication error (2 Viewers)

[7anthala]

Hello, i have an old ms access database that am stuck with in my work, where am facing an error when the total of multiplication is greater than 22, 000,000. I can not comtact the programmer anymore and need your help.

It is access 98.

 

[The_Doc_Man]

That particular number makes no sense as a limit without knowing more context. Are you able to open the .MDB file in design mode and find the data type of the failing variable?

Also, "error" can mean a lot of things, so it would help us a LOT if you can be more specific about WHY or HOW you know it is an error? Wrong answer? Error message? Program hangs? Program crashes?... tell us the physical symptoms. If there is an error message, what is it?

 

[GPGeorge]

Please help us help you.

First, there was no Access 98. Access 97 exists, as does Access 2000. Can you please verify that you have Access 97? And if not, what version is it?

Second, the version of Access would not make a difference anyway, I don't think.

Show us the actual multiplication.

Show us the datatype of the number being calculated.

Show us where the calculation is done. In a form? A report? A query? In VBA?

The more details you provide, the greater the chance someone will be able to offer usable suggestions.

 

[cheekybuddha]

Does this total represent currency?

It sounds as if your developer was multiplying currency values by 100 to remove decimals in calculations and therefore 22,000,000 * 100 is overflowing the positive limit of a long integer (2,147,483,647).

Can you get at the code of your db?

 

[The_Doc_Man]

Good point, David. Didn't think about scaling.

 

[GPGeorge]

Hello all, yes it is Access 97. I have the mde file and could not get mdb file.
Am using mde unlocker to make some adjustments to the forms and reports when needed and everything was working fine. Until we faced this problem when the prices increased madly, so the multiplication as shown in the pictures gives error. It is in arabic.

"Until we faced this problem when the prices increased madly, so the multiplication as shown in the pictures gives error."

That very strongly suggests David's insight is correct. The solution is to change the way the calculation is made. If it's an mde, that could be hard, if not impossible. We'd have to see the actual code or SQL that completes the calculation, I believe.

 

[7anthala]

"Until we faced this problem when the prices increased madly, so the multiplication as shown in the pictures gives error."

That very strongly suggests David's insight is correct. The solution is to change the way the calculation is made. If it's an mde, that could be hard, if not impossible. We'd have to see the actual code or SQL that completes the calculation, I believe.

Is there a way to access how the calculation is done, because i tried to navigate the form i could not find anything. GPGeorge if you can help with a zoom call i would appreciate

 

[Gasman]

"Until we faced this problem when the prices increased madly, so the multiplication as shown in the pictures gives error."

That very strongly suggests David's insight is correct. The solution is to change the way the calculation is made. If it's an mde, that could be hard, if not impossible. We'd have to see the actual code or SQL that completes the calculation, I believe.

MDE Unlocker is incapable of showing the code.

MDE Unlocker for Microsoft® Access. Get the software safely and easily.

This program converts your compiled Microsoft Access database (.MDE...

mde-unlocker-for-microsoft-access.software.informer.com

 

[DickyP]

We don't know what the programmer did but depending on where data tables are just changing the data type to currency or decimal(14,2) might, (and I emphasise might) help. I can see lots of ways this could exacerbate the problem, however.

 

[GPGeorge]

MDE Unlocker is incapable of showing the code.

MDE Unlocker for Microsoft® Access. Get the software safely and easily.

This program converts your compiled Microsoft Access database (.MDE...

mde-unlocker-for-microsoft-access.software.informer.com

Yeah, I was trying to be polite about it, but only the SQL in a query might be visible.

 

[The_Doc_Man]

With the .MDB file, this fix would be relatively easy. Without the .MDB file, fixing this might be close to impossible. We face the problem that anything that reverse-engineers an Access .MDE database would be a legally actionable violation of the MS Office End User License Agreement. I don't know whether you can find your programmer, @7anthala, but you are now caught in between the proverbial rock and hard place.

 

[DickyP]

We don't know what the programmer did but depending on where data tables are just changing the data type to currency or decimal(14,2) might, (and I emphasise might) help. I can see lots of ways this could exacerbate the problem, however.

Whoops! Actually this was an inaccurate suggestion -forget decimal as it wasn't in 97!

 

[cheekybuddha]

@7anthala, you appear to have added some screenshots to your post #6 which look quite helpful.

Are you able to open the form in the third screenshot (20240701_203544.jpg) in Design View?

What is the RecordSource (in SQL) of the form (or subform perhaps)?

 

[7anthala]

Hello my dear friends,

I really appreciate your support.
Finally i got to the line total arguement if you could help

Line Total: CLng(([Quantity]*[UnitPrice])*(1-[Discount])*100)/100

This is the item that is resulting in error if the unite price is more than 22,000,000

 

[arnelgp]

maybe change the Formula of Line Total to:

Line Total:  (CLng([Quantity])*CLng([UnitPrice])*(1-[Discount])*100)/100

 

[Galaxiom]

Line Total: CLng(([Quantity]*[UnitPrice])*(1-[Discount])*100)/100

Whoops! Actually this was an inaccurate suggestion -forget decimal as it wasn't in 97!

The division by 100 at the end of the calculation suggests to me that the developer may have simulated Decimal data using long integers.

Long integers will calculate the result as long Integer so will overflow if any part of the result is > 2^31 - 1.

The values could be converted to one of the floating point types, Single or Double (more precise) but there would be potential for rounding errors in the result.

If arnel's suggestion doesn't do it then try something like this.

 CLng((CdDbl([Quantity])*CDbl([UnitPrice]))*(1-[Discount])*100)/100

 

[7anthala]

maybe change the Formula of Line Total to:

Line Total:  (CLng([Quantity])*CLng([UnitPrice])*(1-[Discount])*100)/100

It worked perfectly. Thank you sooo much

 

[7anthala]

I faced another problem in another form with this equation as follows
=[LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100/100)+[FreightCharge]

I have tried to edit it as follow
=[LineTotal]+(CLng([LineTotal])*CLng([SalesTaxRate])*100/100)+[FreightCharge

But gave an error using too much ()

Your help is appreciated

 

[GaP42]

Your second effort lacks a closing square bracket for FreightCharge. Was that the verbatim error message?

 

[7anthala]

Your second effort lacks a closing square bracket for FreightCharge. Was that the verbatim error message?

No here i missed the closing bracket, i used too much parenthesis