Code on Query

[Lyncroft]

Had to admit defeat I'm afraid. I got the following code to work to produce a dynamic query using 3 multiselect list boxes. Works fine. The 3 listboxes are lstConsulation1, lstStakeholder1 and lstCountry1.

However, it only works if I make a selection from all 3 lists. If I leave one out I get a 3075 error. Could some kind person make a suggestion as to how I can amend the code so that a user can leave any of the lists unselected. I guess it must be the SQL statement but have tried various options without success! Thanks.

Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim SQL As String
Dim sConsultation1 As String
Dim sStakeholder1
Dim sCountry1 As String
Dim sCriteria As String
Dim varItem As Variant

' build criteria string for selected locations.
For Each varItem In Me.lstConsultation1.ItemsSelected
sConsultation1 = sConsultation1 & ",'" & Me.lstConsultation1.ItemData(varItem) & "'"
Next varItem
sConsultation1 = Mid(sConsultation1, 2) ' remove leading comma.
sConsultation1 = " [Consultation] in (" & sConsultation1 & ")"

' build criteria string for selected item classes.
For Each varItem In Me.lstStakeholder1.ItemsSelected
sStakeholder1 = sStakeholder1 & ",'" & Me.lstStakeholder1.ItemData(varItem) & "'"
Next varItem
sStakeholder1 = Mid(sStakeholder1, 2) ' remove leading comma.
sStakeholder1 = " [Stakeholder] in (" & sStakeholder1 & ")"

' build criteria string for selected item classes.
For Each varItem In Me.lstCountry1.ItemsSelected
sCountry1 = sCountry1 & ",'" & Me.lstCountry1.ItemData(varItem) & "'"
Next varItem
sCountry1 = Mid(sCountry1, 2) ' remove leading comma.
sCountry1 = " [Country] in (" & sCountry1 & ")"

' build SQL Statement.
If Me.lstConsultation1.ItemsSelected.Count = 0 And _
Me.lstCountry1.ItemsSelected.Count > 0 And _
Me.lstStakeholder1.ItemsSelected.Count > 0 _
Then
sCriteria = sConsultation1 & " AND " & sCountry1 & " AND " & sStakeholder1
Else
sCriteria = IIf(Me.lstConsultation1.ItemsSelected.Count > 0, sCountry1, sStakeholder1)
End If

 

[namliam]

'simply' add a check before every for loop:
If Me.lstConsultation1.ItemsSelected.Count > 0 then
For Each varItem In Me.lstConsultation1.ItemsSelected
sConsultation1 = sConsultation1 & ",'" & Me.lstConsultation1.ItemData(varItem) & "'"
Next varItem
sConsultation1 = Mid(sConsultation1, 2) ' remove leading comma.
sConsultation1 = " [Consultation] in (" & sConsultation1 & ")"
End if

Regards

 

[Shadez]

Your almost there all you need to do is supply a conditional IF statement after each for next look and only if there is data in your string do you add that field to your final query

Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim SQL As String
Dim sConsultation1 As String
Dim sStakeholder1
Dim sCountry1 As String
Dim sCriteria As String
Dim varItem As Variant
   
  ' build criteria string for selected locations.
For Each varItem In Me.lstConsultation1.ItemsSelected
      sConsultation1 = sConsultation1 & ",'" &    Me.lstConsultation1.ItemData(varItem) & "'"
Next varItem

IF sConsultation1 <> "" THEN
      sConsultation1 = Mid(sConsultation1, 2)  ' remove leading comma.
      sConsultation1 = " [Consultation] in (" & sConsultation1 & ")"
      sCriteria = "and " & sConsultation1 
END IF

   ' build criteria string for selected item classes.
For Each varItem In Me.lstStakeholder1.ItemsSelected
    sStakeholder1 = sStakeholder1 & ",'" & Me.lstStakeholder1.ItemData(varItem) & "'"
Next varItem

IF     sStakeholder1  <> "" then
    sStakeholder1 = Mid(sStakeholder1, 2)  ' remove leading comma.
    sStakeholder1 = " [Stakeholder] in (" & sStakeholder1 & ")"
    sCriteria =sCriteria &  " and " & sStakeholder1 
END IF
         
   ' build criteria string for selected item classes.
For Each varItem In Me.lstCountry1.ItemsSelected
    sCountry1 = sCountry1 & ",'" & Me.lstCountry1.ItemData(varItem) & "'"
Next varItem

IF sCountry1 <> "" then
     sCountry1 = Mid(sCountry1, 2)  ' remove leading comma.
     sCountry1 = " [Country] in (" & sCountry1 & ")"
     sCriteria =sCriteria &  " and " & sCountry1 
END IF

if sCriteria <> "" then
     sCriteria = Mid(sCriteria ,5)
else
     'no criterial selected
end if

 

[Lyncroft]

Thanks to you both very much. Got it to work finally!

 

[Shadez]

no probs