Using checkbox to filter query by text in a column

[SteveFlash]

Hi. I am very new at Access.



I have two tables:

1) MasterTable, with standardize students names and address with a ID number.
2) LunchTable, a table that indicates what every student (w/ ID number) had for lunch year by Date, Hot/Cold, Food type, Amount (how much).

My end goal is to set up a query linked to my form which will allow me to build list of different types of combos from the lunch table. Like, lunches during 2010-2012 that was hot pizza with a amount from 3-7.

Right now I am trying to place check boxes on my form for years, so I can pick which years I want as apart of my result. In my Query Design view I have the field named "Year of lunch" from the table "LunchTable" and in the criteria I have place the following:

IIf([Forms]![SearchList]![Check2012],[LunchTable]![Year of Lunch] Like "2012",0)

but it doesn't seem to work correctly. When the box is not checked I want it to display all years.

EDIT: I want to add that the field named "Year of Lunch" has about 15 years in it going back to 1997. So the value can range from 1997-2012 and with this button I basically want to say only return values with "2012" in the "Year of Lunch" field.

[ssanfu]

Like "2012" without a wildcard is the same as "= 2012"

Will you always select only one year or will you select a span of years?

It is easier to have a text box to enter the year or a combo box to select the year.
Is the field type a number or is it text?

Also, you should only use letters, numbers and the underscore in names - try not to use spaces.

[SteveFlash]

Hi. Sometimes I will selected one year other times many years but when nothing is checked it should return all the data. The field type is a number.

[ssanfu]

OK, so walk me through the process.
You have a form named "SearchList". The data is displayed in another form or a report? How do you open the form/report?

[SteveFlash]

I have a form name SearchList, it will be used to create list of data from my tables. On this form is the first of hopefully many checkboxes, called "Check2012".

I have 2 tables:

1) MasterTable, with standardize students names and address with a ID number.
2) LunchTable, a table that indicates what every ID number had for lunch year by Date, Hot/Cold, Food type, Amount (how much).

I want my form when ran to produce a result of:

From MasterTable
-Fname
-Lname

From LunchTable
-Year of Lunch
-Hot or Cold
-Food Type (whatever text is in the column)
-Amount (How much the lunch cost)

When the checkbox2012 is checked I want the query to only produce result where the feild "Year of Lunch" equals "2012". I am just not sure what to put in the criteria line to make this happen.

[ssanfu]

How are the two tables related? What is the record source for the form "SearchList"? If it is a query, please post the SQL.

The problem with a check box used in this manner is that it is very rigid. To add another year, say 2013, you have to change your queries, forms and reports.

[SteveFlash]

Ok I am even closer. With this in the criteria for the "Year of Lunch" field:

IIf([Forms]![LunchList]![2012Check],2012,([LunchTable].[Year of Lunch]) Like "*")

When the box is checked that will only return lunches in 2012, but when it is not checked the query does not return anything. I need it to return everything if it is not checked regardless of the year. There most be something wrong with my False part, I put the Like "*" because I thought that would return everything.

[SteveFlash]

The two tables are related by an ID number the share. Like each Fname and Lname has an ID number and on the LunchTable the ID number appears instead of the Fname and Lname.

So it would be difficult to say have 7 check boxes that would be 2012,2011,2010,2009,2008,2007,2006 and prior to 2006?

[ssanfu]

It is do-able, but not good design and a lot more code. You have to check each checkbox to see if it is selected or not. The more check boxes, the more code.

You have to create a filter string "on-the-fly", set the form filter property and set the filter on property to TRUE.
Text boxes allow you to select one year or a range of years.
(Remember - no spaces in names)

[SteveFlash]

Do you know what is wrong with this criteria:

IIf([Forms]![LunchList]![2012Check],2012,([LunchTable].[Year of Lunch]) Like "*")

When I have the 2012Check box checked it only pulls results from 2012, which is what I want. But when it is not checked it shows no results, when I need it to show all results regardless of year. What do I need to have in the False part to have it show all results?

[ssanfu]

IIf([Forms]![LunchList]![2012Check],2012,([LunchTable].[Year of Lunch]) Like "*")

WHERE are you putting this?
Remember I do not know what your form looks like, if there is any code behind the form, how you requery the form.....
All I really know is that there are check boxes and the record source for the form is a query. (and the form name

Access does not like controls that begin with numbers. You should use something like "chk2012"...

[SteveFlash]

Sorry I am new at this.

I am put
IIf([Forms]![LunchList]![2012Check],2012,([LunchTable].[Year of Lunch]) Like "*")

in the criteria line under the Year of Lunch field name, all of which is in the query design view named LunchQuery. My form is called LunchList and it has one single checkbox titled 2012Check and then it has one button so it can run the query titled LunchQuery.

In the IIF statement I am first referencing the 2012Check box from the LunchList form and when it is checked I am trying to return entries from the LunchTable that have 2012 in the field. When it is not clicked I want all result to show regardless of year.

[ssanfu]

one button so it can run the query titled LunchQuery.

What is the code for the button?

[SteveFlash]

The name of it is "RunButton" or did you mean something else?

[ssanfu]

so it can run the query titled LunchQuery

How are you doing this?