QBF problem with multiple combo boxes

[Damon Cherne]

Here is my issue. I have created a form called “Search” that has several
unbound combo boxes, one for each of the attribute that match a column
in the Table [Movie Titles]. Each combo box has input from a separate
table that limits what the uses can select. IE. The Genre table feeds a
list of options for the Genre combo box.

My goal is to have the user select the attributes they want to use as
search criteria and then feed those selections from the combo boxes into
the query that is executed when you select the “Query” button action on
the form. The results could then either go to a subform within the main
Search form or a separate datasheet view that is generated based on the
Query results. How are fields that are left blank handled? If you can or
are willing to assist then please let me know. I can send a zip version
of the Access 2003 DB if you are able to assist.

Table Name = Movie Titles
TitleID
Title
Genre
Sub-Genre
Media
Etc… Several other attributes for each title.
Form for Query = Search
cbo_Title à gets Title from [Movie Titles] Table
cbo_Genre à gets Genre from Genre Table
cbo_Media à gets Media from Media Table

*** Sent via Developersdex http://www.developersdex.com ***

[pietlinden]

normally, where you have optional search criteria, you can either do
something like

SELECT...
FROM...
WHERE MyField LIKE "*" & Forms!MySearchForm!MySearchField & "*"

or you can build the search string on the fly... Kind of depends on
where you're going to dump your resultset to. If you're sending it all
to a report, you can build your filter criteria on your form, and pass
the completed filter (basically the entire Where clause minus the
"WHERE" keyword) and then open the report. The report would be based,
in your case, on an unfiltered query that joined all the tables in the
query together.

Hope that helps a little.

[Damon Cherne]

Here is what I cam up with but I still have issues when one or more of
the combo boxes are left blank. Any help with this.
SELECT *
FROM [Movie Titles]
WHERE (Forms!Form1!cbo_title=[Movie Titles].Title Or [Movie
Titles].Title Is Not Null And Forms!Form1!cbo_genre=[Movie Titles].Genre
Or [Movie Titles].Genre Is Not Null And Forms!Form1!cbo_subgenre=[Movie
Titles].[Sub-Genre] Or [Movie Titles].[Sub-Genre] Is Not Null And
Forms!Form1!cbo_media=[Movie Titles].[Media Types] Or [Movie
Titles].[Media Types] Is Not Null And Forms!Form1!cbo_origin=[Movie
Titles].Origin Or [Movie Titles].Origin Is Not Null And
Forms!Form1!cbo_edition=[Movie Titles].Edition Or [Movie Titles].Edition
Is Not Null And Forms!Form1!cbo_awards=[Movie Titles].Awards Or [Movie
Titles].Awards Is Not Null And Forms!Form1!cbo_favorites=[Movie
Titles].Favorites Or [Movie Titles].Favorites Is Not Null And
Forms!Form1!cbo_lastuser=[Movie Titles].LastUser Or [Movie
Titles].LastUser Is Not Null And Forms!Form1!cbo_curruser=[Movie
Titles].UserIdCuruser Or [Movie Titles].UserIdCuruser Is Not Null);

*** Sent via Developersdex http://www.developersdex.com ***