Filter From a Filter Table Using Three Fields - Filter Records in Microsoft Access


	Back to Filter Records Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Filter From a Filter Table Using Three Fields

Brian Heintzberger

9 months ago

I am having trouble making the filter table work looking at three fields. I can make it work with two fields but not three. example below
RegistrationYear = '2024-2025'AND RosterGroup = 'High Performance'
The above works
RegistrationYear = '2024-2025' AND RosterGroup = '10 & Under 90' AND Gender = 'F'
This does not. I made a filter table and a combo box to access the filters. I can make it work with two criteria but not three. I have been trying for three days and cannot get this figured out. My main continous form is based on a query that is based on two tables.
Thanks in advance

	Adam Schwanz @Reply 9 months ago
Does it work with just two when you use that & one?

	Adam Schwanz @Reply 9 months ago
You could also add `msgbox me.filter` and see what it's being set to

	Brian Heintzberger OP @Reply 9 months ago
It works with one AND but not Two ANDS. Would I add the msgbox me.filter at the end of the after event?

	Adam Schwanz @Reply 9 months ago
I thought the "&" might be the problem, that's why I was curious. You could probably just add to the end of the after event yea. Or you can make a seperate button with that code in it, and just push it after you set the filter.

	Brian Heintzberger OP @Reply 9 months ago
ok, can i filter by three fields or am I stuck with two? I can go to the query and enter the criteria there and it works with three but I really don't want to go to the query every time.

Brian Heintzberger OP @Reply

9 months ago

I tried this in the query that the form is based on using combo boxes from a "Search Form" and it still doesn't work. I guess i still don't quite understand. this is the sql from the query
FROM TBLAthlete INNER JOIN TBLSeason ON TBLAthlete.AthleteID = TBLSeason.AthleteID
WHERE (((TBLSeason.RegistrationYear)=[Forms]![FRMSearch]![CBOSelectYear] Or (TBLSeason.RegistrationYear) Is Null) AND ((TBLAthlete.Gender)=[Forms]![FRMSearch]![CBOSelectGender] Or (TBLAthlete.Gender) Is Null) AND ((TBLSeason.RosterGroup)=[Forms]![FRMSearch]![CBOSelectRoster] Or (TBLSeason.RosterGroup) Is Null));

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Filter Records.

Next Unseen

New Feature: Comment Live View