If field is blank ignore query criteria 1

[KLewisBPM]

I have a form with date, customer name and product.

I have a query that uses each field as its filter criteria i.e. [forms]![myform]![date]

I would like the query to not use the field if it is left blank. That way I can choose to filter on whichever field I want.

I hope this clear!

Kind Regards

Kelley Lewis

 

[dhookom]

The typical method is to use something like:
[forms]![myform]![date] Or [forms]![myform]![date]
Is Null

If this query is a record source for a report or form, I usually remove dynamic criteria and specify the filtering in the where condition of DoCmd.Open....


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[KLewisBPM]

Hi dhookom

Thanks for your quick response. It is a record source for a report. I am only used to using the filtering criteria can you elaborate on "Removing dynamic criteria and specify filtering........"

Kind Regards

Kelley Lewis

 

[dhookom]

The solution depends on whether the field is included in the report's record source. If so, my typical code in the command button to open the report would be:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
   strWhere = strWhere & " And [DateField]>=#" & _
      Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
   strWhere = strWhere & " And [DateField]<=#" & _
      Me.txtEndDate & "# "
End If
DoCmd.OpenReport "rptYourName", acPreview, , strWhere

This type of coding allows you to open the report independent of the having some form open. It handles Nulls in criteria and is good for coding multi-select list boxes.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]