Using "Like" with Dates in a query 2

[mmogul]

I have a query where a user can select from and to dates on a form. I want the user to be able to leave the fields blank and the query would then retrieve all records.

I know how to do this with text:

ex: Like [Forms]![frmPriceTrendReport]![SelectedProduct] & "*"

But how do you structure this with date fields?

 

[dhookom]

You can use:

WHERE [DateField] BETWEEN Nz([Forms]![frmPriceTrendReport]![txtStartDate],#1/1/1900#) AND Nz([Forms]![frmPriceTrendReport]![txtEndDate],#12/31/2100#)

If this query is the Record Source of a form or report, and [DateField] is in the field list, I would write a little code to modify the Where Condition of the DoCmd.OpenForm/Report method.

Duane
Hook'D on Access
MS Access MVP

 

[mmogul]

Thanks. That does the trick.

 

[PHV]

A more generic way:

WHERE ([DateField] >= [Forms]![frmPriceTrendReport]![txtStartDate] OR [Forms]![frmPriceTrendReport]![txtStartDate] Is Null)
  AND ([DateField] <= [Forms]![frmPriceTrendReport]![txtEndDate] OR [Forms]![frmPriceTrendReport]![txtEndDate] Is Null)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mmogul]

Thanks. Elegant.

 

[dhookom]

I try to remove all dynamic criteria out of queries. I like to keep objects independent of other objects.

To expand on the recommendation regarding record sources for reports or forms:

   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 "rptName", acPreview, , , strWhere

Duane
Hook'D on Access
MS Access MVP