Number fields truncated and rounded

[Menno M Jansz]

Numeric number fields are truncated.

To replicate, create a test database table in MS Access, with a Number field, setting the Field Size to Decimal, with Scale of 2.

Enter values into this column with 2 decimal places, eg 12.34

Now quering this via UCanAccess, and it will return 12 rather than 12.34. An "export test.csv;" on the commandline version results in 12.0 in the file.

UCanAccess actually seems to round the numbers, as entering 34.56 will cause it to round up to 35.

Any ideas how to get UCanAccess to return the correct number with 2 decimal places?

Thanks,
Menno

[Marco Amadei]

It seems there is a bug in the "scale" handling, which is only related to "numeric"-->"decimal" setting and has not effect with other numeric types:
e.g., with the most-used "numeric"-->"double precision" or "numeric"-->"single precision" all works fine .
It's a major bug, so it will be patched, ASAP (I'm going to release the 2.0.5 in the next few days).
In the meantime you can use numeric single or double precision.
Thank you for reporting.
Marco

[Marco Amadei]

Patched on svn trunk.

[Menno M Jansz]

Hi Marco.

I noticed it worked fine with double. Unfortunately I have no control over the Access database as it's generated by a third party, so the fix is appreciated.

Thanks for your help.

Menno

[Marco Amadei]

Okey, the 2.0.5 (with the proper fix) has been released.
Thank you so much.
Cheers
Marco

[Menno M Jansz]

Hi Marco,

Just to let you know I've tested the fix on all my use cases and it all works fine now.

Let me know your Paypal account and I can send you some beer tokens.

Cheers,
Menno

[Marco Amadei]

Hi Menno,
unfortunatly I haven't yet a paypal account.
But thank you for your feedback! One beer (or two)... good idea!
Cheers Marco

[Menno M Jansz]

Well if you do get one let me know, and I'll be happy to send some beers your way :)

Cheers,
Menno

[Sathish]

Hi Marco,

I can see this is an old post. But the decimal truncation issue is still prevailing in the latest version of ucanaccess 4.0.4.

I have a decimal value stored in access db as 5.73333333333333. But im getting the output as 5 from the select query.

Could you please let me know how to resolve this issue.

Thanks
Sathish

[Gord Thompson]

How are you retrieving the value? Please show us some repro code. FWIW, this works fine for me from console.bat with a column named [dec] defined as DECIMAL(28,14):

UCanAccess>
Copyright (c) 2018 Marco Amadei
UCanAccess version 4.0.4
You are connected!!
Type quit to exit

Commands end with ;

Use:
   export [--help] [--bom] [-d <delimiter>] [-t <table>] [--big_query_schema <pathToSchemaFile>] [--newlines] <pathToCsv>;
for exporting the result set from the last executed query or a specific table into a .csv file

UCanAccess>SELECT * FROM table1;

·----+-----+------------------·
| id | txt |              dec |
·----+-----+------------------·
|  1 | foo | 5.73333333333333 |
·----+-----+------------------·

[Thomas F]

Hi,

The problem still exists if you are selecting decimal values from a Query (not from a table). For tables it works fine but if you create a simple query (In the console it is displayed at Loaded Queries) with a calculated column for example 5/3 it returns 1 instead of 1,666666666666667

I have tested it with version 4.0.4 on the UCanAccess console

It would be fine if you can fix this

Cheers
Thomas

[Gord Thompson]

@Thomas - That's because the Access Database Engine (ACE) and HSQLDB handle division differently when both columns are integer. ACE apparently casts the values to Double and then performs the division (1.666666666666667) while HSQLDB performs integer division (1).

The workaround is to modify the query to be explicit about what you want UCanAccess to do. So instead of this ...

UCanAccess>SELECT 5/3 AS foo FROM DUAL;

·-----·
| foo |
·-----·
|   1 |
·-----·

... use this ...

UCanAccess>SELECT CDbl(5)/CDbl(3) AS foo FROM DUAL;

·--------------------·
|                foo |
·--------------------·
| 1.6666666666666667 |
·--------------------·

... or just this:

UCanAccess>SELECT CDbl(5)/3 AS foo FROM DUAL;

·--------------------·
|                foo |
·--------------------·
| 1.6666666666666667 |
·--------------------·

[Thomas F]

Perfect works fine :) Thx for the quick answer!

Cheerz Thomas