Run-time error '3075': Syntax error (missing operator) in Access 2013 but not in 2010

[mishash]

Hello dear experts.
In my frmListOfOrders I have unbound StartDate and EndDate text boxes as well as search buttons for several predefined intervals (btnCurrentMonth, btnLastMonth etc.).

The underlying code for the search sub is:

Sub Search()
Dim strCriteria, task As String
Me.Refresh
If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
    MsgBox "Missing dates interval", vbInformation, "Enter dates interval"
    Me.StartDate.SetFocus
Else
    strCriteria = "(OrderDate >= #" & Me.StartDate & "# and OrderDate <= #" & Me.EndDate & "#)"
    task = "select * from qryListOfOrders where (" & strCriteria & ")"
    DoCmd.ApplyFilter task
End If
End Sub

The code for the buttons is (instance of btnCurrentMonth):

Private Sub btnCurrentMonth_Click()
    Me.StartDate = DateSerial(Year(Date), Month(Date), 1)
    Me.EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)
    Call Search
End Sub

This setting works fine in one machine with Access 2010, but fails in other machine with Access 2013 and produces Run-time error '3075': “syntax error (missing operator) in query expressions '((OrderDate >= #01-Oct-2020# and OrderDate <= #31-Oct-2020#))' with debugging pointing to the line DoCmd.ApplyFilter task.

What might be the catch?

 

[arnelgp]

It has nothing to do with access version.
It has to do with locale date setting of the computer.
So better format your date variable:

"Orderdate between " & format(me.startdate, "\#mm\/dd\/yyyy\#") & " and " & format(me.enddate, "\#mm\/dd\/yyyy\#")

 

[mishash]

It has nothing to do with access version.
It has to do with locale date setting of the computer.
So better format your date variable:

"Orderdate between " & format(me.startdate, "\#mm\/dd\/yyyy\#") & " and " & format(me.enddate, "\#mm\/dd\/yyyy\#")

Thank you. This must be it!

 

[theDBguy]

Hi. Can post the code for Search, please? Thank you.

 

[mishash]

Hi. Can post the code for Search, please? Thank you.

t'was my fault. everything works fine with formatting

 

[theDBguy]

t'was my fault. everything works fine with formatting

Of course. I was just curious if the fix could have been applied to the calling sub. For example:

Me.StartDate=Format(DateSerial(Year(Date()),Month(Date()),1),"\#yyyy\-mm\-dd\#")

 

[mishash]

Of course. I was just curious if the fix could have been applied to the calling sub. For example:

Me.StartDate=Format(DateSerial(Year(Date()),Month(Date()),1),"\#yyyy\-mm\-dd\#")

This is the calling sub:

Private Sub btnCurrentMonth_Click()
    Me.StartDate = DateSerial(Year(Date), Month(Date), 1)
    Me.EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)
    Call Search
End Sub

I was wondering how could I change the locale date setting on the other computer. The available Windows settings are Short Date and Long Date, but the OrderDate in my DB is Medium Date format. So I don't understand why there was incompatibility in the first place.

 

[theDBguy]

This is the calling sub:

Private Sub btnCurrentMonth_Click()
    Me.StartDate = DateSerial(Year(Date), Month(Date), 1)
    Me.EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)
    Call Search
End Sub

I was wondering how could I change the locale date setting on the other computer. The available Windows settings are Short Date and Long Date, but the OrderDate in my DB is Medium Date format. So I don't understand why there was incompatibility in the first place.

Hi. I was hoping you could also post the code for the called Sub, so I can see if we could modify the calling Sub, or if you're stuck with fixing the called Sub (Search).

 

[mishash]

Hi. I was hoping you could also post the code for the called Sub, so I can see if we could modify the calling Sub, or if you're stuck with fixing the called Sub (Search).

Do you mean this:

Sub Search()
Dim strCriteria, task As String
Me.Refresh
If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
    MsgBox "Missing dates interval", vbInformation, "Enter dates interval"
    Me.StartDate.SetFocus
Else
    strCriteria = "(OrderDate >= #" & Me.StartDate & "# and OrderDate <= #" & Me.EndDate & "#)"
    task = "select * from qryListOfOrders where (" & strCriteria & ")"
    DoCmd.ApplyFilter task
End If
End Sub

 

[theDBguy]

Do you mean this:

Sub Search()
Dim strCriteria, task As String
Me.Refresh
If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
    MsgBox "Missing dates interval", vbInformation, "Enter dates interval"
    Me.StartDate.SetFocus
Else
    strCriteria = "(OrderDate >= #" & Me.StartDate & "# and OrderDate <= #" & Me.EndDate & "#)"
    task = "select * from qryListOfOrders where (" & strCriteria & ")"
    DoCmd.ApplyFilter task
End If
End Sub

Yes, thank you. So, try modifying the calling Sub like I showed you earlier except take out the hash tags, because you already have them in the called Sub. Don't forget to do the EndDate as well.

Sent from phone...