Versatile filter for report created from a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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).
 
Back
Top