Encountered strange interaction between Excel data and MS Access Link / Import (1 Viewer)

ions

Access User
Local time
Today, 07:48
Joined
May 23, 2004
Messages
816
Hello Access Forum,

I have encountered strange behavior between Excel Data and MS Access. I have an Excel field that contains strings and numbers that contain decimals.

1724431936837.png


When I link to this Excel sheet or Import it in MS Access the last digit from the first number is truncated. (See below screenshots) The field is type short text from MS Access' perspective and I don't understand why it would truncate it. Note, it doesn't truncate the second number which is one digit shorter.

I have provided the source Excel file and the MS Access file in an attached zip file.

Thank you for your feedback and comments.

1724432047372.png

1724432118688.png
 

Attachments

On my machine the link provides an exact match. As far as the table data goes, you have not specified how you did the import, so we can't test your process.

Screenshot 2024-08-23 104139.png
 
Hello Access Forum,

I have encountered strange behavior between Excel Data and MS Access. I have an Excel field that contains strings and numbers that contain decimals.

View attachment 115757

When I link to this Excel sheet or Import it in MS Access the last digit from the first number is truncated. (See below screenshots) The field is type short text from MS Access' perspective and I don't understand why it would truncate it. Note, it doesn't truncate the second number which is one digit shorter.

I have provided the source Excel file and the MS Access file in an attached zip file.

Thank you for your feedback and comments.

View attachment 115758
View attachment 115759
If the field in the Access table is Short Text, how many characters are allowed in that field? Open it in design view, please, and take a screenshot showing that property.
 
On my machine the link provides an exact match. As far as the table data goes, you have not specified how you did the import, so we can't test your process.

View attachment 115760
I am running MS Access 2019. What are you running MarkK? I imported using the Import Wizard.
 
If the field in the Access table is Short Text, how many characters are allowed in that field? Open it in design view, please, and take a screenshot showing that property.
It's 255 George. I also tried it with Long Text. Same results on my Machine.

1724436840549.png
 

Attachments

  • 1724436821602.png
    1724436821602.png
    18.1 KB · Views: 26
365, but I doubt this is a bug that varies with Office versions. There is something else going on.
 
365, but I doubt this is a bug that varies with Office versions. There is something else going on.
I believe the issue is with my Excel. The Excel Link doesn't show the 2nd digit and that is where I think he problem originates.

When I imported using SQL Server as a .CSV the 2nd digit appears
 
have you refreshed the link, or tried to link it again and see if you get the same result?
 
Thank you MarkK and George for your feedback. I was able to figure out the cause.

When the Field Format in Excel is General the MS Access Link Truncates the 2nd Decimal Digit. When the Field Format is Number with 2 decimals the MS Access Link to the Excel file shows the 2nd Decimal Digit.

The Excel file I provided in the attached .Zip has the Format Number with 2 decimals that is why it worked for you both. I suspect if you change the Format back to General you will have the 2nd decimal truncated? Please confirm, I would appreciate it.

1724441685861.png


1724441726268.png




1724441891015.png

1724441836773.png
 

Attachments

  • 1724441821660.png
    1724441821660.png
    6.7 KB · Views: 26
Thanks for explaining what you found. Excel is more likely to play games with values in this manner than Access. Good work tracking it down.
 
Strange, as after all, they are just formats?
 
Thanks for explaining what you found. Excel is more likely to play games with values in this manner than Access. Good work tracking it down.
Thank you George. We actually met in PADU back in the day.
 
Strange, as after all, they are just formats?
Exactly, that is what makes this puzzling. I consider this to be a bug. Note, it only starts to falter when you increase the number of Numerical Digits.
 
Thanks for explaining what you found. Excel is more likely to play games with values in this manner than Access. Good work tracking it down.
Bitter experience says you should ALWAYS define formats for every cell in use in spreadsheet to avoid such problems.
 

Users who are viewing this thread

Back
Top Bottom