Run-time error '3075': Syntax error (missing operator) in query expression '()'.

[irenenys]

Below is my code. Highlighted error is DoCmd.ApplyFilter task (Line #19)

Not too sure what is wrong, really new to access programming.

Option Compare Database
 
Private Sub SearchB_Click()
' Search Button
Call Search
End Sub
 
Sub Search()
Dim strCriteria, task As String
 
Me.Refresh
 
If IsNull(Me.DOF) Or IsNull(Me.DOT) Then
    MsgBox "Please enter the date range", vbInformation, "Date Range Required"
    Me.DOF.SetFocus
Else
    srtCriteria = "([Date Occurred] >= #" & Me.DOF & "# And [Date Occurred] <= #" & Me.DOT & "#)"
    task = "select * from ARMG where (" & strCriteria & ") order by [Date Occurred]"
    DoCmd.ApplyFilter task
 
End If
 
End Sub

[PhilOfWalton]

You have tried to use an SQL type statement as a filter.

This should work if you are using American dates (mm/dd/yyyy), but will need modification for English Dates (dd/mm/yyyy)

I strongly urge you not to have spaces in Field Names in tables, Conrol Names in Forms & Reports and all objects in the database window. If there are spaces, the words have to be surrounded with square brackets [Date Occurred] instead of DateOccurred

I have 2 Command buttons on my form, one for US dates and one for UK Dates

Option Compare Database
Option Explicit
 
Private Sub CmdUSSearch_Click()
 
    Dim strFltr
 
    If IsNull(Me.DOF) Or IsNull(Me.DOT) Then
        MsgBox "Please enter the date range", vbInformation, "Date Range Required"
        Me.DOF.SetFocus
    Else
        strFltr = "(DateOccurred >= #" & Me.DOF & "# And DateOccurred <= #" & Me.DOT & "#)"
        Me.Filter = strFltr
        Me.FilterOn = True
    End If
 
End Sub
 
Private Sub CmdUKSearch_Click()
 
    Dim strFltr
 
    If IsNull(Me.DOF) Or IsNull(Me.DOT) Then
        MsgBox "Please enter the date range", vbInformation, "Date Range Required"
        Me.DOF.SetFocus
    Else
        strFltr = "(DateOccurred >= #" & Format(Me.DOF, "mm/dd/yyyy") & "# And DateOccurred <= #" & Format(Me.DOT, "mm/dd/yyyy") & "#)"
        Me.Filter = strFltr
        Me.FilterOn = True
    End If
 
End Sub
 

Choose which one applies to you.

Phil

[jforbes]

Once you have srtCriteria filled out with the where clause, you should be able to do the following:

Me.Filter=srtCriteria 

[NeoPa]

JForbes is perfectly correct. However, there are a couple of factors that still need looking at.

1. Please see Require Variable Declaration. This is an early point for those new to VBA to learn. It will save you a deal of strife.
2. Dates shouldn't really be formatted depending on which country you're in. It's possible to miss this point if you live in USA, but that doesn't mean it's correct or advisable to. See Literal DateTimes and Their Delimiters (#) for the full story including a few gotchas you won't see unless you go to Europe or Canada, but essentially date literals should always be used as formatted strings.
3. SQL has a construct specifically for ranges (Most applicable for dates.) where each end is inclusive, just as you require. Your code could be :
   

   Private Sub cmdSearch_Click()
       Dim strFilter As String
    
       With Me
           If IsNull(.DOF) _
           Or IsNull(.DOT) Then
               Call MsgBox(Prompt:="Please enter the date range" _
                         , Buttons:=vbInformation Or vbOkOnly _
                         , Title:="Date Range Required")
               If IsNull(.DOF) Then
                   Call .DOF.SetFocus
               Else
                   Call .DOT.SetFocus
               End If
           Else
               strFilter = Replace("(DateOccurred Between #%F# And #%T#)" _
                                 , "%F", Format(.DOF, "yyyy\/m\/d"))
               strFilter = Replace(strFilter, "%T", Format(.DOT, "yyyy\/m\/d"))
               Me.Filter = strFilter
               Me.FilterOn = True
           End If
       End With
   End Sub