Using checkbox to filter query by text in a column

[SteveFlash]

When I placed the button in the Form design view it asked me what the form does and I went to miscellaneous then run query and selected the query I have set up.

[ssanfu]

So when you click on the button, the query is displayed separate from the form?

Open the query "LunchQuery" in design view, switch to SQL view, Copy the SQL and paste it in here so I can see it. Please.

[SteveFlash]

Here it is:

SELECT AllStudents.FNAME, AllStudents.LNAME, AllLunch.[HotCold], AllLunch.type, AllLunch. Sum(AllLunch.AMOUNT) AS SumOfAMOUNT, AllLunch.YEARofLunch
FROM AllLunch RIGHT JOIN AllStudents ON AllLunch.ID = AllStudents.[ID]
GROUP BY AllStudents.FNAME, AllStudents.LNAME, AllLunch.[HotCold], AllLunch.type, AllLunch.YEARofLunch
HAVING (((AllLunch.YEARofLunch)=IIf([Forms]![LunchList]![2012Check],2012,-1)));

[ssanfu]

What is the field "AllLunch.YEARofCon"?

[SteveFlash]

It was a miss type. It should be YearofLunch and I updated it in the post.

[ssanfu]

Rather than try and type everything, look at this example. I tried to get it as close as possible to what you gave me. (Access 2000)

Again, this is not the best way, but it is the way you wanted.
I enter only a few names. Look at the years before you check the check boxes. Then click "Search". "Show All" removes the filter.
I don't know all of your field names, but I did the best I could....


---------------
Note: "ID" is not a good name - it is not descriptive
"Type" is a reserved word in Access and shouldn't e used as object names.

[SteveFlash]

Whoa thanks. Where is the query and form design so I can try to learn how you did that and copy it?

[SteveFlash]

Ok. I figured that out. How would I change it so the results are not displayed below rather you click a button to launch the query in a another tab?

[ssanfu]

OK, I have to ask... Why do you open the query rather than using a form? It is better to view in a form.

Having said that, to open the query filtered will take more code. It will take a few minutes to modify the code.. (people still bother me with work )

[SteveFlash]

Thank you a ton for your help.

I would just rather have it open in a new window then the search window. I need to add the ability to search by dirffrent fields on this form and I don't want it to be crowded. Can it be opened in like another table form?

[ssanfu]

As you wish..

How is this?

[SteveFlash]

Awesome. So would this be easy for me to copy what you did here and change it over to another database? I had to change some of the fields to protect privacy. Do I just need to upload my two table and then change the names in the Macro?

[ssanfu]

Sure. If any of the object names change, check box names, the query name and/or the table names, you will have to change the code. As long as you understand what the code does, it is easy to import and adapt the code.

I have to say (again - sorry) that it would be better to use a form than open a query. It is easier to filter a form; you can still sort on different fields...
But as long as you are happy with what you have, I'll be happy too.