Wildcard - keyword searching question 1

[mrcanada]

Need some help with this.

I'm working on a library database search form with a bunch of different fields, Title, Author, Publisher etc. Users can search by one or more fields - Access ignores whatever fields are left blank on the search form.

I'm trying to set it up so that users can search the Title field with wildcards. Right now they are required to submit their search criteria enclosed in asterisks, and this works. However, I think it would be 'cleaner' to have a separate field where users could just enter their search criteria and let Access take care of the asterisks.

How to build this in to the script?

Here what I've got so far:

<snip>

where = where & &quot; AND [Title] = '&quot; + Me![titlekeyword] + &quot;'&quot;

</snip>

[Title] is the name of the field in the queried table; [titlekeyword] is the name of the field in the searchform.

If someone could let me know how to stick the asterisks into this script I would really appreciate it. Thanks in advance.

 

[Databaseguy]

Either use a varible (Call it Qu) and assign it this value &quot;&quot;&quot;&quot;,

Or

Use chr(34) in lieu of the quotation marks.

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[jebry]

Hi!

Put the asterisks in like this:

where = where & &quot; AND [Title] = '*&quot; + Me![titlekeyword] + &quot;*'&quot;

That should do it for you.

hth
Jeff Bridgham

 

[mrcanada]

Jeff,

This did the job! I just had to change <=> to <like> and Presto! Thanks for helping, it is very appreciated.

 

[Aivars]

Hi, Tyrone!
You can try to use function what I wrote.
Then you will can do searching for more words of phrase.

Example:
Me.SearchCriteria_1 -> text box for key words phrase entering;
Me.SearchCriteria_2 -> text box for key words phrase entering;
Me.SearchCriteria_N -> text box for key words phrase entering;
etc. ->>> all other necessary textboxes for all ckecking fields
Put in to Tag of these textboxes field name in what it's needed to do searching (e.g. Title, Author, Publisher etc. For easier programming start names of these textboxes alike - SearchCriteria
cmdSelect -> commandbutton for selection running

Sub cmdSelect_Click()
On Error GoTo Err_cmdSelect_Click
Dim strSelectCriteria As String
dim strSelCriteriaTemp as string
Dim strSQL As String
Dim strWhere As String
Dim rst As Recordset
Dim blnIsFound As Boolean
dim ctl as control
dim strTxt as string

strTxt=&quot;SearchCriteria&quot; 'Start of textboxes names
'Get all criterias textboxes
for each ctl in me.controls
If ctl.Properties(&quot;ControlType&quot = acTextBox Then
if instr(1,ctl.name,strTxt,vbTextCompare)<>0 then
if not isnull(ctl) and trim(ctl)<>&quot;&quot; then
'Transform entered key words phrase
strSelCriteriaTemp = CriteriaOfKeyWords(ctl.value, ctl.tag, False)
if strSelCriteriaTemp<>&quot;&quot; then
if strSelectCriteria <>&quot;&quot; then
strSelectCriteria = strSelectCriteria & &quot; And &quot;
end if
strSelectCriteria = strSelectCriteria & strSelCriteriaTemp
end if
end if
end if
end if
next

If strSelectCriteria = &quot;&quot; Then 'No criterias
GoTo Exit_cmdSelect_Click
End If
'Compose SQL text
strSQL = &quot;Select * From MyTable &quot;
strWhere = &quot;Where &quot; & strSelectCriteria
strSQL = strSQL & strWhere & &quot;;&quot;

'Check for found records
Set rst = CurrentDb.OpenRecordset(strSQL)
blnIsFound = Not rst.EOF
rst.Close
Set rst = Nothing

Exit_cmdSelect_Click:
If Not blnIsFound Then 'Not found any record
MsgBox &quot;There are not records with your search criterias.&quot;
Me.SubForm.Form.FilterOn = False
Else
'Set filter of subform to found key words
Me.SubForm.Form.Filter = strSelectCriteria
Me.SubForm.Form.FilterOn = True
End If
Exit Sub

Err_cmdSelect_Click:
MsgBox &quot;Error No &quot; & Err.Number & vbLf & Err.Description
Resume Exit_cmdSelect_Click

End Sub

'-------------------------
'Function

Public Function CriteriaOfKeyWords(strPhrase As String, _
strFieldName As String, _
Optional ExactPhrase As Boolean = False) As String
'This function transforms Key Words Phrase
'to phrase with asterisks and field name
'as well as criteria's parameter &quot;Like&quot;
'Optional ExactPhrase -> True if it's needed to find whole phrase
'If is omited ExactPhrase then all words is found
'strFieldName -> Field name in what it's needed words to find

Dim strCriteria As String
Dim n As Integer

'Remove spaces from start and from end of key words phrase
strPhrase = Trim(strPhrase)
If ExactPhrase = True Then 'Whole phrase
CriteriaOfKeyWords = strFieldName & &quot; Like '*&quot; & strPhrase & &quot;*'&quot;
Else
'For more correct searching
'it's possibly to exlude insignificant words
'like articles - a, the, conj. - and,
'particles - in, on, at, by
'In Acc2000 it's possibly to do by using function
'Replace()

n = InStr(1, strPhrase, &quot; &quot;, vbTextCompare)
If n = 0 Then
strCriteria = strCriteria & strPhrase & &quot;*'&quot;
GoTo FinishOfCompose
End If
Do
'Look for spaces in phrase
'and change its to asterisk
'add field name and param &quot;Like&quot;
n = InStr(1, strPhrase, &quot; &quot;, vbTextCompare)
If n > 0 Then
If strCriteria <> &quot;&quot; Then
strCriteria = strCriteria & &quot; Or &quot; & strFieldName & &quot; Like '*&quot;
End If
strCriteria = strCriteria & Left(strPhrase, n - 1) & &quot;*'&quot;
strPhrase = Trim(Mid(strPhrase, n))
Else
If strPhrase <> &quot;&quot; Then
If strCriteria <> &quot;&quot; Then
strCriteria = strCriteria & &quot; Or &quot; & strFieldName & &quot; Like '*&quot;
End If
strCriteria = strCriteria & strPhrase & &quot;*'&quot;
End If
Exit Do
End If
Loop
FinishOfCompose:
If strCriteria <> &quot;&quot; Then
strCriteria = strFieldName & &quot; Like '*&quot; & strCriteria ' & &quot;*'&quot;
End If
CriteriaOfKeyWords = &quot;(&quot; & strCriteria & &quot&quot;
End If

End Function
'-------------------------
?CriteriaOfKeyWords(&quot;Find key words&quot;,&quot;Title&quot
..... (Title Like '*Find*' Or Title Like '*key*' Or Title Like '*words*')

?CriteriaOfKeyWords(&quot;Find key words&quot;,&quot;Title&quot;,True)
..... (Title Like '*Find key words*')

Aivars