help needed

[nur annie]

i have a form call Search that uses to find records in my database with key "PRODUCT CODE" OR "INSPECTION DATE".. the form return the desired result when i enter only the product code. problem start when i entered the date as the text box is Date from as date to... the error appeared:

Extra)in query expression '[INSPECTION DATE] >= #09/07/2012#) AND ([INSPECTION DATE] <= #15/07/2012#)'.

My entire code for this form is :
Option Compare Database

Private Sub Close_Click()
DoCmd.Close
End Sub

Private Sub cmdClear_Click()
Me.InspectionRecords_subform.Form.RecordSource = "SELECT * FROM InspectionRecords "
Me.InspectionRecords_subform.Requery
txtProductCode = ""
txtDateFrom = ""
txtDateTo = ""
txtProductCode.SetFocus
End Sub

Private Sub cmdSearch_Click()
On Error GoTo errr
Me.InspectionRecords_subform.Form.RecordSource = "SELECT * FROM InspectionRecords " & BuildFilter
Me.InspectionRecords_subform.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"

Const ConJetDate = "\#dd\/mm\/yyyy\#"

varWhere = Null

If Me.txtProductCode > "" Then
varWhere = varWhere & "[PRODUCT CODE] like " & Me.txtProductCode & " AND "
End If

If Me.txtDateFrom > "" Then
varWhere = varWhere & "[INSPECTION DATE] >= " & Format(Me.txtDateFrom, ConJetDate) & ") AND "
End If

If Me.txtDateTo > "" Then
varWhere = varWhere & "([INSPECTION DATE] <= " & Format(Me.txtDateTo, ConJetDate) & ") AND "
End If

If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = " Where " & varWhere

If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function

Private Sub Form_Load()
cmdClear_Click
End Sub



can any body help me with... pls...i really appreciate this

annie:banghead:

 

[bob fitz]

Replace:

If Me.txtDateFrom > "" Then
varWhere = varWhere & "[INSPECTION DATE] >= " & Format(Me.txtDateFrom, ConJetDate) & ") AND "
End If

With:

If Me.txtDateFrom > "" Then
varWhere = varWhere & "[B][COLOR=red]([/COLOR][/B][INSPECTION DATE] >= " & Format(Me.txtDateFrom, ConJetDate) & ") AND "
End If

 

[nur annie]

thanks... such a silly mistake... but it didn't return any result...

any idea?thanks

annie

 

[nur annie]

oh bob...thanks a lot... i get it this time... the result is great....
thanks for the help

annie