Add a column to an Append Query and populate with Date from Form

[Indigo]

I am hoping someone can help me out here.... I am trying to write an append query to track employee attendance. I have a table with the EmployeeInfo and another table where I am tracking Attendance records by date. I tried and was successful with this query:

INSERT INTO Attendance ( EmployeeNo, AbsenceDate, AbsenceReason, Comments, GroupID )
SELECT EmployeeInfo.EmployeeNo, Date() AS ShiftDate, EmployeeInfo.AbsenceReason, EmployeeInfo.AbsenceComment, EmployeeInfo.GroupID
FROM EmployeeInfo 
WHERE (((EmployeeInfo.AbsenceReason) Is Not Null));

However, instead of using "Date()" I want to populate the AbsenceDate field with the date on my form, i.e. attendance may be entered after midnight, but the ShiftDate is the date before midnight. This is already accounted for on my form in this field: Forms!frmAttendanceTracking!ShiftDate

However, when I change my query as follows:

INSERT INTO Attendance ( EmployeeNo, AbsenceDate, AbsenceReason, Comments, GroupID )
SELECT EmployeeInfo.EmployeeNo, Forms!frmAttendanceTracking!ShiftDate AS ShiftDate, EmployeeInfo.AbsenceReason, EmployeeInfo.AbsenceComment, EmployeeInfo.GroupID
FROM EmployeeInfo 
WHERE (((EmployeeInfo.AbsenceReason) Is Not Null));

The date field is blank in my query results. Can anyone steer me in the right direction? Thank you.

 

[CJ_London]

you need to add the date field into the first row of your code

 

[Indigo]

I'm sorry, I'm not sure what you mean exactly?

As a parameter? Okay, yes, this works now! Thank you!

PARAMETERS Forms!frmAttendanceTracking!ShifttDate DateTime;
INSERT INTO Attendance ( EmployeeNo, AbsenceDate, AbsenceReason, Comments, GroupID )
SELECT EmployeeInfo.EmployeeNo, Forms!frmAttendanceTracking!ShiftDate AS ShiftDate, EmployeeInfo.AbsenceReason, EmployeeInfo.AbsenceComment, EmployeeInfo.GroupID
FROM EmployeeInfo 
WHERE (((EmployeeInfo.AbsenceReason) Is Not Null));

 

[CJ_London]

sorry, was distracted, didn't mean to send the post