Query based on Multiple Combo Boxes (1 Viewer)

Jalnac74

Registered User.
Local time
Today, 15:53
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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:53
Joined
Feb 19, 2013
Messages
16,553
please show the sql to your query - the screenshot does not show the whole picture
 

Jalnac74

Registered User.
Local time
Today, 15:53
Joined
Apr 4, 2015
Messages
23
please show the sql to your query - the screenshot does not show the whole picture
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,
 

SHANEMAC51

Active member
Local time
Tomorrow, 01:53
Joined
Jan 28, 2022
Messages
310
I am trying to filter a subform based on the values in 5 different combo boxes.
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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:53
Joined
Feb 19, 2013
Messages
16,553
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
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:53
Joined
Feb 19, 2002
Messages
42,976
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:)
 

Jalnac74

Registered User.
Local time
Today, 15:53
Joined
Apr 4, 2015
Messages
23
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
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
thanks. ive done that. your code was much neater!!
 

Jalnac74

Registered User.
Local time
Today, 15:53
Joined
Apr 4, 2015
Messages
23
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
i'll give this a go. thanks!
 

Users who are viewing this thread

Top Bottom