Can I create a wild card search of a memo field? If so, how?

[Guest]

I have a memo field called SpecTrainingSkillsMEM. In this filed I store such
skills like Spanish or German speaker and or computer tech and etc. I would
like to be able to, using a parametar search, type in say Spaninsh for
instance, and get all the people in the database that have listed Spanish as
a second language.

 

[Guest]

With the use of Like in the Query

SELECT TableName.SpecTrainingSkillsMEM
FROM TableName
WHERE TableName.SpecTrainingSkillsMEM Like "*" & [Please select a language]
& "*"

 

[Guest]

SIGS,

1) Build a query based off the corresponding table
2) Add in which ever fields you like in addition to the
SpecTrainingSkillsMEM field
3) In the criteria below the SpecTrainingSkillsMEM field, enter: Like
"*Spanish*"

This will pull up anyone who has the word "Spanish" in the memo field. You
can create others for what ever other languages (French, German, etc.) you
need.

 

[strive4peace]

yes, here is an example for you

~~~~~~~~~~~~~~~~~~~~~
you have a form for displaying or editing records.

In the header of the form, you have:
1. an unbound textbox named SrchMemo
(label caption --> Text to Find
2. an unbound option frame named FraSrchMemo

optionbutton: value, caption
1, beginning
2, middle
3, end
4, exact

3. a unbound listbox named FindPeople whose recordsource is
PersonID,
Lastname & ', ' & Firstname as Person,

ColumnCount --> 2
ColumnWidths --> 0;1.5
ListWidth --> 1.5

in code behind a form

'~~~~~~~~~~~~~~~
'written by Crystal
'strive4peace2006 at yahoo dot com

Private Function FilterListbox()

dim mWhere as string

if isNull(me.SrchMemo) then
mWhere = ""
else
select case me.FraSrchMemo
case 1
mWhere = "MemoFieldname LIKE '" _
& me.FraSrchMemo _
& "*'"
case 2
mWhere = "MemoFieldname LIKE '*" _
& me.FraSrchMemo _
& "*'"
case 3
mWhere = "MemoFieldname LIKE '*" _
& me.FraSrchMemo _
& ""
case 4
mWhere = "MemoFieldname = '" _
& me.FraSrchMemo _
& "'"
end select

end if

FindPeopleSQL mWhere

End Function

'~~~~~~~~~~~~~~~

Private Function FindPeopleSQL(byVal pWhere as string)

Dim strSQL as string

strSQL = "SELECT PersonID, " _
& Lastname & ', ' & Firstname as Person " _
& " FROM Tablename " _
& iif(len(mWhere)=0,""," WHERE " & mWhere) _
& "ORDER BY Lastname, Firstname;"

'comment next line or remove after debugged
debug.print strSQL

me.FindPeople.RowSource = strSQL
me.FindPeople.Requery

End Function

'~~~~~~~~~~~~~~~

Private Function FindRecord()

'thanks for ideas, freakazeud

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mRecordID As Long
mRecordID = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "PersonID= " & mRecordID

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Function
End If

End Function

'~~~~~~~~~~~~~~~

You would also want to include error handling code -- left
it out to just give you an idea of logic

'~~~~~~~~~~~~~~~

on these controls
SrchMemo
FraSrchMemo

AfterUpdate event -->
=FilterListbox()

on FindPeople AfterUpdate event -->
=FindRecord()


It sounds, however, that you may need to take some of the
information out of your memo field such as: Language_Main,
Language_Second that should have their own fields. Memo
fields are good for extra comments, but don't make the
mistake of lumping discreet pieces of information into a
comment field.


Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Guest]

That works but with 2700 records and dozens of different combinations of
skills I would need to create far too many queries for that to be practical.
What I would like to do is do a straight parameter query, have the query
dialogue pop up when you run the query and be able to just type in a word
(bracketed with the appropriate wildcard symbols) that would allow a search
of the memo field directly with just one query construct. I did not design
the database; I am just trying to help out by finding a quick/handy
workaround rather that redesigning the entire dB.

 

[strive4peace]

Hello Gene,

you talk of creating a query ...

you can use the same logic as the form filter to construct a
WHERE clause for a query

here is some code you can put into a general module:

'~~~~~~~~~~~~~~~~~~~~~~~~
'---------------- MakeQuery

Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 10-22-05
On Error GoTo MakeQuery_error

Dim mStr As String, mBooMake As Boolean

'if query already exists, update the SQL
'if not, create the query

mBooMake = True

DoCmd.Echo False
DoCmd.SetWarnings False
On Error Resume Next
Err.Number = 0
mStr = CurrentDb.QueryDefs(qName).Name
If Err.Number = 0 Then mBooMake = False
On Error GoTo MakeQuery_error
DoCmd.Echo True
DoCmd.SetWarnings True

If mBooMake Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

MakeQuery_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

MakeQuery_error:
MsgBox Err.Description, , "ERROR " & Err.Number & "
MakeQuery"
DoCmd.Echo True
DoCmd.SetWarnings True
Stop
'Press F8 to step through code and find problem
'comment out when program is debugged
Resume
Resume MakeQuery_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day