Insert into contains autonumber field that is primary key

[jac1]

I have seen a number of responses to other questions that indicate I should be able to do the following to insert a new record into an Access 2007 database and have my primary key autonumber field populated automatically:

INSERT INTO D_INVOICE (Org_Code, Prog_Code, [Year], Balance, Base_Rate, Affiliate_Rate, Cost_Per_Intern, Number_of_Interns, Date_of_Invoice, Date_Due, Date_Paid, Amount_Paid, Credit_Offset, Entered_By )
SELECT Org_Code, Prog_Code, Year, Balance, Base_Rate, Affiliate_Rate, Cost_Per_Intern, Number_of_Interns, Date_of_Invoice, Date_Due, "" AS Date_Paid, "" AS Amount_Paid, Credit_Offset, [Entered By] AS Entered_By
FROM q_org_invoicing_records;

What actually happens is that a record is inserted into my table, but the primary key (which is autonumbered) is left blank. Can anyone explain why this might be happening. The primary key is not contained in the insert.

[NeoPa]

I would suggest checking the design again carefully. An AutoNumber field should work exactly as you expected it to.

[jac1]

I have figured out the problem, although I still don't understand the behavior. The field "" as Amount_Paid was actually incompatible because Amount_Paid is configured as a number. I changed it to 0 and the autonumbering is now working as expected.

[NeoPa]

jac1:
The field "" as Amount_Paid was actually incompatible because Amount_Paid is configured as a number.

I assume you mean (value "" of). If so, then that certainly makes sense.