exception in query code 1

[MICKI0220]

I have this code that excludes selections that I make from a list box for the query. I need to add another list box called lstemployees and make the query also exclude employee names that are in this list box, can someone tell me where to put it in this code and what it's syntax should be?

Private Sub Command10_Click()
Dim stDocName As String
Dim itm As Variant
Dim strtype As String
Dim strsql As String
Dim qdf As DAO.QueryDef
DoCmd.SetWarnings False
For Each itm In Me.lstCompanies.ItemsSelected
strtype = strtype & """,""" & Me.lstCompanies.Column(0, itm)
Next

'Set up SQL string

strsql = "SELECT dbo_VP_TIMESHEETITEM.PERSONFULLNAME, " _
& "dbo_VP_TIMESHEETITEM.PERSONNUM, HISTORYJOBS.TEAM, " _
& "(Sum([timeinseconds]/3600)) AS Hours, dbo_VP_EMPLOYEEV42.EMPLOYMENTSTATUS, " _
& "HISTORYJOBS.MONTH, dbo_VP_TIMESHEETITEM.LABORLEVELNAME3, " _
& "dbo_VP_TIMESHEETITEM.LABORLEVELNAME4, dbo_VP_EMPLOYEEV42.BADGEEXPIRATIONDTM, " _
& "dbo_VP_TIMESHEETITEM.EVENTDATE, dbo_VP_TIMESHEETITEM.LABORLEVELNAME5 " _
& "FROM (dbo_VP_TIMESHEETITEM INNER JOIN HISTORYJOBS " _
& "ON dbo_VP_TIMESHEETITEM.LABORLEVELNAME3 = HISTORYJOBS.JOB) " _
& "INNER JOIN dbo_VP_EMPLOYEEV42 ON dbo_VP_TIMESHEETITEM.PERSONNUM = " _
& "dbo_VP_EMPLOYEEV42.PERSONNUM " _
& "GROUP BY dbo_VP_TIMESHEETITEM.PERSONFULLNAME, " _
& "dbo_VP_TIMESHEETITEM.PERSONNUM, HISTORYJOBS.TEAM, " _
& "dbo_VP_EMPLOYEEV42.EMPLOYMENTSTATUS, HISTORYJOBS.MONTH, " _
& "dbo_VP_TIMESHEETITEM.LABORLEVELNAME3, dbo_VP_TIMESHEETITEM.LABORLEVELNAME4, " _
& "dbo_VP_EMPLOYEEV42.BADGEEXPIRATIONDTM, dbo_VP_TIMESHEETITEM.EVENTDATE, " _
& "dbo_VP_TIMESHEETITEM.LABORLEVELNAME5, HISTORYJOBS.JOB " _
& "HAVING ((HISTORYJOBS.TEAM)=[Forms]![Response]![Text7]) " _
& "AND ((dbo_VP_EMPLOYEEV42.EMPLOYMENTSTATUS)=""Active"") " _
& "AND ((HISTORYJOBS.MONTH)=[Forms]![Response]![txtmth]) " _
& "AND ((dbo_VP_EMPLOYEEV42.BADGEEXPIRATIONDTM)=#1/1/3000#) "
'& "AND (HISTORYJOBS.JOB) NOT IN ("
If Trim(strtype & "") <> "" Then
strsql = strsql & "AND HISTORYJOBS.JOB NOT IN (" & Mid(strtype, 3) & """)"
End If

strsql = strsql
'& Mid(strtype, 3) & """)"

'Check if a query called query1 exists
'If it does not exist, create it.
'If it does exist, permanently change it
If DLookup("Name", "MSysObjects", "Name= 'Query1'") <> "" Then
Set qdf = CurrentDb.QueryDefs("Query1")
qdf.SQL = strsql
Else
Set qdf = CurrentDb.CreateQueryDef("Query1", strsql)
End If
'Open the query
DoCmd.OpenQuery "detailbonusbyteam"
DoCmd.OpenQuery "updateworked"
DoCmd.OpenQuery "updateworked2"
stDocName = "Bonus Report per Team"
DoCmd.OpenReport stDocName, acPreview

End Sub

 

[Remou]

You can do the same thing, just copy the code that loops through the selected items and change the names to match the second list box, then insert an extra line to refer to the new list into the SQL.

 

[MICKI0220]

I've done that but it doesn't work. I need it to exclude both if there is information selected to be excluded. How do I make each string create one string. Here is the code.

Private Sub Command10_Click()
Dim stDocName As String
Dim itm As Variant
Dim itm2 As Variant
Dim strtype As String
Dim strtype2 As String
Dim strsql As String
Dim qdf As DAO.QueryDef
DoCmd.SetWarnings False
For Each itm In Me.lstCompanies.ItemsSelected
strtype = strtype & """,""" & Me.lstCompanies.Column(0, itm)
Next
For Each itm2 In Me.lstemployees.ItemsSelected
strtype2 = strtype2 & """,""" & Me.lstemployees.Column(0, itm)
Next

'Set up SQL string

strsql = "SELECT dbo_VP_TIMESHEETITEM.PERSONFULLNAME, " _
& "dbo_VP_TIMESHEETITEM.PERSONNUM, HISTORYJOBS.TEAM, " _
& "(Sum([timeinseconds]/3600)) AS Hours, dbo_VP_EMPLOYEEV42.EMPLOYMENTSTATUS, " _
& "HISTORYJOBS.MONTH, dbo_VP_TIMESHEETITEM.LABORLEVELNAME3, " _
& "dbo_VP_TIMESHEETITEM.LABORLEVELNAME4, dbo_VP_EMPLOYEEV42.BADGEEXPIRATIONDTM, " _
& "dbo_VP_TIMESHEETITEM.EVENTDATE, dbo_VP_TIMESHEETITEM.LABORLEVELNAME5 " _
& "FROM (dbo_VP_TIMESHEETITEM INNER JOIN HISTORYJOBS " _
& "ON dbo_VP_TIMESHEETITEM.LABORLEVELNAME3 = HISTORYJOBS.JOB) " _
& "INNER JOIN dbo_VP_EMPLOYEEV42 ON dbo_VP_TIMESHEETITEM.PERSONNUM = " _
& "dbo_VP_EMPLOYEEV42.PERSONNUM " _
& "GROUP BY dbo_VP_TIMESHEETITEM.PERSONFULLNAME, " _
& "dbo_VP_TIMESHEETITEM.PERSONNUM, HISTORYJOBS.TEAM, " _
& "dbo_VP_EMPLOYEEV42.EMPLOYMENTSTATUS, HISTORYJOBS.MONTH, " _
& "dbo_VP_TIMESHEETITEM.LABORLEVELNAME3, dbo_VP_TIMESHEETITEM.LABORLEVELNAME4, " _
& "dbo_VP_EMPLOYEEV42.BADGEEXPIRATIONDTM, dbo_VP_TIMESHEETITEM.EVENTDATE, " _
& "dbo_VP_TIMESHEETITEM.LABORLEVELNAME5, HISTORYJOBS.JOB " _
& "HAVING ((HISTORYJOBS.TEAM)=[Forms]![Response]![Text7]) " _
& "AND ((dbo_VP_EMPLOYEEV42.EMPLOYMENTSTATUS)=""Active"") " _
& "AND ((HISTORYJOBS.MONTH)=[Forms]![Response]![txtmth]) " _
& "AND ((dbo_VP_EMPLOYEEV42.BADGEEXPIRATIONDTM)=#1/1/3000#) "
'& "AND (HISTORYJOBS.JOB) NOT IN ("
If Trim(strtype & "") <> "" Then
strsql = strsql & "AND HISTORYJOBS.JOB NOT IN (" & Mid(strtype, 3) & """)"
End If
If Trim(strtype2 & "") <> "" Then
strsql = strsql & "AND dbo_VP_TIMESHEETITEM.PERSONNUM NOT IN (" & Mid(strtype2, 3) & """)"
End If
strsql = strsql
'& Mid(strtype, 3) & """)"

'Check if a query called query1 exists
'If it does not exist, create it.
'If it does exist, permanently change it
If DLookup("Name", "MSysObjects", "Name= 'Query1'") <> "" Then
Set qdf = CurrentDb.QueryDefs("Query1")
qdf.SQL = strsql
Else
Set qdf = CurrentDb.CreateQueryDef("Query1", strsql)
End If
'Open the query
DoCmd.OpenQuery "detailbonusbyteam"
DoCmd.OpenQuery "updateworked"
DoCmd.OpenQuery "updateworked2"
stDocName = "Bonus Report per Team"
DoCmd.OpenReport stDocName, acPreview

End Sub

 

[Remou]

You seem to have it very nearly right except for a space:

If Trim(strtype2 & "") <> "" Then
strsql = strsql & "[COLOR=red yellow] [/color]AND dbo_VP_TIMESHEETITEM.PERSONNUM NOT IN (" & Mid(strtype2, 3) & """)"
End If

 

[MICKI0220]

That still doesn't work. The first exclusion works still and works fine, but the code isn't putting the second exclusion into the query.

 

[MICKI0220]

I checked the query that this is supposed to set the exception query and what is happening is this...It isn't taking the selection I want as the criteria, it is taking the first record of my listbox as the exception, no matter what I choose. If I don't choose anything, the criteria becomes blank like it should, but no matter who I choose it grabs the first record and puts it into my query as "not like" someone.

 

[Remou]

You need to change this a little:

[tt]For Each itm2 In Me.lstemployees.ItemsSelected
strtype2 = strtype2 & """,""" & Me.lstemployees.Column(0, [red]itm2[/red])
Next[/tt]

 

[MICKI0220]

I am back testing this and it worked fine with one name as an exception, but when I choose multiple names from the list box I get a syntax error. This is the error

Run-Time error '3075'

Syntax error (comma) in query expression
'((Historyjobs.team)=[Forms]![Response]![Text7]) and ((dbo_vp_employeev42.employmentstatus)="active") and
((historyjobs.month) = [forms]![response]![txtmth]) and
((dbo_vp_employee42.badgeexpirationdtm)=#1/1/3000#) and
dbo_vp_timesheetitem.personfullname not like("Alexander,Keith L","Coyle,Gene A")'.



This is where it errors.

'Check if a query called query1 exists
'If it does not exist, create it.
'If it does exist, permanently change it
If DLookup("Name", "MSysObjects", "Name= 'Query1'") <> "" Then
Set qdf = CurrentDb.QueryDefs("Query1")
qdf.SQL = strsql
Else
Set qdf = CurrentDb.CreateQueryDef("Query1", strsql)
End If
'Open the query
DoCmd.OpenQuery "detailbonusbyteam"
DoCmd.OpenQuery "updateworked"
DoCmd.OpenQuery "updateworked2"
stDocName = "Bonus Report per Team"
DoCmd.OpenReport stDocName, acPreview

 

[MICKI0220]

Never Mind. I found the error. It was a space...

Thanks anyway