Multiple filter condition from combo box

[crossy5575]

Another quick one!!!
I have a form with a combobox1 on it showing;

All
both UK & SA
UK
SA
USA
Other

This is populated by a table which has 4 columns; Id, text (from Above) country1, and country2
country1 and country2 are hidden but are shown on textbox1 and textbox2 (only to solve this problem As you cant call column(3) in a query criteria i need to work around.)

I need to filter a list of staff based on the dropdown box contents.
Thus filter on UK, SA, and USA (easy)
all (easy (*))
Both UK and SA - need help!!!!

the problem i face is that with only 255 charaters there is not enough space to do embedded if statements plus not sure how it will work.
so far i have thought that if i add a field and have an expression where

[country] Like ([Forms]![addpublic]![text2] Or ([Forms]![addpublic]![text2])) where the criteria is true

I should get a result, however because text 2 for all other criteria is blank it doesnt filter anything.

Im sure this is easy, just cant think around it.

 

[Isskint]

hi crossy5575

You can not multi select on a combobox. You would need to replace it with a listbox. You are pretty much on it from there. The normal practice is to use the afterupdate event of the listbox to 'build' the where criteria. You can then either use that to set the forms filter OR use it to populate a hidden textbox that is used as the criteria source in a query.

 

[crossy5575]

Found the solution,
by storing the number selected by the combo box you can then write a sub to create a query based on 1. normal numbers, 2. pairs of numbers, 3. all numbers

Private Sub Combo23_Change()

If Combo23 = 2 Then
strwhere = "1"
strwhere2 = "2"

ElseIf Combo23 = 3 Then
strwhere = "2"
ElseIf Combo23 = 4 Then
strwhere = "1"
ElseIf Combo23 = 5 Then
strwhere = "4"
ElseIf Combo23 = 6 Then
strwhere = "5"
Else
strwhere = "0"
End If
Call temp

End Sub

Sub temp()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim qdf2 As DAO.QueryDef
Dim strSQL As String
Dim strSQL2 As String
Set db = CurrentDb
Set qdf = db.QueryDefs("2special")
Set qdf2 = db.QueryDefs("2names")

If Combo23 = 2 Then
strSQL = "SELECT SpecialDates.id, SpecialDates.xDate, SpecialDates.xDesc, [12a Country].Country, SpecialDates.Country " & _
"FROM [12a Country] RIGHT JOIN SpecialDates ON [12a Country].ID = SpecialDates.Country " & _
"WHERE (((SpecialDates.Country)=" & strwhere & ") OR (((SpecialDates.Country)=" & strwhere2 & ")));"

strSQL2 = "SELECT [01 Name].ID, [01 Name].Cname, [01 Name].Sname, [12a Country].Country, [12b Holsub].ayear " & _
"FROM [12a Country] INNER JOIN (([01 Name] INNER JOIN [12b Holsub] ON [01 Name].[ID] = [12b Holsub].[staff no]) INNER JOIN [09 Jobs] ON [01 Name].ID = [09 Jobs].Staffno) ON [12a Country].ID = [12b Holsub].hcountry " & _
"WHERE ((([12b Holsub].ayear)=Year(Date())) AND ((([12b Holsub].hcountry)=" & strwhere & ") OR (([12b Holsub].hcountry)=" & strwhere2 & ")));"

ElseIf Combo23 = 1 Then
strSQL = "SELECT SpecialDates.id, SpecialDates.xDate, SpecialDates.xDesc, [12a Country].Country, SpecialDates.Country " & _
"FROM [12a Country] RIGHT JOIN SpecialDates ON [12a Country].ID = SpecialDates.Country ;"

strSQL2 = "SELECT [01 Name].ID, [01 Name].Cname, [01 Name].Sname, [12a Country].Country, [12b Holsub].ayear " & _
"FROM [12a Country] INNER JOIN (([01 Name] INNER JOIN [12b Holsub] ON [01 Name].[ID] = [12b Holsub].[staff no]) INNER JOIN [09 Jobs] ON [01 Name].ID = [09 Jobs].Staffno) ON [12a Country].ID = [12b Holsub].hcountry " & _
"WHERE ((([12b Holsub].ayear)=Year(Date())));"

Else
strSQL = "SELECT SpecialDates.id, SpecialDates.xDate, SpecialDates.xDesc, [12a Country].Country, SpecialDates.Country " & _
"FROM [12a Country] RIGHT JOIN SpecialDates ON [12a Country].ID = SpecialDates.Country " & _
"WHERE (((SpecialDates.Country)=" & strwhere & "));"

strSQL2 = "SELECT [01 Name].ID, [01 Name].Cname, [01 Name].Sname, [12a Country].Country, [12b Holsub].ayear " & _
"FROM [12a Country] INNER JOIN (([01 Name] INNER JOIN [12b Holsub] ON [01 Name].[ID] = [12b Holsub].[staff no]) INNER JOIN [09 Jobs] ON [01 Name].ID = [09 Jobs].Staffno) ON [12a Country].ID = [12b Holsub].hcountry " & _
"WHERE ((([12b Holsub].ayear)=Year(Date())) AND (([12b Holsub].hcountry)=" & strwhere & "));"

End If

qdf.Sql = strSQL
qdf2.Sql = strSQL2


Me.Refresh

Set qdf = Nothing
Set qdf2 = Nothing
Set db = Nothing

End Sub


Hope this helps someone