Multiple Keyword Search

[Guest]

Hello.
I currently have a "Keyword" button on a form that runs the parameter query
below
Like "*" & [Enter Task Keyword] & "*"

A pop up appears where I can type in someting like Printer

I need to modify this paramater so that I can do a multiple keyword search
in the same paramter query pop up box i.e. Printer Server

Could you help with this?

Thanks.
Iram/mcp

 

[John Spencer]

Can't be done with a parameter query.

Can be done by writing some VBA code that modifies the query or sets the
filter on the form.

Do you want to match ALL keywords entered or do you want to match ANY
keyword entered?

Assuming ALL the keywords need to be matched, you would need a procedure
like the following UNTESTED AIR CODE.

Function fApplyFilter(txtFilter)
'Applys filter based on FSubject field

Dim strSQL As String
Dim vArray As Variant
Dim iLoop As Integer
Dim strCon As String 'conjunction string

vArray = Split(txtFilter, " ", -1, 0)

strCon = " AND "

For iLoop = LBound(vArray) To UBound(vArray)

If Len(Trim(vArray(iLoop))) > 0 Then
strSQL = strSQL & _
" fSubject Like ""*" & Trim(vArray(iLoop)) & "*""" &
strCon
End If
Next iLoop

strSQL = Left(strSQL, Len(strSQL) - Len(strCon)) & ")"

Me.Filter = strSQL
Me.FilterOn = True
End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Where do you enter the field names to be searched? I will need the the
multiple keyword search feature to search multiple fields in an Access table.
Is this possible.

Your help is greatly appreciated.
IRam/mcp

Can't be done with a parameter query.

Can be done by writing some VBA code that modifies the query or sets the
filter on the form.

Do you want to match ALL keywords entered or do you want to match ANY
keyword entered?

Assuming ALL the keywords need to be matched, you would need a procedure
like the following UNTESTED AIR CODE.

Function fApplyFilter(txtFilter)
'Applys filter based on FSubject field

Dim strSQL As String
Dim vArray As Variant
Dim iLoop As Integer
Dim strCon As String 'conjunction string

vArray = Split(txtFilter, " ", -1, 0)

strCon = " AND "

For iLoop = LBound(vArray) To UBound(vArray)

If Len(Trim(vArray(iLoop))) > 0 Then
strSQL = strSQL & _
" fSubject Like ""*" & Trim(vArray(iLoop)) & "*""" &
strCon
End If
Next iLoop

strSQL = Left(strSQL, Len(strSQL) - Len(strCon)) & ")"

Me.Filter = strSQL
Me.FilterOn = True
End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello.
I currently have a "Keyword" button on a form that runs the parameter
query
below
Like "*" & [Enter Task Keyword] & "*"

A pop up appears where I can type in someting like Printer

I need to modify this paramater so that I can do a multiple keyword search
in the same paramter query pop up box i.e. Printer Server

Could you help with this?

Thanks.
Iram/mcp

 

[John Spencer]

Yes, it is possible. There are several ways of doing this depending on how
many fields you want to filter by and how many keywords are involved.

One method uses a supplemental table to contain the primary key of the
matched records.

You run mutliple queries to populate tblMatches with the primary key value
of the target table. AND then join tblMatches to the target table to show
the matches. Since I almost always use a number field (autonumber) as my
primary key field I can rely on joining a number field to a number field in
a query.

Again, let me ask the question - Do you want to select records where ALL the
keywords are in the record or do you want to select records where ANY of
the keywords are found?

I don't have the code handy right now to do this, so I have nothing to post
other than the idea.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Where do you enter the field names to be searched? I will need the the
multiple keyword search feature to search multiple fields in an Access
table.
Is this possible.

Your help is greatly appreciated.
IRam/mcp

Can't be done with a parameter query.

Can be done by writing some VBA code that modifies the query or sets the
filter on the form.

Do you want to match ALL keywords entered or do you want to match ANY
keyword entered?

Assuming ALL the keywords need to be matched, you would need a procedure
like the following UNTESTED AIR CODE.

Function fApplyFilter(txtFilter)
'Applys filter based on FSubject field

Dim strSQL As String
Dim vArray As Variant
Dim iLoop As Integer
Dim strCon As String 'conjunction string

vArray = Split(txtFilter, " ", -1, 0)

strCon = " AND "

For iLoop = LBound(vArray) To UBound(vArray)

If Len(Trim(vArray(iLoop))) > 0 Then
strSQL = strSQL & _
" fSubject Like ""*" & Trim(vArray(iLoop)) & "*""" &
strCon
End If
Next iLoop

strSQL = Left(strSQL, Len(strSQL) - Len(strCon)) & ")"

Me.Filter = strSQL
Me.FilterOn = True
End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello.
I currently have a "Keyword" button on a form that runs the parameter
query
below
Like "*" & [Enter Task Keyword] & "*"

A pop up appears where I can type in someting like Printer

I need to modify this paramater so that I can do a multiple keyword
search
in the same paramter query pop up box i.e. Printer Server

Could you help with this?

Thanks.
Iram/mcp