Run Time Error 13 :-(

Question

I've created a Search Form. My Search Form contain a subform and I've been trying to get the Search button to work. My Search button will help me to run the Search form based by Date and Product.

I've keep getting the error on "Run Time error 13"

The error is at the line which have ***

Would really appreciate if someone assist me on this Search Form. Thanks! :-)

These are my codes

Private Sub Search_Click()

    Dim strProduct As String
    Dim strDatePicker As Date
    Dim sql As String

    sql = "select * from 2_WIP where "
    strDatePicker = format(Me.DatePicker, "dd/mm/yyyy")

    If IsDate(Me.DatePicker) Then
       ***strDatePicker = " [Date] Like '*'"***
    Else
       strDatePicker = " [Date] = '" & Me.DatePicker & "'"
    End If

    If IsNull(Me.Product) Then
       strProduct = " [Product] like '*'"
    Else
       strProduct = " [Product] like '" & Me.Product & "'"
    End If

    sql = strDatePicker & "and" & strProduct

    Me.subfrmWIP.Form.RecordSource = sql
    Me.subfrmWIP.Form.Requery

End Sub

Answer 1

You're getting the error because you are trying to assign a non-date string value to a date variable.

Dim strDatePicker As Date
strDatePicker = " [Date] Like '*'"

This assignment is backwards.

If IsDate(Me.DatePicker) Then
   ***strDatePicker = " [Date] Like '*'"***
Else
   strDatePicker = " [Date] = '" & Me.DatePicker & "'"
End If

---

Private Sub Search_Click()

    Dim ProductSearch As String, DateSearch As String
    Dim sql As String

    sql = "SELECT * FROM 2_WIP WHERE "

    If IsDate(Me.DatePicker) Then
        DateSearch = "(([2_WIP].[Date])>=#" & DateValue(Me.DatePicker) & _
        "# And ([2_WIP].[Date])<#" & DateValue(Me.DatePicker) + 1 & "#) "
    Else
        DateSearch = " [Date] Like '*'"
    End If

    If IsNull(Me.Product) Then
        ProductSearch = " [Product] like '*'"
    Else
        ProductSearch = " [Product] like '" & Me.Product & "'"
    End If

    sql = sql & DateSearch & " AND " & ProductSearch

    Me.subfrmWIP.Form.RecordSource = sql
    Me.subfrmWIP.Form.Requery

End Sub

Sample Output

SELECT * FROM 2_WIP WHERE [Date] = #11/23/2016# AND [Product] like '16oz Coke'

How use Query Builder to help build SQL in the VBA