I spent some time going through your dB and, IMHO, there are design issues that should be addressed. But since it seems to be working for you, I'll focus on the problem at hand.
I am not surprised that there are design issues and if you want to help me out I am not going to stop you. Honestly I am scared almost everyday when I run this database since I know close to nothing about how to create a database like this. If this thing ever crashes completely I am not sure I'll do. Can you recommend some online Access classes that I can take to help me learn more about managing, understands and creating a database like this?
So in the Excel file CDHDR.XLSX, the first column is named "Object Value" (data type is TEXT). The data seems to be 10 characters - in this case they are numeric - and are left padded with zeros.
In the Access table "Credit hold table", the corresponding field is named "Order number" (data type Short Text) - but not left padded.
-----
Warning: you should test this on a COPY of your dB or make a copy of the query and make the changes in the query copy.
----
If you go the route of copying the query, you will rename the original: I added a suffix of "_Old".
Then query
copy name MUST be "Update - 020 - Credit hold table - append new data".
Now you will have
"
Update - 020 - Credit hold table - append new data"
"
Update - 020 - Credit hold table - append new data_OLD".
Edit the SQL of the query "Update - 020 - Credit hold table - append new data". Change the SQL to
Code:
INSERT INTO [Credit hold table] ( [Order number], [Date released], [Time released] )
SELECT VAL(CDHDR.[Object Value]), CDHDR.Date, CDHDR.Time
FROM CDHDR;
The VAL() function will convert the text to a number (which removes the leading zeros), Access will automatically convert the number to text and append it to the [Order number] field in "Credit hold table".
---
In my testing of the file you posted, there are 2387 rows in the spreadsheet, but only 2243 rows are appended.
An error dialog box says 144 records not appended due to Key violations. (something like that). Because the field "Object Value" is set as the PK field, duplicate values are not allowed in that field.
The Excel spreadsheet has duplicate values in the "Object Value" column, but with different Dates/Times. In the attached file, I highlighted the duplicates using yellow/gray and orange/blue.
There are 1 duplicate for value "0006551806" (2 total), but there are a few "Object Value" with up to 3 duplicates (4 total). Could this affect your report?
Windows 10 
Access 2013 64bit
Reply With Quote
to modify the report to have the sales order number column exported in a number format. They, (the High Priests), are just using some dialect of SQL to create the report, so it should be an easy fix.... at least it was in my experience. (I am surprised that the reports output was changed from TXT to XLSX.) 
Originally Posted by GMadd
