Versatile filter for report created from a form

[bluemoir]

I'm designing a contact database for all industry contacts related to our
company. I want users to be able to print a contact list with any combination
of selections on it. Here are their options:

Category: Office or Field
Department: Administration, Construction, Estimating, Warranty
Builder: (there are 23 different home builders we deal with)

For example I want to be able to print all contacts that work in the field
for the Construction and Warranty departments for every builder. Another
example would be I want to print a list of all the contacts for the warranty
departments for a select 3 customers.

I currently have the form set up with check boxes divided into 3 sections
where they can select what they want on the report (Build the filter for the
source query for the report) and then open and print it. I have the check
boxes populating text boxes with the search criteria but I doubt this is
neccesary - either way I can't seem to write a Where statement that will
allow me to filter by any or all of these criteria.

Any help would be much obliged. Let me know if more info would help.

Ross

 

[Duane Hookom]

I generally create code that checks to see if the user has made a selection
on a form like:

Dim strWhere as String
strWhere = " 1= 1"
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [DateField]>=#" & _
Me.txtStart & "# "
End If

I also use a generic function that builds an " IN " clause for strWhere
based on a multi-select list box. My DoCmd.OpenReport then uses the
dynamically built where clause (strWhere).

 

[Allen Browne]

Download this sample database, which demonstrates how to build the Where
clause from any combination of unbounds controls:
http://allenbrowne.com/unlinked/Search2000.zip
For Access 2000 and later.