Hey, I want to add a form to my database which allows the user to perform a filter by selecting either yes or no from the fields on that form.

I have 6 fields on my form, each has combo box with yes or no.

For example if a user wanted to only view the records of people who had a yes in option 1, they would:

- select yes in option 1, and no in the other 5 options.

- click the open form button which opens another form and only shows the records of people who have yes in option 1.


Im not sure if I need some kind of query, and the search results form based on that query.

Ive added an example databse which might explain a little better.

Thanks for any help, much appreciated.

Dan

Have a query with your six fields associated to the combo box.

The in the first field of the query in the parameter, reference it to the combo box control on your form.

Forms!frmSelectOption!cbo1

For the second field reference it to the second combo box.

Forms!frmSelectOption!cbo2

and so on.

Then in your on click. Hide your selection form and open a form (continuous mode) that is bound to your filter query and your result will be displayed.

HTH

Jim

Thanks for your reply Jim, I had a go at doing what you said, Im sure it will work but i was a bit usure on what to do, Ive done an example database in access 97 format trying to do what you said, and wondered if you could just have a quick look at it form me.

Thank you!

Dan

Hi Dan

Got it working.

You had referenced the form as i had suggested as frmSelectOption, but you called your form SelectForm. You have to rename your form frmSelectOption and then it works.

Also, I would restructure your result form as a continuous form. Use the continuous form wizard and this will work. (Your form is set out like a single form in rows, whereas the continuous form is set out in columns. This means you can display 30 records on a screen with a continuous form, but with the form you have it might be 3 at the most.)

Finally Dan.

In what I donloaded, you are not using Lesinsky naming conventions for your tables, queries, forms etc. In fact, you have the Search Results form named with a space in between.

Never use spaces in naming anything. They come back and bite you in coding at a later stage. Also, using the designator tbl for Table and qry for query, or qapp for append query, or qdel for a delete query helps you no end when you have hundreds of queries in an appliation.

Get back if you need more help.

Jim

Thanks Jim, I cant believe it was that simple!

I do have 1 problem though. (ill try explain using just 2 fileds)


Records in my table:

Record 1 - Option1 (Yes) - Option2 (No)

Record 2 - Option1 (No) - Option2 (Yes)


If I then use my search form and select "yes" for both options , it wont bring up any results because it will only show records which have "yes" in both options.

Ideally I wanted the search to show up any records which have yes in that column, so the search should show up these two records.

Is this really difficult to do?

Thanks again Jim

Dan

Well Dan

Now we are talking about something that is not easy.

If you think that in what you are saying above, the data can not return a no if the criteria is yes.

What I have done is added a wild card creator on to the form, so if a null is selected, it returns a wildcard and thus will pull data wether it is a yes or a no.

You can see this in frmSelectOption and Query2 returns the results.

Basically, what you are asking is for the control to be yes or no, when a yes is added.

I have tried a few things but can not figure out how to do this.

Take a look at the wild card thing and see if this might help.

HTH and sorry. I will keep thinking though.

Jim

I have the same problem and am trying to think of doing it along the lines of having a query that would display each result, with a user-defined field that does an IF command for each field, if the first field in a record equals the field search field then the user-definied field has a value of 1, if it doesn't then it goes to the second field, and goes on through each field.

Below is the code I've created for my project - problem is, it is too complex for the expression builder!

calc1 =if( [AWARD]![DIVERSITY] = Forms![frmBetterSearch]![DIVERSITY] ,1,if( [AWARD]![BEST PRACTICE] = Forms![frmBetterSearch]![BEST PRACTICE] ,1,if( [AWARD]![sustainability] = Forms![frmBetterSearch]![sustainability] ,1,if(( [AWARD]![innovation] = Forms![frmBetterSearch]![innovation] ,1,if(( [AWARD]![value] = Forms![frmBetterSearch]![value] ,1,if(( [AWARD]![beacon] = Forms![frmBetterSearch]![beacon] ,1,if(( [AWARD]![chartermark] = Forms![frmBetterSearch]![chartermark] ,1,if(( [AWARD]![lpsa] = Forms![frmBetterSearch]![lpsa] ,1,if(( [AWARD]![emas] = Forms![frmBetterSearch]![emas] ,1,if(( [AWARD]![partnership] = Forms![frmBetterSearch]![partnership] ,1,if(( [AWARD]![whole life cost] = Forms![frmBetterSearch]![whole life cost] ,1,if(( [AWARD]![customers] = Forms![frmBetterSearch]![customers] ,1,if(( [AWARD]![consultation] = Forms![frmBetterSearch]![consultation] ,1,if(( [AWARD]![accessibility] = Forms![frmBetterSearch]![accessibility] ,1,if(( [AWARD]![DIsability] = Forms![frmBetterSearch]![DIsability] ,1,if(( [AWARD]![social inclusion] = Forms![frmBetterSearch]![social inclusion] ,1,if(( [AWARD]![community] = Forms![frmBetterSearch]![communITY] ,1,if(( [AWARD]![shared priorities] = Forms![frmBetterSearch]![shared priorities] ,1,if(( [AWARD]![minorities] = Forms![frmBetterSearch]![minorities] ,1,if(( [AWARD]![children] = Forms![frmBetterSearch]![children] ,1,if(( [AWARD]![youths] = Forms![frmBetterSearch]![youths] ,1,if(( [AWARD]![parish council] = Forms![frmBetterSearch]![parish council] ,1,if(( [AWARD]![leadership] = Forms![frmBetterSearch]![leadership] ,1,if(( [AWARD]![strategy] = Forms![frmBetterSearch]![strategY] ,1,0))))))))))))))))))))))))

mjr511