Solved: Search Form

[David627]

I have a search form that I obtained which allows the user to pick which criteria they wish to search in a combo box and then a text box where the string is entered.

Everything works fine excpet date. I get a Run time error 3075: "Syntax error (missing operator) in query expression 'Entry Date LIKE '*1/5/09*"

(1/5/09 is the date I choose)


Code follows:


Private Sub cmdSearch_Click()
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

Else

'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

'Filter frmCHECKS based on search criteria
Form_CHECKS.RecordSource = "select * from CHECKS where " & GCriteria
Form_CHECKS.Caption = "CHECKS (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

'Close frmSearch
DoCmd.Close acForm, "frmSearch"

MsgBox "Results have been filtered."

End If

End Sub

------------------

Thoughts?

Thank you
David

[CreganTur]

Is the Entry Date field in you rtable set to the Date/Time format? If so, your code is failing because the date you are searching on is not surrounded by pound signs (#).

HTH:thumb

[David627]

so when someone enters a date it should have # around it? Shouldn't it add that intuitively like it does in queries?

David

[OBP]

It does if you use the search "Fields" from the Form in the Query Criteria Rows.

[David627]

so I will need to instruct my user to put in the date #MM/DD/YY# in the text box?

[CreganTur]

You could use the IsDate() function to see if the user entered a date, and then just alter how your SQL string is compiled using either Select Case of If conditionals.

[David627]

now for those who know just enough VBA to be dangerous :biggrin: could you post how that would work based on by code above?

Thanks
David

[DarkSprout]

In The cmdSearch_AfterUpdate() event for the date input, wrap the input
Like this
[cmdSearch] = "#" & Nz([cmdSearch], "") & "#"

and then validate the input using the IsDate function.

If IsDate([cmdSearch]) Then ...