Query Based on form checkbox

Question

I have no experience using a form to do a query in Access but that has been my assigned task. 

We have a database with many columns of data.  I have a form that gives people the option to filter the data based on values in each of those fields.  For example, you can choose the data that corresponds to a "class" of 1 or 2, you can choose the records corresponding to a "zone" of 1, 2 or 3.  Any number of things can be combined to produce the desired dataset.

My problem is I have not figured out how to modify the code in the qry (not the form) so that a limitation does not have to be included.  My code so far for "class" is [forms]![block model]![class] but if someone does not want to limit the data search by a value within the "class" field I have not found a way to tell this code to exclude it.

On my form I included a check box that a person can check if they want to limit the search using that field.  Is there a way to have that code look at whether or not that checkbox has a check in it and if it does to run that code in the query?  If the checkbox is not checked that code would not be run and no limits would be placed on that field.  I hope this makes sense.

This query will not update anything in the data itself and the form is connected to a database of our data.

Answer 1

Funny I searched several hours and found nothing ... until I posted this question.

Like IIf([forms]![block model]![check_class]=-1,[forms]![block model]![class],"*")

I answered my own question.  But the one I am looking for values greater than or equal to the value chosen in the list box is not working.  I still have to figure that one out.

Answer 2

"Fields are expensive, records are cheap". I fear you are "committing spreadsheet" by having many fields (columns) for your data. This is fine for Excel, but bad design for Access; I suspect you would do better to have THREE tables modeling the many-to-many relationship you now have embedded in each record.

That said, a criterion

([Fieldname] >= [Forms]![YourFormName]![Controlname] OR [Forms]![YourFormName]![Controlname] IS NULL)

will let you use form controls to specify criteria, but ignore that form control if it is blank.

Answer 3

now I am needing this same thing, Matt_Roger, can you elaborate more on what you did to make it work?

Answer 4

I got it to work with the like iif but thanks for your help.

Answer 5

Strange - not working.  I end up with a table with a record with a .2 record at the top (if searching for >=.2) and every other record has 0 in that field.

I understand your concern.  I was working with the data set in Excel and perhaps thinking that their constant need to get me to update these tables based on their criteria would mean they would have to renew my contract in July, they asked if I could create a form in Access where anyone would be able to pick their desired criteria and spit out data from the database.  So obviously I was not going out of my way to break up our database into access friendly tables.  I am very familiar with the format of Access but this is what was asked of me and this is what I will give them.  Perhaps it can be done in Excel as well but the boss suggested Access.