query criteria question

[shiner]

Hi,
I have a form that is serving as an advanced search engine for my table. Using combo boxes, I have placed value lists items like *, True, and False. A user would go to this advanced search page and select all of the criteria for their desired records.

Leaving the form open - whose text boxes are not bound to anything, I have placed their addresses in the criterea with LIKE [form_name]![text_box_name] in hopes that it would perform the desired search. However, this is not working. If I hard code in place of the above sample TRUE - I get the desired results. How can I use the variables to make it work? The items that are not *,true,false all work (like state, HS graduation year, and gender) and are set up the same way. The T/F variables are set up as yes/no in the table - and work when I hard code TRUE so, I thought the value list would suffice given the same logic.

 

[shiner]

another screen shot of query

Here is a screenshot from the query design view - to see my syntax

 

[Jack Cowley]

I think you will find the answer to your search form here.

hth,
Jack

 

[shiner]

Thank you jack! I will try that in the morning when I get to the office. From the sounds of it, I need to code it in sql rather than the query wizard. The explanation looks thorough - I just hope I can follow the directions and make it happen.

 

[Jack Cowley]

It can be a bit overwhelming at first but the code example is very good so just be patient and you will get it to work... Good luck.

Jack

 

[Jon K]

In a Yes/No field, the value is -1 or 0. But [Forms]![form_name]![combo_box_name] returns the word True or False, not -1 or 0.


With the following alterations in the combo box, *,-1,0 can be returned:-

Column Count : 2
Column Widths : 0", x"
Row Source Type : Value List
Row Source : *;*;-1;True;0;False
Bound Column : 1


I have attached a small DB with two Yes/No fields as an illustration.

Hope it helps.


The attached DB is in Access 97 format. If you use Access 2000 or 2002, choose Convert and save as a new name when the DB is opened for the first time.

 

[shiner]

Jon,
Thank you! That example provided a crucial advance in my forms. Not only was it simple to incorporate, it answered my nagging question as to how to get the field to say one thing, but the db understand it as another. The filter works great, and now it is more user friendly!

Rather than showing the * as an option, I now show the user the option "Doesn't Matter" which is much more intuitive. Also, for my state filter, I show the whole names of the states (from a table) and the db see's the abbreviation for the state.

The more I dive into this project, the more significant these learning milestones become. Thank you all for your help!

 

[mingbin]

Jon,

How do you go one step further and specify what u want to search for in a earch box after clicking the criterias thru a combo box?