By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,454 Members | 3,133 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

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

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

Not too sure what is wrong, really new to access programming.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub SearchB_Click()
  4. ' Search Button
  5. Call Search
  6. End Sub
  7.  
  8. Sub Search()
  9. Dim strCriteria, task As String
  10.  
  11. Me.Refresh
  12.  
  13. If IsNull(Me.DOF) Or IsNull(Me.DOT) Then
  14.     MsgBox "Please enter the date range", vbInformation, "Date Range Required"
  15.     Me.DOF.SetFocus
  16. Else
  17.     srtCriteria = "([Date Occurred] >= #" & Me.DOF & "# And [Date Occurred] <= #" & Me.DOT & "#)"
  18.     task = "select * from ARMG where (" & strCriteria & ") order by [Date Occurred]"
  19.     DoCmd.ApplyFilter task
  20.  
  21. End If
  22.  
  23. End Sub
Aug 10 '17 #1

✓ answered by 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 :
    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdSearch_Click()
    2.     Dim strFilter As String
    3.  
    4.     With Me
    5.         If IsNull(.DOF) _
    6.         Or IsNull(.DOT) Then
    7.             Call MsgBox(Prompt:="Please enter the date range" _
    8.                       , Buttons:=vbInformation Or vbOkOnly _
    9.                       , Title:="Date Range Required")
    10.             If IsNull(.DOF) Then
    11.                 Call .DOF.SetFocus
    12.             Else
    13.                 Call .DOT.SetFocus
    14.             End If
    15.         Else
    16.             strFilter = Replace("(DateOccurred Between #%F# And #%T#)" _
    17.                               , "%F", Format(.DOF, "yyyy\/m\/d"))
    18.             strFilter = Replace(strFilter, "%T", Format(.DOT, "yyyy\/m\/d"))
    19.             Me.Filter = strFilter
    20.             Me.FilterOn = True
    21.         End If
    22.     End With
    23. End Sub

Share this Question
Share on Google+
3 Replies


PhilOfWalton
Expert 100+
P: 1,430
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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub CmdUSSearch_Click()
  5.  
  6.     Dim strFltr
  7.  
  8.     If IsNull(Me.DOF) Or IsNull(Me.DOT) Then
  9.         MsgBox "Please enter the date range", vbInformation, "Date Range Required"
  10.         Me.DOF.SetFocus
  11.     Else
  12.         strFltr = "(DateOccurred >= #" & Me.DOF & "# And DateOccurred <= #" & Me.DOT & "#)"
  13.         Me.Filter = strFltr
  14.         Me.FilterOn = True
  15.     End If
  16.  
  17. End Sub
  18.  
  19. Private Sub CmdUKSearch_Click()
  20.  
  21.     Dim strFltr
  22.  
  23.     If IsNull(Me.DOF) Or IsNull(Me.DOT) Then
  24.         MsgBox "Please enter the date range", vbInformation, "Date Range Required"
  25.         Me.DOF.SetFocus
  26.     Else
  27.         strFltr = "(DateOccurred >= #" & Format(Me.DOF, "mm/dd/yyyy") & "# And DateOccurred <= #" & Format(Me.DOT, "mm/dd/yyyy") & "#)"
  28.         Me.Filter = strFltr
  29.         Me.FilterOn = True
  30.     End If
  31.  
  32. End Sub
  33.  
Choose which one applies to you.

Phil
Aug 10 '17 #2

jforbes
Expert 100+
P: 1,107
Once you have srtCriteria filled out with the where clause, you should be able to do the following:
Expand|Select|Wrap|Line Numbers
  1. Me.Filter=srtCriteria 
Aug 12 '17 #3

NeoPa
Expert Mod 15k+
P: 31,494
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 :
    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdSearch_Click()
    2.     Dim strFilter As String
    3.  
    4.     With Me
    5.         If IsNull(.DOF) _
    6.         Or IsNull(.DOT) Then
    7.             Call MsgBox(Prompt:="Please enter the date range" _
    8.                       , Buttons:=vbInformation Or vbOkOnly _
    9.                       , Title:="Date Range Required")
    10.             If IsNull(.DOF) Then
    11.                 Call .DOF.SetFocus
    12.             Else
    13.                 Call .DOT.SetFocus
    14.             End If
    15.         Else
    16.             strFilter = Replace("(DateOccurred Between #%F# And #%T#)" _
    17.                               , "%F", Format(.DOF, "yyyy\/m\/d"))
    18.             strFilter = Replace(strFilter, "%T", Format(.DOT, "yyyy\/m\/d"))
    19.             Me.Filter = strFilter
    20.             Me.FilterOn = True
    21.         End If
    22.     End With
    23. End Sub
Aug 12 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.