Searching Memo field

  • Thread starter Thread starter RandyH
  • Start date Start date
R

RandyH

Thanks in advance....
I have an established database (in use for about a year) that tracks
customer support calls. The main form (workorders-which has mostly customer
information) has a subform (workorders subform) which logs the problem,
resolution, date, workorder ID number and employee who took/resolved the
problem. On the subform there is a memo field (ProblemDescription) in which
we log the problem. We would like to be able to do some sort of free text
search in the memo field ProblemDescription (which is resident in the
Workorder Table). We have callers experiencing the same problems, so the
search should ideally bring up the record (for whatever customer it happened
to before) in the subform to help expedite a solution for the customer we
happen to be helping at the time.
I have searched Google archives, but have not found anything that will
work. Has anyone done this or more to the point, know how to do this?
 
Randy,
We have an internal MDB that we use for tracking time, notes, customers and
billing. It is similar to what you are talking about. For our application
we use a little search box(textbox) in the right menu of the form with a
list box below it. When a user types in keywords like (Fix MDB Repair
Compact), we then in turn put "LIKE" infront of them and for every space we
put * so it would filter the memo field in the following mannor in the where
clause Like *Fix*MDB*Repair*Compact* this in return will give me all the
service calls in the list box below with those keywords in them.

Hope this helps.

Jim
 
Jim Pavek said:
Randy,
We have an internal MDB that we use for tracking time, notes, customers and
billing. It is similar to what you are talking about. For our application
we use a little search box(textbox) in the right menu of the form with a
list box below it. When a user types in keywords like (Fix MDB Repair
Compact), we then in turn put "LIKE" infront of them and for every space we
put * so it would filter the memo field in the following mannor in the where
clause Like *Fix*MDB*Repair*Compact* this in return will give me all the
service calls in the list box below with those keywords in them.

Hope this helps.

Jim

--
Jim Pavek
Fort Collins CO
Squarei Techologies
http://www.squarei.com
Click to expand...
Jim, this does sound like it would work, any idea how it was coded?
 
Randy,

I think the best way to handle this is to build the where clause from code
using the replace function built into Access:

Replace("this is a test", " ", "*")

That should return:

this*is*a*test

Then from there just concatinate the Like and outer * onto that.

I think that is what you where asking if not please specify.

Jim
 
Here's the SQL for the query I use that gets up to 4 keywords:

SELECT tblName.ID, tblName.MemoField
FROM tblName
WHERE (tblName.MemoField) Like "*" & [Find1] & "*" & [Find2] & "*" & [Find3]
& "*" & [Find4] & "*"

Watch out for newsreader line wrapping. Just run this query and add the
keywords as they are prompted.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Arvin Meyer said:
Here's the SQL for the query I use that gets up to 4 keywords:

SELECT tblName.ID, tblName.MemoField
FROM tblName
WHERE (tblName.MemoField) Like "*" & [Find1] & "*" & [Find2] & "*" & [Find3]
& "*" & [Find4] & "*"

Watch out for newsreader line wrapping. Just run this query and add the
keywords as they are prompted.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


RandyH said:
Thanks in advance....
I have an established database (in use for about a year) that tracks
customer support calls. The main form (workorders-which has mostly customer
information) has a subform (workorders subform) which logs the problem,
resolution, date, workorder ID number and employee who took/resolved the
problem. On the subform there is a memo field (ProblemDescription) in which
we log the problem. We would like to be able to do some sort of free text
search in the memo field ProblemDescription (which is resident in the
Workorder Table). We have callers experiencing the same problems, so the
search should ideally bring up the record (for whatever customer it happened
to before) in the subform to help expedite a solution for the customer we
happen to be helping at the time.
I have searched Google archives, but have not found anything that will
work. Has anyone done this or more to the point, know how to do this?
Click to expand...
Click to expand...
Thanks for your help guys! The SQL query works great, now I was wondering if
there was a way to get my form/subform to go to the first record from the
search results? How would I handle subsequent search results (say a keyword
yielded 3 results, can we display the first result with the option to cycle
to the next)? Or, should I just show the results of the search in a totally
different form?
 
Back
Top