I looked through the threads in this topic, but didn't see anything like this.
I have an Access file in which I store a price list and other associated pricing data. I update this file, as needed, buy importing the data (in entirety) to the target table.
My Excel file has pricing to four decimals ($0.0000) accuracy (table spec: Currency field, 4 decimals). When I load my pricing data, all the fields in the table hold their integrity, except certain values for price; specifically: $0.0001, $0.0008, $0.0016, $0.0032, $0.0064, and $0.0128. These all convert to "$-00" when imported. Definite pattern here. All other values above $0.0128 appear to load properly.
I also have another Access file that uses this Excel data, but that file uses "links," rather than the native table, and it acts the same way; so, it doesn't appear to be my specific Access application file.
First, I tried "painting format" in my Excel file from pricing that did properly load to those that did not. When I uploaded, no change. I tried applying the format of the cells that didn't load to ones that did to see if it would prevent those cells from loading; it corrupted those records. This made me suspect a format problem in Excel.
Just to be sure, the next thing I tried was saving my Excel file to .CSV to strip out any Range Names, formatting, or other background attributes. Opened and saved the .CSV file back to Excel Workbook and used this new file for the import to the native table version and linked to the Access file using file links. Same result--zeros for the specific price values.
Out of about 5,000 records, I have only 131 prices that fall into this "bucket" of specific prices that won't load. Once the file was upload to my Access file with native tables, I manually updated the specific records to the correct pricing, and they hold their values just fine.
Is there something in the code of Access itself for imports that interferes with these values specifically? If not, could there be something in the formatting or background (such as name range) in the Excel source file that is causing this? I don't see how such info could make it through the Excel--->CSV--->Excel conversion.
This behavior is driving me crazy. Any insight is appreciated.
JIMK