Why does 160.34 x 6 return 962.02 and not 962.04?

[KellyAnchor]

I am multiplying unit cost by a percentage and then multiplying that result by quantity and cannot get the calculations to come out to the precision I would like.

All my fields are described as standard numeric with 2decimal positions, but it looks like the extension is being calculated with a decimal precision of 3.

I'm calculating the fields in a select query.
I've tried doing the extended calculation all at once as in cost*markup*qua ntity and also splitting it into 2 calculated fields. One for the marked up price (calculatedunit price=cost*mark up) and one for the extension (calculatedunit price*quantity) with the same results.

[NeoPa]

Kelly, can you post the SQL of the query along with the values of the various involved fields for the record where this is going wrong please.

PS. Can you also specify the field types of all related fields too.

[KellyAnchor]

Here's the sql behind the query.

SELECT QuoteMaster.*, QuoteDetail.*, IIf([MarkupPercent]=0,QuoteDetail!UnitPrice,QuoteDetail![Unit Cost]*QuoteDetail!MarkupPercent) AS CalcUnitPrice, [CalcUnitPrice] AS CalculatedUnitPrice, QuoteDetail!Quantity*[CalculatedUnitPrice] AS Expr1, QuoteDetail!Quantity*QuoteDetail![Unit Cost] AS Expr2, IIf([taxcode]=0," ",[taxcode]) AS TaxCodePrint, IIf([CustomerNumber]=0," ",[CustomerNumber]) AS CustomerNumberPrint, IIf([Quantity]=0," ",[Quantity]) AS QuantityPrint, IIf([taxpercent]=0," ",[taxpercent]) AS TaxPercentPrint, IIf([Quantity]=0," ",[QuoteLineNumber]) AS LinePrint, QuotePictures.Picture1, QuotePictures.Picture2, QuotePictures.Picture3, QuotePictures.Picture4, QuotePictures.Picture5, QuotePictures.Picture6, QuotePictures.Picture7, QuotePictures.Picture8, QuotePictures.Picture9, QuotePictures.Picture10
FROM (QuoteMaster LEFT JOIN QuoteDetail ON QuoteMaster.QuoteNumber = QuoteDetail.QuoteNumber) LEFT JOIN QuotePictures ON QuoteMaster.QuoteNumber = QuotePictures.QuoteNumber
WHERE ((([QuoteMaster.QuoteNumber])=[Forms]![FormQuoteMaster]![QuoteNumber]));

[NeoPa]

How about the rest of the info requested? I have nothing without it all.

[KellyAnchor]

Sorry...was trying to post the remaining answers when I had to reboot my computer.

All fields are defined as Standard with 2 decimal positions although I can't always see the decimal positions field when I look at the properties on the calculated fields. Not sure why.

Unit Cost
145.76
243.46
148.61
126.51
225.56
291.93

MarkupPercent
1.1
1.1
1.1
1.1
1.1
1.1

CalcUnitPrice
160.336
267.806
163.471
139.161
248.116
321.123

(on my screen this actually shows only 2 decimal positions, but when I copy and paste, it shows up as 3 decimal positions here)

Expr1
962.016
2142.448
653.884
1391.61
992.464
1284.492

(same as above)


So....even though the results of my query show on the screen as 2 decimals, the query is storing it as 3 decimals...or that's what it looks like.

[NeoPa]

Kelly, although I was after the Data Type, I should have realised that most people see only the format so made it clearer what I was after.

The Data Type is likely to be something like Number or Currency. You problem is almost certainly related to using Number rather than Currency for those fields that hold currency values.

Within the Number Data Type there are also a number of Field Size settings to choose from which can be relevant (I'm afraid there is absolutely no relevance to the Format property. It simply controls how the data is displayed and not how it is stored or used in complications). Single would be a poor setting to use. Double better, but Decimal probably the best. That said, using Currency as a Data Type is probably better and more reliable than all the others, but only for the currency fields (EG. [Unit Cost]).

[Markup Percent] should be set to Number and Double (or possibly even Decimal) and [Quantity] probably set to Number and Long (assuming no fractions allowed, otherwise Double or Decimal).

Can you make these changes (I'm guessing here to a certain extent as I'm not sure yet what you're currently using) and let us know how things work out for you afterwards.

BTW. These settings are all found in the definition of the fields in the table. Not in a query (as I suspect you may have been looking at before). Changing the query will have no effect.

[KellyAnchor]

Here's what I have now which has made no difference in the results.

Quantity
Number
Decimal

Unit Cost
Currency
Currency

Markup Percent
Number
Decimal

The fields that I'm having problems with are calculated within the query. How is data type for those fields determined?

Thank you,
Kelly

[NeoPa]

Kelly:
Here's what I have now which has made no difference in the results.

That's good (The work rather than the results). Fields of Data Type Currency have no Field Size property, but clearly you have everything set up properly.

Kelly:
The fields that I'm having problems with are calculated within the query. How is data type (determined) for those fields

That is up to the application (Access) to determine for itself. It's supposed to use types that are compatible with fields and/or values in the calculation. To be fair, the worst assumption that it could make would be to hold or calculate values as singles, but I'd be surprised if even that could account for errors of this magnitude. We're only dealing with values of six decimal digits after all.

Now may be the time to post a copy of your database in here for us to have a look at. I'll copy in below some instructions you should follow carefully to get it done reliably :

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If the database includes any code, ensure that all modules are set to Option Explicit (See Require Variable Declaration).
5. If you've done anything in steps 1 to 4 then make sure that the problem you're experiencing is still evident in the updated version.
6. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
7. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
8. Compress the database into a ZIP file.
9. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[NeoPa]

While I'm looking at this, can you confirm for me that the following much simplified SQL also exhibits the same problem characteristics and that Expr1 is the field we are looking at to see these incorrectly rounded values?

SELECT IIf([MarkupPercent]=0,
           tQD.UnitPrice,
           tQD.[Unit Cost]*tQD.MarkupPercent) AS CalcUnitPrice
     , tQD.Quantity*[CalcUnitPrice] AS Expr1
     , tQD.Quantity*tQD.[Unit Cost] AS Expr2
     , tQM.*
     , tQD.*

FROM   QuoteMaster AS tQM
       LEFT JOIN
       QuoteDetail AS tQD
  ON   tQM.QuoteNumber = tQD.QuoteNumber
WHERE ([tQM.QuoteNumber] = [Forms]![FormQuoteMaster]![QuoteNumber])

[KellyAnchor]

Yes. Same results. I will leave your sql in the copy of the database I send.

[KellyAnchor]

I was not able to leave your sql in the database because I could not get the form to work correctly.

And, just so you know, I'm not really profficient in sql language. I'm an AS/400 RPG programmer who dabbles with Access. Which is probably why I'm having such a time of this! :-)

Anyway, when you open the database, select "Enter Quotes and Invoices" and then click on the "Preview" tab.
This will display the invoice we are talking about. And, FYI, I just added the markup percent field to the database yesterday.

[NeoPa]

Kelly:
Yes. Same results.

Good. I will look at this in more detail when I get home and can download the file. I'm very curious.

[KellyAnchor]

Did you have any luck with this?

[NeoPa]

Not yet kelly, but I hope to have some more free time very shortly indeed (See NeoPa Absence).

Joking aside, sorry to leave you hanging. I have a busy day tomorrow related to linked item, but I hope to start catching up soon with all my outstanding threads.

[Stewart Ross]

Given NeoPa's current situation I've had a look at your DB. The values calculated in your queries are subject to small rounding errors, even though many (not all) of the currency-related items are now defined as currency types. Reason for this is that you have percentage fields etc defined as doubles, and when these are used to multiply the currency values rounding errors are introduced into the many IIFs etc you are using.

The calculation headlined in this question is actually correct when the true value is taken into account; it is not 160.34 * 6 = 962.02, but 160.336 * 6 = 962.016, which when rounded to 2 decimal places in the formatted result gives the apparently erroneous result.

To resolve this you will need to use rounding on the calculated unit price value in your QueryQuoteRepor t query:

CalcUnitPrice: Round(IIf([MarkupPercent]=0,[QuoteDetail]![UnitPrice],[QuoteDetail]![Unit Cost]*[QuoteDetail]![MarkupPercent]),2)

What this does is to round the unit price to two decimal places, so that what is currently displayed as 160.34 but passed to your calculation as 160.336 is passed instead as 160.34.

At least in the example I've checked this leads to the correct total of 962.04 in your invoice report.

I find the queries you've written a little difficult to follow, as expressions are used which have unhelpful names such as Expr1 and so on. These are defaults created by Access and intended to be replaced by something more meaningful, but as you have many field dependencies I'm not suggesting you replace these at present. Because of the complexity of the dependencies I cannot be sure there are no other rounding errors lurking in your tax calculations etc, but at least you should be able to understand the principles involved: if working with values representing currencies, use currency types wherever possible and multiply these by whole-numbers if you can. If you have to use doubles then ensure that you explicitly round each calculated non-whole-number value used in subsequent expressions to two decimals if you want the end result to be correct to the penny.

-Stewart

@NeoPa: sorry to hear of your work situation. I hope it works out for the best, if this is at all possible in the circumstances.