Here is my code, the questions are below
Private Sub cmdProd_Click()
Dim strSQL As String
Dim strWhere As String
If Forms!Main!lstDeptP.ItemsSelected.Count = 0 Then
MsgBox “No departments are selected!!”, vbInformation
[Forms]![Main].SetFocus
Exit Sub
End If
For Each varItm In Forms!Main!lstDeptP.ItemsSelected
strWhere = strWhere & “, ” & Chr(34) & Forms!Main!lstDeptP.ItemData(varItm) & Chr(34)
Next varItm
strWhere = “PerDiem2Unit In (” & Mid(strWhere1, 3) & “)”
DoCmd.SetWarnings False
‘Creates the base table that Productivity will be moved over to Excel
strSQL = ” SELECT tblLearners.LastName, tblLearners.Nickname, tblLearners.Credential, ” & _
“tblLearnerDepartments.PerDiem2Unit, tblLearners.Inactive INTO tblProductivity ” & _
“FROM qryLimitDepartments INNER JOIN (tblLearners INNER JOIN tblLearnerDepartments ON ” & _
“tblLearners.LearnerID = tblLearnerDepartments.LearnerID) ON ” & _
“qryLimitDepartments.Department = tblLearnerDepartments.PerDiem2Unit ” & _
“WHERE (((tblLearners.Inactive) = 0)) ORDER BY tblLearners.LastName, tblLearners.Nickname”
‘ Execute it
DoCmd.RunSQL strSQL
End Sub
I need to limit the tblLearnerDepartments.PerDiem2Unit in the strSQL to the strWhere statement above. The above creates the tblProductiving but doesn’t limit to the selected departments.
I tried
“WHERE (((tblLearners.Inactive) = 0)) ORDER BY tblLearners.LastName, tblLearners.Nickname AND ” & strWhere
With the above statement I get a syntax error (missing Operator tblLearners.Nickname AND PerDiem2Unit().
I would appreciate any help. Thank you. Fay
