Single becomes Double when exporting to Excel - looking for workaround

When exporting from a database that runs on both Access 2003 and 2010, to Excel, we have some problems in the numbers getting some additional decimals on them.

LIke 1.6 becomes 1.60000002384185

After some googling I found out, that it's because the numbers are stored as Single in the access table, but when writing it into the Excel file it becomes a double... so I'm looking for a way to get around this, with out changing the table structure ?
LVL 1
Mik MakConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
There is no such thing as "single" or "double" in Excel, it just stores the value it is given.
Since Double (in Access) produces the most decimal places(15), this is the most accurate number type to export with. (Access does this when Importing Excel data into Access as well)

Then in Excel you can format it to display the way you want.
0
Jeffrey CoachmanMIS LiasonCommented:
I have had success formatting and converting the number in a query, then exporting the query to Excel...

Something like this perhaps:

SELECT ID, YourNumberField, CDbl(Format([YourNumberField],"00.00")) AS YourFormattedNumberField
FROM Table1;

JeffCoachman
0
Jeffrey CoachmanMIS LiasonCommented:
...But here again, you will lose the actual "stored" value in Access (with the full decimal accuracy)

Note that the Full number may be needed for sensitive calculations...

Finally note that this type of "rounding" (actually destroying the decimal values) may be against company or accounting policies.
(Rounding the stored values can allow for miniscule theft of monies, that add up over time, or at best destroy the accuracy of sensitive calculations)

I strongly recommend that you import the full value and then format the value in Excel for the needed number of decimals...
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Mik MakConsultantAuthor Commented:
Well, I have a table with decimal values stored in a Single field in Access - but I cannot get the same number of decimal Places exportet to Excel - as in my example:

Access field value 1.6 becomes 1.60000002384185 when exported to Excel - I know I can manipulate the "view" in Excel to only show ie. 2 decimal places - but the underlying field value in Excel show the full (wrong) decimal places - which is pretty unfortunate to say the least :)
0
Mik MakConsultantAuthor Commented:
Because of the layout of the Excel file, I'm exporting the values using the Excel object model, not via a Query/export

 objActiveWkb.Worksheets(WorkSheetNo).Cells(rk, kol + 1).Value = rstRebG.Fields("TilskPrL")
0
Jeffrey CoachmanMIS LiasonCommented:
1.
Because of the layout of the Excel file, I'm exporting the values using the Excel object model, not via a Query/export
I am no expert in Excel VBA (only Access vba)
But I am reasonably sure you can format the cell with code similar to this:
objActiveWkb.Worksheets(WorkSheetNo).Cells(rk, kol + 1).Value = rstRebG.Fields("TilskPrL")
objActiveWkb.Worksheets(WorkSheetNo).Cells(rk, kol + 1).NumberFormat="0.00"
(...You can request that this Q be linked to the Excel zone for more assistance...)

2. This constraint of having to transfer the data via Excel automation was not made clear originally..., so also consider loading the Access data into an excel "Template", where the formatting is already in place.
(Again a link to this Q in the Excel Topic area will probably help)

;-)

JeffCoachman
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" but when writing it into the Excel file it becomes a double"
Would that be a bigger bang for the buck ?
:-)
0
Mik MakConsultantAuthor Commented:
Hi Jeff

Thank you for your inputs - but I'm not looking for a different "format/view" in Excel - I'm looking for a way to make sure the contents of the Excel cell matches exactly the value in the Access field - this can not be done the way you describe unfortunately - it only changes the way the cell contents "look".

I know you said Excel doesnt have "Double" format but if you try and convert 1.6 in single value to a double value - you get exactly what the content of the Excel cell is: 1.60000002384186

Try this Little function in Access/Excel/VBA
Function TestSingleToDouble()
  Dim s As Single
  Dim d As Double
  s = 1.6
  d = s
  MsgBox (d)

End Function
0
Mik MakConsultantAuthor Commented:
Hi DatabaseMX

Yes I guess so :)
0
Jeffrey CoachmanMIS LiasonCommented:
Odd because when you export the table from Access to Excel, it keeps the same value (ex.:1.6)
...

Then all I can suggest is that you use a query like I posted and bring that into Excel instead of the table.
You can bring the "modified" field into the query in the same column Position as the original field, so as not to crash your import code
(and not include the original field in the query)
This way your code will see the query just like it would see the table...

So wherever you are declaring the recordset based on the TableName, change it to the query name.

For example change:
Set rstRebG=CurrentDb.OpenRecordset("YourTablename")
to:
Set rstRebG=CurrentDb.OpenRecordset("YourQueryname")

Or if it is SQL:
Change this:
Set rstRebG=CurrentDB.OpenRecordset("SELECT * FROM YourTable")
to:
Set rstRebG=CurrentDB.OpenRecordset("SELECT * FROM YourQuery")

In other words wherever you are referencing the name of the table, substitute the name of the query...

Perhaps MX can see another way, but that's all I got right now.

Hope this helps...

JeffCoachman
0
Mik MakConsultantAuthor Commented:
But unfortunately all the values in the table doesn't have the same number of decimals, so using a Format on the value will have an impact on values with more decimals than specified in the Format specification.
0
Jeffrey CoachmanMIS LiasonCommented:
Oh, OK, ...Sorry I could not help more
0
Mik MakConsultantAuthor Commented:
You've helped plenty - made me think about a couple of workarounds, but nothing quite fits the bill yet :)

Thank you
0
Jeffrey CoachmanMIS LiasonCommented:
Ok, perhaps one more try...

You can examine the data in Access and determine the max number of decimals for the numbers, then adjust my query for that number of decimals for all the numbers...
So some number will have more decimals than they need (any extra decimal places will be all zeros), but at least the underlying "value" will be the same as in Access (without the "mystery" decimal values Access outputs now...)

Or we could look at it from this way...
What is the reason why this import can only be done from Excel?
(what is so "custom" about this output?)

In other words, can you show us a sample of the Access data, and a sample of the Excel file that gets created?
If the values you are getting now are wrong, and there is no fix, would you keep them that way, just to have this custom Excel output?

Or, ...could you export from Access and get the "correct" values and except that, even if the layout is not perfect?

JeffCoachman
0
Mik MakConsultantAuthor Commented:
It's not Access the exports the weird values - everything is ok in Access - its when the insertion into Excel is done the conversion takes place - hence my suspicion about a Double value in Excel :)

The Excel sheet is only a temporary base - after that it gets imported into SAP - so I've suggested to the customer that we be whatever is need to be done to the data before going into SAP, in Access - skip Excel, and then Exchange the data via a csv file - no data can be garbled in that :)

It's still very weird about the conversion though :)
0
Jeffrey CoachmanMIS LiasonCommented:
<It's still very weird about the conversion though :-) >
yeah, ...it is...

I was wondering why this was not more of an issue here, then I realized that most of the time the data is exported in Access, --> to Excel...


I just tried saving single data to a text file...
It seems to only export with two decimal places, ...regardless of the number of decimal places in the source table values...
It looks like it is using the standard rounding protocol, but you would have to test this to be sure...

If you select the option to :"Export Data with formatting and layout", ...it will give you each number with the correct decimal places, ...but it will add in simulated "column and row" formatting, which will give you headaches when bringing this into SAP.

I'm am trying...
;-)

Keep me posted with what your determinations are...

;-)

Jeff
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mik MakConsultantAuthor Commented:
If you setup an export specification it shoud be possible to export with more than 2 decimals. But I'll have a look at it next week.

I'll close the question because I don't think we'll get any closer - thank you for all your inputs.
Enjoy your weekend
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.