This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(265.6, 10%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJK%3C/text%3E%3C/svg%3E)
I have a report that I built for a client.
On my PC when I run the report it pull the data correctly. Example value of 1234.56
When I hop on screen sharing with the client, send them the same workbook and have them run it that same value is stripped of everything after the decimal place. So appears like 1234
The column was initially formatted as General. (not a problem on my PC)
While on their PC I changed it to Number with 2 decimal places and ran the query again.
Value now shows as 1234.00
It seems to me as though there is something on their PC that is stripping off everything after the decimal.
Or rounding to a whole number during the data pull.
But I can't figure out what is doing it.
Their ODBC connection settings appear to be identical to mine.
I have reviewed my excel setting to see if there is something in Excel that can round or strip everything after a decimal place for data coming from an SQL data connection and not finding anything.
I am at a loss for what to look for here.
What could be causing Excel to strip data brought in from an SQL query/connection of it's value after the decimal?
The problem this causes is that when I Pivot the data the sums aren't matching their GL.
Is there any chance this has something to do with version of Excel?
Clients
Mine is
One thing I would check is regional settings. Are they the same?
Okay so my client uninstalled IBM I access for windows and then reinstalled it and ODBC.
Went from a 2010 version to a 2015 version.
This fixed the issue.
Would have never thought to try that.
Thanks anyhow for the chat.
Glad to hear you got it working! Convert your comment to answer and accept it.
....and while you are at it, remove sql-server-general from the tags, since this had no relation to SQL Server at all, as you are querying AS400.
Yeah, wasn't really sure what to tag this as at the time. Just knew I was using SQL to query a database through Excel.
Anyhow, I removed the SQL tag and turned my reply to an accepted answer.
Thanks again for you time :)
I aggree with ErlandSommarskog, please go to Control Panel > Clock and region > Region > Formats tab > Additional Settings > Numbers tab, especially the set of "No. of digits after demical".
Then go to Excel > Options > Advanced > Make sure the "Automatically insert a demical point" is not ticked.
If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
@John Kourtesis
If therre is any update, please let me know.
Thank you for the reply. Unfortunately still not working.
I made sure that his region settings matched mine and that his region settings matched mine.
Now his Auto-insert setting was unchecked but set to 2 as seen below.
I had him change that to be checked and set the value to ZERO as mine is set and still no go with it checked or unchecked.
This sounds more like an Excel problem to me,, and being an SQL Server guy I may not be able to help that much. However, it maybe could be helpful if you posted screenshots of the steps you take to run the query?
So basically I using excel replace formula's to change date variables in the SQL statement.
Then I have VBA code that grabs the final SQL string, forms an ODBC connection, pulls the data, clears out the previous data from an excel table and adds the new data to it.
The user experience is just changing date ranges and clicking a run button.
They then see a spinning wheel while VBA does its work.
Once complete the code lands them on the excel tab with the data pulled.
I have attached a screenshot PDF. But not sure it will help.
Worst case, I think my client understands that this is a PC issue beyond the scope of my support as it works fine on other PC's at his office.
Would still be nice to determine what causes this if possible though.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
What exactly does the value in the top bar show? The top bar in Excel will show the actual value, not the formatted value.
Your problem sounds like a display problem in Excel, not a problem with the data.
