Using Multiple Parameter Queries

[Peter425]

I have a literature reference database with about 1000 technical journal references in it. I've established a form and the related query from which the user can select a date range, a category, and a geographic area, among others, from drop down lists. When they click on a command button, the search is executed.

Here's the problem: there are about 6 fields that I want the user to be able to select their search criteria from. However, most users will only use two or three of the fields. While I can get results by filling in EVERY search field with a criteria, leaving even one field blank returns zero records. How can I set this up where the user can only make entries in the fields that they want to search on?

Thanks,
Peter

 

[BNPMike]

Just make sure one of your options in each category passes all records and make that the default. eg Date < 12/12/5678, Category not null etc.

The user then just amends the one(s) of interest.

 

[dhookom]

If the results are presented in a form or report (they should be) then I would create a dynamic where clause to use in the operform/openreport line.

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND [SalesDate] >=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND [SalesDate] <=#" & _
Me.txtEndDate & "# "
End If
If Not IsNull(Me.cboSalesPerson) Then
strWhere = strWhere & " AND [SalesPersonID] =" & _
Me.cboSalesPerson
End If
'etc
DoCmd.OpenReport "rptSales", acPreview, , strWhere




Duane
MS Access MVP
Find out how to get great answers faq219-2884.