Runtime Error 3075 on search button

[kmarkenday]

I am trying to create a search form in Access where in people can search records by date range. But each time I click search I get a run time error 3075... with DoCmd.ApplyFilter task highlighted. I am new to VBA.

Private Sub Command62_Click()
    ' Search Button
    Call Search
End Sub
Sub Search()
 
    Dim strCriteria, task As String
 
    Me.Refresh
    If IsNull(Me.txtDateIntakeAssignedFrom) Or IsNull(Me.txtDateIntakeAssignedTo) Then
        MsgBox "Please enter the date range", vbInformation, "Date Range Required"
        Me.txtDateIntakeAssignedFrom.SetFocus
    Else
        strCriteria = "([Date_Staff_Assigned]) >= #" & Me.txtDateIntakeAssignedFrom & "# And [Date_Staff_Assigned] <= #" & Me.txtDateIntakeAssignedTo & "#)"
        task = "select * from frmCustomizeSearchDataonDatasheet where (" & strCriteria & ") order by [Date_Staff_Assigned]"
        DoCmd.ApplyFilter task
    End If
 
End Sub

[twinnyfo]

kmarkenday,

Welcome to Bytes!

As a newcomer, I remind you to enclose your code in code tags (use the [CODE/] button in your text editor), as this is a requirement of this forum. I have edited your post to reflect such changes.

There are a few things I find unorthodox about your code. You said you are new to VBA, so I will try to be as instructional as possible.

First, You may want to change the name of your Search Sub, as the word "Search" is one of those words in Access that can cause the DB engine to occasionally become confused. This can occur with field names like "Name" or Tables named "Table", etc.

Second, unless you are calling the Search Sub from other areas in your code, you can roll all your code under the Command Button's OnClick event. There is nothing wrong with how you have done it, especially if you call this search from multiple locations.

Third, a Filter is defined as "the WHERE clause of a SQL string, without the word WHERE." So, by assigning an actuall SQL string to the Filter, you are causing the error in the syntax of te Filter. Your line #14 should be the appropriate filter that you use.

Fourth, I am going to assume that you want this filter to filter the records that are currently being displayed onthe Form? I must assume this as it is not directly stated. Although you can use the ApplyFilter method, the more trasditional method to use is to simply directly apply the filter to the form itself. In fact, when you use the ApplyFilter method, you are setting the filter, but indirectly.

I would also highly suggest that you get into the habit of including Error Handling Code into all your procedures. Again, I have done a simple error handling code for you.

So, using the majority of your code, we would get this:

Private Sub Command62_Click()
On Error GoTo EH
    Dim strCriteria As String
 
    Me.Refresh
    If IsNull(Me.txtDateIntakeAssignedFrom) Or _
        IsNull(Me.txtDateIntakeAssignedTo) Then
        MsgBox "Please enter the date range", _
            vbInformation, "Date Range Required"
        Me.txtDateIntakeAssignedFrom.SetFocus
    Else
        strCriteria = "([Date_Staff_Assigned]) >= #" & _
            Me.txtDateIntakeAssignedFrom & _
            "# And [Date_Staff_Assigned] <= #" & _
            Me.txtDateIntakeAssignedTo & "#)"
        Me.Filter = strCriteria
        Me.FilterOn = True
    End If
    Exit Sub
EH:
    MsgBox "There was an error searching the form!  " & _
        vbCrLf & vbCrLf & Err.Number & _
        vbCrLf & vbCrLf & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", _
        vbCritical, "WARNING!"
    Exit Sub
End Sub

Hope this hepps!