Round function being ignored in query

[tvanstiphout]

A365-32 semi-annual, v2408. Using classic SQL editor.

Experiment 1:
Select CSng(Nz([Ingredients].[MaxQty],[Ingredients].[Qty])) AS IngredientQty From …

Experiment 2:
Select Round(CSng(Nz([Ingredients].[MaxQty],[Ingredients].[Qty])),3) AS IngredientQty From …

Both return the exact same value: 1.00000004749745E-03
I expected the second one to return 3 decimals.

In the Immediate window, it works correctly:
?Round(1.00000004749745E-03,3)
0.001

Ingredients.MaxQty and Ingredients.Qty are nullable single in the attached table; real in the SQL Server table (attached via ODBC 18).
If I add a column to the query: Round(1.00000004749745E-03,3), then it is rounded as expected.

What could be going on?

 

[Minty]

What happens if you change it to a double?
I've seen some strange behaviour with single data types, so no longer use them....

In fact CSng(1.00000004749745E-03) should return 0.001 as it's beyond it's prescribed accuracy:

? ? Interesting

 

[Mark_]

As I understand, ROUND only works server side for SQL. What back end are you accessing?

 

[tvanstiphout]

It is not directly related to the value I cited. Another record has a value of 0.675999999046326, and it is not being rounded either.

 

[tvanstiphout]

As I understand, ROUND only works server side for SQL. What back end are you accessing?

I doubt that Mark. This is not a passthrough query. Also note that the Nz function does not throw errors, while it does not exist in T-SQL.
The BE is SQL Server 2017. That's what the customer is using.

 

[The_Doc_Man]

I'm thinking it is because you have a decimal fraction on a binary machine, and you are using CSNG() which truncates digits beyond about 7 significant places. The rule of thumb is that every 10 bits in a binary number is 3 digits in a decimal number. CSNG puts your number into a layout of 1 sign bit, 8 exponent bits, and 23 mantissa bits = 32 bits for a SINGLE. So you have 23 bits of mantissa which is 10+10+3 bits. Your range is therefore 6 full digits + one more digit that isn't a full decimal place. Also remember that decimal fractions are irrational when converted to binary so those trailing digits are the result of playing with irrational numbers and the limit of precision of the machine..

Then, there is the fraction you are using. Using ROUND doesn't give you a clean displayed number, it gives you a constrained binary number usable for computation. If you REALLY wanted exactly three decimal places to be shown, use a FORMAT function.

 

[tvanstiphout]

@The_Doc_Man: I like your suggestion of using Format, but I am still puzzled by several aspects already introduced above:
* Immediate window result
* Round with literal value in query
* Not limited to very small numbers.

 

[June7]

Here's another curve. Round works when field is Float type.

Be aware Round in Access uses even/odd (banker's rounding) rule.

 

[tvanstiphout]

Here's another curve. Round works when field is Float type.

Be aware Round in Access uses even/odd (banker's rounding) rule.

In Access we don't have Float data type, but Double is the equivalent.
The VBA.Round function takes any "Numeric expression", per this documentation. That means my "real" data type is supported.

 

[The_Doc_Man]

Some time ago we had a discussion regarding what is actually displayed in a "raw" query grid - or for that matter, what is shown in a table's datasheet grid. That answer was that we see whatever the Access default formatting routine wants to show us - if we didn't include our own formatting specs. If you don't have a formatting option, you are seeing the exact content of the grid element, formatted by default to show maximum digits when you are talking about SINGLE or DOUBLE.

Just remember that 1/10th in binary is 0.0001100110011001100...00110011.... for as long as you express the bits. Just like 1/3 in decimal is 0.333333....33333..... for as long as you want to show it.

Round Function - Microsoft Support

Syntax for the function that returns a number rounded to a specified number of decimal places in Access.

support.microsoft.com

This is the description of what is returned by the ROUND function for Access - and I have emphasized a specific part.

Returns a number rounded to a specified number of decimal places.

The significant element is that it is NOT a formatting function. It is a numerical transformation function. The problem with returning a number is that if it is fractional, it is GOING to have truncation artifacts at the end. And that number is in the range of 1.000E-3, or less than 0.01 - DEFINITELY fractional.

 

[Josef P.]

I would classify this as a floating point issue in Jet/ACE.

Range of Single: 1.401298E-45 to 3,402823E38; -3,402823E38 to -1.401298E-45
Pay attention to the decimals.

? CSng(1.00000004749745E-03) = CDbl(0.001)
=> True
but:
? CDbl(CSng(1.00000004749745E-03)) = CDbl(0.001)
=> False

Round is included in Jet/ACE, isn't it?
=> Round with expression service:

Public Function VbaRound(ByVal Number As Variant, ByVal NumDigitsAfterDecimal As Long) As Variant
   VbaRound = Round(Number, NumDigitsAfterDecimal)
End Function

SELECT
    Round(CSng(0.00100000004749745),3) as AceRound,
    VBARound(CSng(0.00100000004749745),3) AS VbaRound
FROM ...

 

[The_Doc_Man]

? CSng(1.00000004749745E-03) = CDbl(0.001)
=> True
but:
? CDbl(CSng(1.00000004749745E-03)) = CDbl(0.001)
=> False

The question for your example is, "what is the actual effect of "type coercion" in each case?" You are doing a comparison of two REAL numbers but of different types.

I think it is clear enough that somehow these two cases are different in the nature of the coercion. Unfortunately, the VBA Language reference doesn't address expressions in the "Immediate" window so I don't have a clear enough reference, and Access is not open-sourced so we can't easily determine exactly how that proceeds.