Insert Into Query filling table with all but one record

[VicM]

Folks,

I have an Insert Into query that fills a table. When I run the query as a simple Select query I get one more record than when the Insert query runs.

I've checked the filtering criteria, and the missing record in the Insert query meets the criteria which is proved when running the query as the simple Select query.

Has anyone ever run into this before?

Thanks,

Vic

 

[dhookom]

Typically a table will have constraints like unique indexes and relationships. Is the failing record caused by one of these rules?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[VicM]

Duane,
Thanks for your suggestion.

I did some more investigating and tried to run the delete table query and insert into table query manually. In the context of my code, I set the Warnings off. So I never saw the error messages causing the problem.

When running the queries manually, the Insert Into query indicated I would be inserting 2513 records into the table. When I allowed it to do so, I received a msg saying that 1 record had a 'Validation Rule Violation'. I reviewed all the Validation Rules in the receiving table and no fields had any Validation Rules listed.

The website I found about Validation Rules also indicated that if there were no Validation Rules, maybe there was a problem with text fields' "Required" rule being set to No or the fields' "Allow Zero Length" rule being set to No that was causing the problem. It also indicated if I allowed the Insert Into to continue without the offending record, a Paste Errors or Insert Errors table would be created. That never happened during the manual events. So I was still confused as to what the cause was.

I looked at the data in the table where the original data resides, and from which, the query was extracting to put into the receiving table. I looked at every 'empty' text field and entered each to perform a delete. Still the Insert Into query failed at that same record.

I then copied that record into an Excel spreadsheet and went thru each 'empty' text field and deleted the contents. Then I pasted that data back into the original table and reran the queries. The Insert Into worked this time and all records were inserted into the receiving table.

But at this point, I still don't know why this issue appeared since both times I 'deleted' the contents of 'empty' text fields with only the second time (the Excel version of deleting) working.

I will need to understand this so that I can prevent this issue from occurring in the future.

Vic

 

[dhookom]

Do all of your fields allow zero length strings? Tables also have validation rules.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016