please help with a "where" statement

[KevinSlater]

hi does anyone know whats wrong with this bit of code?, the bit up to the "where" line works fine (ie if i take the where bit out it will work), however i would like to copy only employees on a particular shiftname, instead of copying all of them from the employee shift table.

Private Sub Form_AfterUpdate()
Dim SQL_Text As String
SQL_Text = "INSERT INTO ATTENDANCE (SHIFTNAME, SHIFT_DATE, EMPLOYEE_NUMBER) SELECT '" & Me.SHIFTNAME & "','" & Me.SHIFT_DATE & "'" & " ,EMPLOYEE_NUMBER FROM [EMPLOYEE SHIFT] WHERE ([EMPLOYEE SHIFT].SHIFTNAME)"

DoCmd.RunSQL (SQL_Text)
End Sub

 

[workmad3]

well, the most glaring ommission is what you are going to compare the shiftname to in the where clause.
You should have
([EMPLOYEE SHIFT].SHIFTNAME = <some comparison here>)

 

[KevinSlater]

ok this code is along the lines of what im trying to do but its not working:
(trying to copy any employees in employee shift table to another table (attendance) along with the shiftname i enter in the form

WHERE (([EMPLOYEE SHIFT].SHIFTNAME) ='""& Me.SHIFTNAME"")); "

 

[workmad3]

WHERE (([EMPLOYEE SHIFT].SHIFTNAME) ='"& Me.SHIFTNAME & "')); "

try the above

 

[bhe]

Try to create a string variable for your "WHERE" criteria, try this script

Private Sub Form_AfterUpdate()
Dim strCrit as string
Dim strSql As String

'------ Set your criteria here -----
strCrit = <Your criteria>

'------ Define SQL string ---------
strSql = "INSERT INTO ATTENDANCE (SHIFTNAME, SHIFT_DATE, EMPLOYEE_NUMBER) SELECT '" & Me.SHIFTNAME & "','" & Me.SHIFT_DATE & "'" & " ,EMPLOYEE_NUMBER FROM [EMPLOYEE SHIFT] WHERE ([EMPLOYEE SHIFT].SHIFTNAME='" & strCrit & "')"

'------ Execute SQL -----
DoCmd.RunSQL (strSql)


End Sub

Try it who knows it'll work

 

[KevinSlater]

Thanks workmad your code worked (apart from one extra bracket that wasnt needed at the end) so it was like this:

WHERE (([EMPLOYEE SHIFT].SHIFTNAME) ='"& Me.SHIFTNAME & "'); "

and thanks bhe i will try your way aswell as that seems like a good way to do it instead.