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

mishash

Member
Local time
Today, 21:24
Joined
Aug 20, 2020
Messages
52
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:
Code:
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):
Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:24
Joined
May 7, 2009
Messages
19,169
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

Member
Local time
Today, 21:24
Joined
Aug 20, 2020
Messages
52
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!
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 12:24
Joined
Oct 29, 2018
Messages
21,358
Hi. Can post the code for Search, please? Thank you.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:24
Joined
Oct 29, 2018
Messages
21,358
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:
Code:
Me.StartDate=Format(DateSerial(Year(Date()),Month(Date()),1),"\#yyyy\-mm\-dd\#")
 

mishash

Member
Local time
Today, 21:24
Joined
Aug 20, 2020
Messages
52
Of course. I was just curious if the fix could have been applied to the calling sub. For example:
Code:
Me.StartDate=Format(DateSerial(Year(Date()),Month(Date()),1),"\#yyyy\-mm\-dd\#")
This is the calling sub:
Code:
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

I’m here to help
Staff member
Local time
Today, 12:24
Joined
Oct 29, 2018
Messages
21,358
This is the calling sub:
Code:
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

Member
Local time
Today, 21:24
Joined
Aug 20, 2020
Messages
52
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:

Code:
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

I’m here to help
Staff member
Local time
Today, 12:24
Joined
Oct 29, 2018
Messages
21,358
Do you mean this:

Code:
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...
 

Users who are viewing this thread

Top Bottom