Using a form to search keywords in a memo field

[Frumonster]

Currently, I am trying to search a memo field on a table by having the user enter keywords on a form (up to 10 keywords can be entered). It works when just one keyword is entered, however it doesn't work if more than one is entered. How do I account for more than one keyword being entered to search the memo field and return the recors where any of the terms show up in that field. Each one of the keywords [KW1] ...etc are in a separate unbound box.

Current formula looks as follows:
Like "*" & [Forms]![KeywordInputForm]![KW1] & "*" OR "*" & [Forms]![KeywordInputForm]![KW2] & "*" OR "*" & [Forms]![KeywordInputForm]![KW3] & "*" OR "*" & [Forms]![KeywordInputForm]![KW4] & "*" OR "*" & [Forms]![KeywordInputForm]![KW5] & "*" OR "*" & [Forms]![KeywordInputForm]![KW6] & "*" OR "*" & [Forms]![KeywordInputForm]![KW7] & "*" OR "*" & [Forms]![KeywordInputForm]![KW8] & "*" OR "*" & "*" & [Forms]![KeywordInputForm]![KW9] & "*" OR "*" & [Forms]![KeywordInputForm]![KW10] & "*"

Should I be using AND?

Thank you!

 

[CJ_London]

I'm surprised it works with just one keyword because all the others you are looking for like "**" which means everything.

I would be inclined to use the instr function as follows:

instr(memofld,nz([Forms]![KeywordInputForm]![KW1]))+
instr(memofld,nz([Forms]![KeywordInputForm]![KW2]))+
instr(memofld,nz([Forms]![KeywordInputForm]![KW3]))+.....
instr(memofld,nz([Forms]![KeywordInputForm]![KW10]))>0

note I have used a CR so the code is more readable

 

[Frumonster]

can you take a couple of minutes and explain instr, memofield, and "nz" what they do ... just for future reference. I haven't tried the code yet, but I notice there is double parenthesis )) at the end where does that begin?

 

[jdraw]

For instant help with Access functions go to
http://www.techonthenet.com/access/functions/index_alpha.php

Excellent reference with syntax and samples.

 

[Mihail]

My approach is to use one more field in the query.

ShowRecord:ShowThisRecord([MemoFieldName])

with Criteria = True

Then, in a regular module the function:

Public Function ShowThisRecord(strText As String) As String
  ShowThisRecord = True

  If InStr(1, strText, Forms!FormName.TextBoxName) > 0 Then
Exit Function
  End If

'Repeat the IF THEN instruction for each TextBox that store a keyword
.........
  ShowThisRecord = False 'No keyword was founded

End Function

 

[Frumonster]

What do you mean by a "regular module"?

 

[pr2-eugin]

What do you mean by a "regular module"?

Normally Forms, Reports can have VBA Code behind them, but a regular/Standard module is stand alone and can have all codes that needed to be available to Any object in the Database.

To create a Standard module..

 

[Mihail]

A module that you create from the VBA window editor.
A module that is not attached to a form or a report.

While you are in VBA window, go to Insert menu then select Module.
This will create so named Regular module.

 

[Mihail]

Hi, Paul !
How you post the pics here ?
I can only to upload it

 

[Frumonster]

Thanks for all the great responses. I put in the following code in a module after creating the new field in the query and received an error message - Compile Error: Block If without End If:

Public Function ShowThisRecord(strText As String) As String
ShowThisRecord = True

If InStr(1, strText, Forms!KeywordInputForm.KW1) > 0 Then
If InStr(1, strText, Forms!KeywordInputForm.KW2) > 0 Then
If InStr(1, strText, Forms!KeywordInputForm.KW3) > 0 Then
If InStr(1, strText, Forms!KeywordInputForm.KW4) > 0 Then
If InStr(1, strText, Forms!KeywordInputForm.KW5) > 0 Then
If InStr(1, strText, Forms!KeywordInputForm.KW6) > 0 Then
If InStr(1, strText, Forms!KeywordInputForm.KW7) > 0 Then
If InStr(1, strText, Forms!KeywordInputForm.KW8) > 0 Then
If InStr(1, strText, Forms!KeywordInputForm.KW9) > 0 Then
If InStr(1, strText, Forms!KeywordInputForm.KW10) > 0 Then
Exit Function
End If
ShowThisRecord = False

End Function

 

[pr2-eugin]

Hi, Paul !
How you post the pics here ?
I can only to upload it

It's quiet simple, follow the same procedure how you upload attachments, then from the attachment "Copy the Link Location" and use IMG tags..

 

[pr2-eugin]

Frumonster - You need to have your code as..

Public Function ShowThisRecord(strText As String) As String
    ShowThisRecord = True
    If InStr(1, strText, Forms!KeywordInputForm.KW1) > 0[COLOR=Red][B] Or _[/B][/COLOR]
       InStr(1, strText, Forms!KeywordInputForm.KW2) > 0 [COLOR=Red][B]Or _[/B][/COLOR]
       InStr(1, strText, Forms!KeywordInputForm.KW3) > 0 [COLOR=Red][B]Or _[/B][/COLOR]
       InStr(1, strText, Forms!KeywordInputForm.KW4) > 0 [COLOR=Red][B]Or _[/B][/COLOR]
       InStr(1, strText, Forms!KeywordInputForm.KW5) > 0 [COLOR=Red][B]Or _[/B][/COLOR]
       InStr(1, strText, Forms!KeywordInputForm.KW6) > 0 [COLOR=Red][B]Or _[/B][/COLOR]
       InStr(1, strText, Forms!KeywordInputForm.KW7) > 0 [COLOR=Red][B]Or _[/B][/COLOR]
       InStr(1, strText, Forms!KeywordInputForm.KW8) > 0 [COLOR=Red][B]Or _[/B][/COLOR]
       InStr(1, strText, Forms!KeywordInputForm.KW9) > 0 [COLOR=Red][B]Or _[/B][/COLOR]
       InStr(1, strText, Forms!KeywordInputForm.KW10) > 0 Then
        Exit Function
    End If    

    ShowThisRecord = False
End Function

 

[Mihail]

Take a closer look to the IF statement in my post
The integral structure is:

IF condition THEN
  'Code if condition = True
ELSE
  'Code if condition = False
END IF

If is no need to use the ELSE part then the structure can be simplified:

IF condition THEN
  'Code if condition = True
END IF

So, your code should be:

Public Function ShowThisRecord(strText As String) As String
       ShowThisRecord = True
    
  If InStr(1, strText, Forms!KeywordInputForm.KW1) > 0 Then
Exit Function
  End IF

  If InStr(1, strText, Forms!KeywordInputForm.KW2) > 0 Then
Exit Function
  End IF

  If InStr(1, strText, Forms!KeywordInputForm.KW3) > 0 Then
Exit Function
  End IF

  If InStr(1, strText, Forms!KeywordInputForm.KW4) > 0 Then
Exit Function
  End IF

  If InStr(1, strText, Forms!KeywordInputForm.KW5) > 0 Then
Exit Function
  End IF

  If InStr(1, strText, Forms!KeywordInputForm.KW6) > 0 Then
Exit Function
  End IF

  If InStr(1, strText, Forms!KeywordInputForm.KW7) > 0 Then
Exit Function
  End IF

  If InStr(1, strText, Forms!KeywordInputForm.KW8) > 0 Then
Exit Function
  End IF

  If InStr(1, strText, Forms!KeywordInputForm.KW9) > 0 Then
Exit Function
  End IF

  If InStr(1, strText, Forms!KeywordInputForm.KW10) > 0 Then
Exit Function
  End IF

      ShowThisRecord = False
    
End Function

 

[Frumonster]

Mihail:
That code keeps coming up with the same compile error message.

 

[Frumonster]

pr2-eugin:
That code doesn't account for if the user leaves a blank keyword ... if they do it returns all records.

 

[Frumonster]

I am looking for a way to pass keywords entered by form through a query that only returns the memo field where a word that is listed on the form (up to ten words). I would like to have the report have the keyword(s) searched highlighted, but it isn't necessary, especially because all the code I seems to run from the keyword form displays all the records if one is left blank

 

[Mihail]

Have you use the OPTION EXPLICIT option ?
I'm sure that my code can't raise this error.
The error is somewhere in your code.

Anyway, in order to become very sure, show me again your function.

 

[Frumonster]

Mihail:
My mistake. The code you gave me runs but doesn't account for when a user leaves a keyword space blank.

 

[CJ_London]

look at my original post - #2

 

[Frumonster]

CJ_London:
Do I add this as a module? in the query? I'm confused as to where to enter your code ...