Encountered strange interaction between Excel data and MS Access Link / Import

[ions]

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.

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.

 

[MarkK]

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.

 

[GPGeorge]

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.

 

[ions]

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.

 

[ions]

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.

 

[MarkK]

365, but I doubt this is a bug that varies with Office versions. There is something else going on.

 

[ions]

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

 

[MarkK]

have you refreshed the link, or tried to link it again and see if you get the same result?

 

[ions]

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.

 

[GPGeorge]

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.

 

[Gasman]

Strange, as after all, they are just formats?

 

[ions]

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.

 

[ions]

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.

 

[GPGeorge]

Thank you George. We actually met in PADU back in the day.

In Portland? That was a lifetime ago. There were some really strong meetings.

 

[DickyP]

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.