Need Assistance with Modifying CurrentDb.Execute in Order to Append Date <> "12/30/1899"

[skydivetom]

Hello Experts:

I need to obtain some assistance with tweaking my *CurrentDb.Execute* routine in order to append the correct date (from an unbound control).

Process:
1. Open form "F01_MainMenu"
2. Click on green command button "Run Append" (top right)
3. Close form


4. Open table "tbl_MonthlyBills"
4a. For each click on the command button (form), a record has been inserted.
4b. The problem is the date where [PAYDATE] = "12/30/1899"

Additional information:
- For testing only, now execute query "qryAppend".
- Here, a record has been inserted into "tbl_MonthlyBills" where [PAYDATE] = today's date. I do NOT want that either!!

VBA Background:
- In form "F01_MainMenu", I added the SQL from "qryAppend" into CurrentDb.Execute
- Then, I changed "SELECT qry_Payees_First_Half_Crosstab.Date,..." to "SELECT " & txt_Date_22nd & "
- ... where the unbound control "txt_Date_22nd" stores the desired date (i.e., "2/22/2024").
- Please note, when clicking the green "Run Append", a test message box is thrown displaying the date verifying the control stores the correct date.

My question:
How do I need to modify the CurrentDb.Execute line so that I append the value of "txt_Date_22nd" ("2/22/2024") vs. "12/30/1899" into the table?

'    Original SQL from the append query
'    Original SQL from the append query
'    CurrentDb.Execute "INSERT INTO tbl_MonthlyBills ( PAYDATE, 3676_SAVINGS, 4011_BILLPAY, TOTAL ) " & _
'                      "SELECT qry_Payees_First_Half_Crosstab.Date, qry_Payees_First_Half_Crosstab.[3676 (Savings)], qry_Payees_First_Half_Crosstab.[4011 (Bill Pay)], qry_Payees_First_Half_Crosstab.TotalExpenses " & _
'                      "FROM qry_Payees_First_Half_Crosstab;

    'Changed from: "SELECT qry_Payees_First_Half_Crosstab.Date,..."
    'To:           "SELECT " & txt_Date_22nd & ",
    CurrentDb.Execute "INSERT INTO tbl_MonthlyBills ( PAYDATE, 3676_SAVINGS, 4011_BILLPAY, TOTAL ) " & _
                      "SELECT " & txt_Date_22nd & ", qry_Payees_First_Half_Crosstab.[3676 (Savings)], qry_Payees_First_Half_Crosstab.[4011 (Bill Pay)], qry_Payees_First_Half_Crosstab.TotalExpenses " & _
                      "FROM qry_Payees_First_Half_Crosstab;"

[davegri]

I would give this a try:

Name that field:
"SELECT " & txt_Date_22nd & " As PAYDATE, qry_Payees_First_Half_Crosstab.[3676 (Savings)],


You may need delimiters:
"SELECT #" & txt_Date_22nd & "# As PAYDATE, qry_Payees_First_Half_Crosstab.[3676 (Savings)],

[skydivetom]

Davegri -- thank you for the prompt response.

The 2nd option (with delimiters) works great! Many thanks!!!

[davegri]

Davegri -- thank you for the prompt response.

The 2nd option (with delimiters) works great! Many thanks!!!

Happy to help and thanks for the star!