Search button

[Sean91]

Hi I'm working on an asset register for my company and I'm having a bit of trouble getting my search button to work, which filters the results and then i can generate a report with those results.

Here is the code I'm using


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 & txtSearchString & "*'"

'Filter View IT Assets based on search criteria
Form_View_IT_Asset.RecordSource = "select * from IT_Assets_table where " & GCriteria
Form_View_IT_Asset.Caption = "IT_Assets_table (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

'Close frmSearch
DoCmd.Close acForm, "frmSearch"

MsgBox "Results have been filtered."

End If

End Sub

I have a unbound combo box with the different fields to be search and a unbound text box to enter the search criteria, but when I click search after entering data I get a Syntax error (missing operator) in query expression.

Any help would be appreciated Thanks

 

[scalextric59]

You need to specify a field name in your WHERE clause

Form_View_IT_Asset.RecordSource = "select * from IT_Assets_table where <YOURFIELD> = '" & GCriteria & "'"

 

[Sean91]

So I need to specify the field I wish to search on my form? If so how can I do that for multiple fields?

 

[boblarson]

So I need to specify the field I wish to search on my form? If so how can I do that for multiple fields?

Are you trying to have a combo to select a certain field to search by?

 

[Sean91]

Here is a sample of the database

 

[Sean91]

Are you trying to have a combo to select a certain field to search by?

Yeah I'm using a combo box to select the field I wish to search

 

[Sean91]

Forgot to add using XP and access 03. The form that uses the search button is frmSearch which is accessed from clicking search on the View IT Assets form.

 

[boblarson]

Forgot to add using XP and access 03. The form that uses the search button is frmSearch which is accessed from clicking search on the View IT Assets form.

I don't have time to actually do it but you need to do a couple of things.

1. I would add brackets around your reference to the combo

"[" & Me.YourCombo & "]"

2. If using the asterisk (*) you need the LIKE keyword
"[" & Me.YourCombo & "] Like "

3. You need to branch the code to handle numbers, text and dates different. Numbers need no quotes or octothorpes (#) but text needs to be surrounded by quotes (Chr(34) will do) and dates need to have the # on each end.

With that, see if you can work on it.

 

[Sean91]

Thanks for the help

I am still having a few problems with these lines

'Filter View IT Assets based on search criteria
Form_View_IT_Asset.RecordSource = "select * from IT_Assets_table where [" & cboSearchField.Value & "] & GCriteria & " * ""
Form_View_IT_Asset.Caption = "IT_Assets_table ([" & cboSearchField.Value & "] LIKE *" & txtSearchString & "LIKE *')"


Also, I'm unsure what you mean with point 3.

Sorry to be a bother, first time building a database from scratch

 

[Sean91]

I have done a bit of searching but I can't seem to find why I'm getting this error 'runtime error 2465'

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
[COLOR=Red]        GCriteria = ["cboSearchField.Value"] & " LIKE '*" & txtSearchString & "*'"[/COLOR]
        
        'Filter frmView based on search criteria
        Form_frmView.RecordSource = "select * from 'IT_Assets_table' where " & ["cboSearchField.Value"] & " & GCriteria & " * ""
        Form_frmView.Caption = "'IT_Assets_table' (" & ["cboSearchField.Value"] & " LIKE *" & txtSearchString & "LIKE *')"
        
        'Close frmSearch
        DoCmd.Close acForm, "frmSearch"
        
        MsgBox "Results have been filtered."
        
    End If
    
End Sub

The line marked in red is the problem, whenever I search it tells me "Can't find the field 'I' referred to in your expression". Any ideas?

Thanks

 

[marianne]

have you DIM your criteria?

like: DIM GCriteria as string

 

[Sean91]

I have added

Dim GCriteria As String
  Dim txtSearchString As String

Which has stopped all syntax errors but now it isn't reading the data entered in the text box "txtSearchString"

 

[marianne]

i didn't see any value that you set for your txtseachstring? is txtsearchstring a field in your form or what? maybe you should get back to your coding and see whats lacking?

you may want to study this:

 

[MarkK]

["cboSearchField.Value"] is incorrect syntax.
You'll want to go with cboSearchField as you've used elsewhere

 

[MarkK]

Also, a nice trick to figure out errors in complicated string concatenation expressions is to pause execution, copy the expression, paste it into the immediate window prefixed with a ? and evaluate it there.

 

[Sean91]

Thanks for the help, got one of the developers here at work to help me out
Final code finished like this:

Private Sub cmdSearch_Click()
  
       Dim GCriteria As String
       Dim txtSearchString As String
  
      If Len(Form_frmSearch.cboSearchField) = 0 Or IsNull(Form_frmSearch.cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(Form_frmSearch.txtSearchString) = 0 Or IsNull(Form_frmSearch.txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else

          
        'Generate search criteria
        GCriteria = Form_frmSearch.cboSearchField & " LIKE '*" & Form_frmSearch.txtSearchString & "*'"
        
        'Filter frmView based on search criteria
        Form_frmView.RecordSource = "SELECT * FROM [IT _Assets_table] where [" & Form_frmSearch.cboSearchField & "] = '" & Form_frmSearch.txtSearchString & "';"
        Form_frmView.Caption = "'IT_Assets_table' (" & Form_frmSearch.cboSearchField & " LIKE *" & Form_frmSearch.txtSearchString & "LIKE *')"
        
        'Close frmSearch
        DoCmd.Close acForm, "frmSearch"
        
        MsgBox "Results have been filtered."
        
    End If
    
End Sub

 

[marianne]

good for you. cheers