Link to home
Start Free TrialLog in
Avatar of Natasha
NatashaFlag for Singapore

asked on

Search Form with Run Time Error 3075

I have a Search Form with a Sub Form in it. The Sub Form consist of records from two tables namely 2_WIP and qryWIP. I am trying to make my Search Form work to query based on Date and Product from these two tables.

I keep getting this Run Time 3075 error stating: "Syntax error in query expression '[[2_WIP].Date_Recorded] = #22/11/2016# AND [[2_WIP].Product like '*'".

These are my codes

Private Sub Search_Click()

    Dim strProduct As String
    Dim strDatePicker As String
    Dim sql As String
    
    sql = "SELECT [2_WIP].RecordID, [2_WIP].Date_Recorded, [2_WIP].Product, [2_WIP].Total_Good_Count, [2_WIP].[1st_Operator], [2_WIP].[2nd_Operator], [2_WIP].Run_Time, [2_WIP].WIP_Cleared, [2_WIP].Remarks, qryWIP.Total_WIP, * FROM 2_WIP INNER JOIN qryWIP ON [2_WIP].RecordID = qryWIP.RecordID WHERE"
    
    If IsDate(Me.DatePicker) Then
        strDatePicker = " [[2_WIP].Date_Recorded] = #" & DateValue(Me.DatePicker) & "#"
    Else
        strDatePicker = " [[2_WIP].Date_Recorded] like '*'"
    End If
        
    If IsNull(Me.Product) Then
        strProduct = " [[2_WIP].Product] like '*'"
    Else
        strProduct = " [[2_WIP].Product] = '" & Me.Product & "'"
    End If
    
    sql = sql & strDatePicker & " AND " & strProduct
    
    Me.subfrmWIP.Form.RecordSource = sql
    Me.subfrmWIP.Form.Requery
    
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Natasha
Natasha
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial