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?
Code:
' 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;
Code:
'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;"