Keyword Search for Report

[brsawvel]

Hello,

I have a form with a memo box where the user can input a few sentences.
I was wondering if anyone had a simple keyword search-to-report form/code where a user could enter a...keyword...and hit the report button to display all records where the desired keyword is in the memo box?

 

[brsawvel]

Hello,

trying to revive this question.

 

[pbaldy]

Generally, you would use Like with wildcards in a query criteria:

WHERE FieldName Like "'*" & YourKeywordInput & "*'"

 

[brsawvel]

I'm sure this is an easier issue, but I received an error. This is what I have in the button code

Private Sub Command400_Click()
On Error GoTo Err_Command400_Click
'Button filters out records depending on the criteria entered in the search fields
Dim strWhere As String
Dim lngLen As Long
Dim stDocName As String
WHERE Report Like "'*" & Report & "*'"
'This is used to cut off the " AND " at the end of the Where Condition
lngLen = Len(strWhere) - 5
'If there was no information included in the criteria fields an error pops up, otherwise the search processes the Where Condition
If lngLen <= 0 Then
MsgBox "Please enter search criteria!"
Else
strWhere = Left$(strWhere, lngLen)
stDocName = "rptIncident"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End If
Exit_Command400_Click:
    Exit Sub
Err_Command400_Click:
    MsgBox Err.Description
    Resume Exit_btnReport_Click
End Sub

The error points to the first "Report" in...

WHERE Report Like "'*" & Report & "*'"

and says "Sub or Function not defined"

 

[pbaldy]

Well, the line I gave you would have been part of a query; it wouldn't stand alone in code. You would have to set a variable to it, and as a wherecondition argument, you wouldn't include the word WHERE, so :

strWhere = "Report Like "'*" & Report & "*'"

The second Report would have to be a variable or form reference containing the text you want to search on. You don't want to do any of the stuff that deletes the " AND " off the end, because you aren't adding that in the first place.

 

[brsawvel]

. Hit me over the head and call me stupid.

Do I create a query form? How do I set a variable to it? I have to create a second report and form?

 

[pbaldy]

Thud!

You don't need another form, or a variable, or a second report. It all depends on how you want the app to work. You can add another button to your existing form, and use either an existing or new textbox. Then you can open the same report but with this new wherecondition, substituting a form reference for the second "Report".

 

[brsawvel]

That's what I thought. I actually created a new form (frmkeyword) with just the memo box (tboreport), but the form is linked to the tblreport . The form has a report button for the same report but it's just getting it to search the tblreport.fldreport based on the tboreport in frmkeyword and send it to rptreport. (I need to work on my naming of objects to avoid confusion).

 

[pbaldy]

Let me know if you get stuck. We'll "git-er-done".

 

[brsawvel]

Then you can open the same report but with this new wherecondition, substituting a form reference for the second "Report".

I'm not sure if this is what you meant...

strWhere = "Report Like " '*" & frmKeyword & "*'"

but when I run the search I still get all records rather than the parameters I enter into the text box.

 

[pbaldy]

It looks like somebody added an extra quote mark to my sample code.

Try this

strWhere = "Report Like '*" & frmKeyword & "*'"

 

[brsawvel]

I received a "SYNTAX ERROR - missing operator in query expression" statement that time

 

[pbaldy]

Is frmKeyword a control on the form? Now that I reread, is that the form itself? It would have to be

Forms!frmKeyword.ControlName

 

[brsawvel]

I still receive the same error. Here's the newest evolution of code under that button:

Private Sub Command400_Click()
On Error GoTo Err_Command400_Click
'Button filters out records depending on the criteria entered in the search fields
Dim strWhere As String
Dim lngLen As Long
Dim stDocName As String

strWhere = "Report Like '*" & Forms!frmKeyword.Report & "*'"

'This is used to cut off the " AND " at the end of the Where Condition
lngLen = Len(strWhere) - 5

'If there was no information included in the criteria fields an error pops up, otherwise the search processes the Where Condition
If lngLen <= 0 Then
MsgBox "Please enter search criteria!"
Else
strWhere = Left$(strWhere, lngLen)
stDocName = "rptIncident"
DoCmd.OpenReport stDocName, acPreview, , strWhere


End If

Form.Undo

Exit_Command400_Click:
    Exit Sub

Err_Command400_Click:
    MsgBox Err.Description
    Resume Exit_Command400_Click
End Sub

 

[pbaldy]

As previously noted:

You don't want to do any of the stuff that deletes the " AND " off the end, because you aren't adding that in the first place.

Also, if this is the same db that has strWhere declared in the standard module, you wouldn't have to declare it here.

 

[brsawvel]

I ended up not needing the standard module for the last issue.

I removed the 'delete " AND " off the end' code, and was left with this:

Private Sub Command400_Click()
On Error GoTo Err_Command400_Click
'Button filters out records depending on the criteria entered in the search fields
Dim strWhere As String
Dim lngLen As Long
Dim stDocName As String

strWhere = "Report Like '*" & Forms!frmKeyword.Report & "*'"



strWhere = Left$(strWhere, lngLen)
stDocName = "rptIncident"
DoCmd.OpenReport stDocName, acPreview, , strWhere



Form.Undo

Exit_Command400_Click:
    Exit Sub

Err_Command400_Click:
    MsgBox Err.Description
    Resume Exit_Command400_Click
End Sub

and when I click the report button after submitting parameters in the textbox, i receive all records.

 

[pbaldy]

You're still trimming the string:

strWhere = Left$(strWhere, lngLen)

 

[brsawvel]

that was it! thanks!

Is there another way to enter a code, though, that pops up a error msgbox if no information is entered?


xxxxxxxxxxxxx

Never mind. I figured it out!

Thanks once again for all the help!