Access query returning dates out of range input

Question

I have constructed a query to select all dates between a date range which is inputted by the user. When the user selects the date before todays date, the current date or a day after the current date it brings back dates not within the date range.

Please see my examples below:

Before todays date returning incorrect dates in range:

before todays date returning incorrect dates in range

Selecting date 3 days after current date query query perfectly:

selecting date 3 days after current date query query perfectly

 Option Compare Database

  Private Sub Command121_Click()

  Dim strCriteria, task As String



strCriteria = "([PromisedDeliveryDate] >= #" & Me.DateFrom & "# And [PromisedDeliveryDate] <= #" & Me.DateTo & "#)"
task = "select * from SageOrderLine_Live where (" & strCriteria & ")"
DoCmd.ApplyFilter task

End Sub

EDIT: How would I add to query by the customeraccountnumber as well? I have tried to add it on but it's bringing up an error:

  DoCmd.ApplyFilter _
"select * from SageOrderLine_Live where " & _
"[PromisedDeliveryDate] >= " & Format(Me.DateFrom, "\#mm\/dd\/yyyy\#") & " and " & _
"[PromisedDeliveryDate] <= " & Format(Me.DateTo, "\#mm\/dd\/yyyy\#") & " and " & _
"[CustomerAccountNumber] = " & Me.CustomerAccount & ""

The "Enter parameter box" is appearing when selecting search:

Enter parameter box appearing when selecting search

Lee Mac Lee Mac · Accepted Answer · 2019-11-09T12:53:54

When referencing date values within a SQL statement, you will need to ensure that such dates adhere to the expected format of:

#mm/dd/yyyy#

Else, a date such as 08/11/2019 (8^th November 2019) will be interpreted within the SQL as 11/08/2019 (11^th August 2019), yielding the results that you are witnessing.

You can achieve this using the Format function:

Format(Me.DateFrom, "\#mm\/dd\/yyyy\#")

Here, the additional backslashes \ preceding the octothorpes & forward slashes act as Escape Characters, forcing MS Access to display the next character as a literal.

For example:

?Format(Date(), "\#mm\/dd\/yyyy\#")
#11/09/2019#

In your code, this might be written:

Private Sub Command121_Click()
    DoCmd.ApplyFilter _
    "select * from SageOrderLine_Live where " & _
    "[PromisedDeliveryDate] >= " & Format(Me.DateFrom, "\#mm\/dd\/yyyy\#") & " and " & _
    "[PromisedDeliveryDate] <= " & Format(Me.DateTo, "\#mm\/dd\/yyyy\#")
End Sub

Alternatively, you could reference the form controls directly within the SQL statement, in which case, no formatting is required:

Private Sub Command121_Click()
    DoCmd.ApplyFilter _
    "select * from SageOrderLine_Live where " & _
    "[PromisedDeliveryDate] >= [Forms]![YourFormName]!DateFrom and " & _
    "[PromisedDeliveryDate] <= [Forms]![YourFormName]!DateTo "
End Sub

(Updating YourFormName to the name of your form, of course).

EDIT: If you wish to add the CustomerAccountNumber to the filter, since this is a text field, you'll need to enclose the value with either single or double quotes, e.g. using single quotes:

DoCmd.ApplyFilter _
"select * from SageOrderLine_Live where " & _
"[PromisedDeliveryDate] >= " & Format(Me.DateFrom, "\#mm\/dd\/yyyy\#") & " and " & _
"[PromisedDeliveryDate] <= " & Format(Me.DateTo, "\#mm\/dd\/yyyy\#") & " and " & _
"[CustomerAccountNumber] = '" & Me.CustomerAccount & "'"

Or double-quotes:

DoCmd.ApplyFilter _
"select * from SageOrderLine_Live where " & _
"[PromisedDeliveryDate] >= " & Format(Me.DateFrom, "\#mm\/dd\/yyyy\#") & " and " & _
"[PromisedDeliveryDate] <= " & Format(Me.DateTo, "\#mm\/dd\/yyyy\#") & " and " & _
"[CustomerAccountNumber] = """ & Me.CustomerAccount & """"

Access query returning dates out of range input

1 Answers