Query not giving expected multiplication results

[KRISTIRG]

I know rounding can almost always be an issue, which is why I usually don't convert values to currency until the "end" when they're being displayed or used for end use cases.
But this is just glaringly odd and definitely not a rounding issue. Some of the values are off by way too much for that.
I'm at a loss. All I can say is click open a calculator. Lol. I threw the actual PerPoundRate and LoadPounds into the query for display purposes. I don't actually need them for functionality.

What the heck is it multiplying? And any ideas how to make it do what it's supposed to?

 

[arnelgp]

can you create new query based on "raw" table/tables and see if it makes difference.

 

[CJ_London]

Suggest remove the formatting from your tables as it hides the true value. Suspect for load 9 for example actually has a rate of 0.01405

Edit: formatting also includes the number of decimal points which usually defaults to 2

 

[KRISTIRG]

can you create new query based on "raw" table/tables and see if it makes difference.

So, I created a table with a few of the same numbers, they multiplied correctly because in the table I set them to Double and 4 decimal places. At least I'm assuming that's the difference. But in queries it doesn't give you the option to format that specifically. And in my source table I have Miles, which are a whole number...and a MileageRate, the $ per mile charge.
And that's basically it. LoadCost is just Miles * the per dollar rate the truck charges, and they get weighed at each stop so the sum of Pounds/Stop=TotalPounds and Cost/Pounds gives you the $0.08 or whatever per pound charge. I don't have any of theses values saved in tables since they're calculations. I guess I can try setting the original pounds and mileage rate to doubles even though there aren't any decimals in them and see what happens! Still seems strange though.

 

[KRISTIRG]

Yeah, that did no good at all. Still the same results.

 

[CJ_London]

Yeah, that did no good at all. Still the same results.

but it does show your problem

load9 - your formatted view shows $0.01. The actual value is 0.01405

So presumable the value has been determined by a calculation

 

[MarkK]

If you need numbers rounded to some fixed count of decimal places, use the Round() function.

? Round(0.195867, 2)
 0.2
? Round(0.046, 2)
 0.05

And it works in queries too.

 

[arnelgp]

can you use a function in your Query, on a Module add this function:

Public Function fnMul(Byval a As Double, Byval b As Double) As Double
fnMul = CDec(a) * CDec(b)
End Function

then change your TruckingCjharge calculated column in your query to:

TruckingCharge: fnMul([PerpoundRate], [LoadPounds])

 

[KRISTIRG]

This is what I got using the Module.....*ugh

 

[arnelgp]

modify the function:

Public Function fnMul(ByVal perPoundRate As Variant, ByVal LoadPounds As Variant, ByVal Fmt As String) As Double
    Dim a As Double
    If Nz(perPoundRate, 0) = 0 Or Nz(LoadPounds, 0) = 0 Then
        Exit Function
    End If
    a = Val(Format$(perPoundRate, Fmt) & "")
    fnMul = CDec(a) * CDec(LoadPounds)
End Function

now format the PerPoundRate on the query to 0.00 (or Fixed) to only show 2 decimal places.
change the Calculated column to:

TruckingCharge: fnMul([PerpoundRate], [LoadPounds], "0.00")

sample data:

result:

 

[KRISTIRG]

Well that seems to have done it! Thank you. It probably would help if my dummy data was a little more realistic, and honestly my python faker scripts usually hit it right on the mark but I guess truck driving just isn't a learned type yet. Lol.

 

[Minty]

This is one instance where setting the table data type to Decimal fixed (or currency) would probably resolve the issue.

 

[KRISTIRG]

The data type in the table is Currency.

 

[Minty]

The data type in the table is Currency.

But you said in an earlier post they were set to double, that is not the same?

 

[DHookom]

Would you consider providing a small ACCDB file to illustrate the issue? This should require only a table or two and a query.

 

[Pat Hartman]

Currency is a scaled integer with a maximum of 4 decimal places. It is useful in situations where you don't need more than 4 decimal places because it eliminates floating point errors which occur with singles and doubles. $100.00 = 1000000 with an implied decimal point, 4 from the right.

Rounding errors cannot be avoided so you and your user need to decide how to handle calculations that include multiplication and division since those need to be rounded at some point to avoid showing the customer a list of numbers that don't add up.

 

[KRISTIRG]

But you said in an earlier post they were set to double, that is not the same?

I said that's what the pounds were set to. Pounds are a weight, they can't be set to currency.

 

[Minty]

I said that's what the pounds were set to. Pounds are a weight, they can't be set to currency.

Ah - but you could still use currency format or a fixed decimal format to store the the values assuming you don't need more than 4 decimal places.
That would remove or curtail the rounding issues associated with the double and single formats. You can then format the display to remove the currency symbol.

 

[Pat Hartman]

Pounds are a weight, they can't be set to currency.

Currency is both a format and a data type. Obviously no one is telling you to use dollar signs on your weights.

Do not confuse how data is formatted with how it is stored. Formatting is for human consumption. So you see 4% but that is stored as .04 internally and the data type could be any numeric type that supports decimals including Currency