Query based on Multiple Combo Boxes

J

Jalnac74

Registered User.
Local time
Yesterday, 22:55
Joined
Apr 4, 2015
Messages
23
Hi,

I am trying to filter a subform based on the values in 5 different combo boxes. The boxes can be empty or filled and can be any combination of the selections. I have the following but some of the combos don't generate a filtered list. To clarify, this query is based on another similarly filtered query as this was the only way I could think of to get the desired result.
1645353084934.png


Any advice would be appreciated!

Ian
 
please show the sql to your query - the screenshot does not show the whole picture
 
CJ_London said:
please show the sql to your query - the screenshot does not show the whole picture
Click to expand...
Here it is

SELECT [Copy Of filterTT-qry].Day, [Copy Of filterTT-qry].lesson, [Copy Of filterTT-qry].teacher, [Copy Of filterTT-qry].room, [Copy Of filterTT-qry].lsa, [Copy Of filterTT-qry].class, [Copy Of filterTT-qry].subject, [Copy Of filterTT-qry].name
FROM [Copy Of filterTT-qry]
WHERE ((([Copy Of filterTT-qry].subject)=[forms]![TT-EDIT]![selsubj]) AND (([Copy Of filterTT-qry].name)=[forms]![tt-edit]![selname])) OR ((([Copy Of filterTT-qry].subject)=[forms]![tt-edit]![selsubj]) AND ((IsNull([forms]![tt-Edit]![selname]))<>False)) OR ((([Copy Of filterTT-qry].name)=[forms]![tt-edit]![selname]) AND ((IsNull([forms]![tt-Edit]![selsubj]))<>False)) OR (((IsNull([forms]![tt-Edit]![selname]))<>False) AND ((IsNull([forms]![tt-Edit]![selsubj]))<>False));


I want to filter by Day, Lesson, Class, name.

thanks,
 
Jalnac74 said:
I am trying to filter a subform based on the values in 5 different combo boxes.
Click to expand...
to take into account all combinations of several fields, and of different types, I do this
- I am writing a request for the form without selection conditions
- in the form of a selection field strictly above the fields of the ribbon form
- by pressing the fpoisk code to set the filter string from individual tokens
- I have a button to reset the filter and clear the search fields
Code: 
Sub fpoisk()
''On Error GoTo err00
Dim s1, s2
Me.Refresh
s1 = ""
'''''''''''''''''''''''''
s2 = Replace("" & Me.wCLIENT, "'", "?")
If Len(s2) > 0 Then
s1 = s1 & " and  CLIENT like '*" & s2 & "*'"
End If
'''''''''''''''''''''''''
s2 = "" & Me.wLastName
If Len(s2) > 0 Then
s1 = s1 & " and  LastName like '*" & s2 & "*'"
End If
'''''''''''''''''''''''''
s2 = "" & Me.wCity
If Len(s2) > 0 Then
s1 = s1 & " and  City =" & s2
End If
'''''''''''''''''''''''''
s2 = "" & Me.wTel
If Len(s2) > 0 Then
s1 = s1 & " and  tel like '*" & s2 & "*'"
End If
'''''''''''''''''''''''''
s2 = "" & Me.wdata
If Len(s2) > 0 Then
s1 = s1 & " and  data >=#" & Format(Me.wdata, "mm\/dd\/yyyy") & "#"
End If
'''''''''''''''''''''''''
s2 = "" & Me.wdata_start
If Len(s2) > 0 Then
s1 = s1 & " and  data <=#" & Format(Me.wdata_start, "mm\/dd\/yyyy") & "#"
End If
'''''''''''''''''''''''''
s2 = "" & Me.wdata_end
If Len(s2) > 0 Then
s1 = s1 & " and  data <=#" & Format(Me.wdata_end, "mm\/dd\/yyyy") & "#"
End If
'''''''''''''''''''''''''
Debug.Print s1

If Len(s1) > 5 Then
Me.Filter = Mid(s1, 5)
Me.FilterOn = True
End If
End Sub
 
you are using reserved words in your field names (day and name). Using reserved words can cause problems. At the very least, surround them with square brackets which usually solves the problem. See this link
docs.microsoft.com

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.
docs.microsoft.com
Also not a good idea to have spaces in table or field names

I think your criteria is not quite right - I think

OR IsNull([forms]![tt-Edit]![selname]))<>False

should be

OR [forms]![tt-Edit]![selname] is null

so a record will be returned if the user has not entered a value in selname

I've rewritten your query, should be able to copy paste to a new query

Code: 
SELECT [Day], lesson, teacher, room, lsa, class, subject,[name]
FROM [Copy Of filterTT-qry]
WHERE (subject=[forms]![TT-EDIT]![selsubj] OR [forms]![tt-Edit]![selsubj] is null) AND ([name]=[forms]![tt-edit]![selname] OR [forms]![tt-Edit]![selname] is null) AND (subject=[forms]![tt-edit]![selsubj] OR  [forms]![tt-Edit]![selsubj] is null)

When you first execute it, Access will add back all the tablenames and brackets
 
One trick with the QBE.

When you have one of these complex WHERE clauses where the QBE goes crazy with parentheses, save the query in SQL view and NEVER view or save it in QBE view. Access will leave the string unmolested:)
 
CJ_London said:
you are using reserved words in your field names (day and name). Using reserved words can cause problems. At the very least, surround them with square brackets which usually solves the problem. See this link
docs.microsoft.com

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.
docs.microsoft.com
Also not a good idea to have spaces in table or field names

I think your criteria is not quite right - I think

OR IsNull([forms]![tt-Edit]![selname]))<>False

should be

OR [forms]![tt-Edit]![selname] is null

so a record will be returned if the user has not entered a value in selname

I've rewritten your query, should be able to copy paste to a new query

Code: 
SELECT [Day], lesson, teacher, room, lsa, class, subject,[name]
FROM [Copy Of filterTT-qry]
WHERE (subject=[forms]![TT-EDIT]![selsubj] OR [forms]![tt-Edit]![selsubj] is null) AND ([name]=[forms]![tt-edit]![selname] OR [forms]![tt-Edit]![selname] is null) AND (subject=[forms]![tt-edit]![selsubj] OR  [forms]![tt-Edit]![selsubj] is null)

When you first execute it, Access will add back all the tablenames and brackets
Click to expand...
thanks. ive done that. your code was much neater!!
 
SHANEMAC51 said:
to take into account all combinations of several fields, and of different types, I do this
- I am writing a request for the form without selection conditions
- in the form of a selection field strictly above the fields of the ribbon form
- by pressing the fpoisk code to set the filter string from individual tokens
- I have a button to reset the filter and clear the search fields
Code: 
Sub fpoisk()
''On Error GoTo err00
Dim s1, s2
Me.Refresh
s1 = ""
'''''''''''''''''''''''''
s2 = Replace("" & Me.wCLIENT, "'", "?")
If Len(s2) > 0 Then
s1 = s1 & " and  CLIENT like '*" & s2 & "*'"
End If
'''''''''''''''''''''''''
s2 = "" & Me.wLastName
If Len(s2) > 0 Then
s1 = s1 & " and  LastName like '*" & s2 & "*'"
End If
'''''''''''''''''''''''''
s2 = "" & Me.wCity
If Len(s2) > 0 Then
s1 = s1 & " and  City =" & s2
End If
'''''''''''''''''''''''''
s2 = "" & Me.wTel
If Len(s2) > 0 Then
s1 = s1 & " and  tel like '*" & s2 & "*'"
End If
'''''''''''''''''''''''''
s2 = "" & Me.wdata
If Len(s2) > 0 Then
s1 = s1 & " and  data >=#" & Format(Me.wdata, "mm\/dd\/yyyy") & "#"
End If
'''''''''''''''''''''''''
s2 = "" & Me.wdata_start
If Len(s2) > 0 Then
s1 = s1 & " and  data <=#" & Format(Me.wdata_start, "mm\/dd\/yyyy") & "#"
End If
'''''''''''''''''''''''''
s2 = "" & Me.wdata_end
If Len(s2) > 0 Then
s1 = s1 & " and  data <=#" & Format(Me.wdata_end, "mm\/dd\/yyyy") & "#"
End If
'''''''''''''''''''''''''
Debug.Print s1

If Len(s1) > 5 Then
Me.Filter = Mid(s1, 5)
Me.FilterOn = True
End If
End Sub
Click to expand...
i'll give this a go. thanks!
 
You must log in or register to reply here.

Similar threads

A
Solved Combo box query
Replies
3
Views
166
AlliCarr
A
C
Combo boxes
Replies
1
Views
102
pbaldy
pbaldy
S
"All" option on a combo box, with Inner Join
Replies
11
Views
309
SteveC24
S
Navy Ken
can values in a table be used as criteria to filter a query
2 3
Replies
52
Views
694
Pat Hartman
P
J
Solved Pulling data from three tables into multiple queries.
Replies
3
Views
116
Jomat
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom