Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by SIGS, Jul 28, 2006.

  1. SIGS

    SIGS
    Expand Collapse
    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.
     
  2. Loading...


  3. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    With the use of Like in the Query

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

    --
    Good Luck
    BS"D


    "SIGS" wrote:

    > 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.
     
  4. Jennifer Cali

    Jennifer Cali
    Expand Collapse
    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.
    --
    Thank you! - Jennifer


    "SIGS" wrote:

    > 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.
     
  5. strive4peace

    strive4peace
    Expand Collapse
    Guest

    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 ;)


    SIGS wrote:
    > 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.
     
  6. SIGS

    SIGS
    Expand Collapse
    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.

    "SIGS" wrote:

    > 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.
     
  7. strive4peace

    strive4peace
    Expand Collapse
    Guest

    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 ;)


    SIGS wrote:
    > 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.
    >
    > "SIGS" wrote:
    >
    >
    >>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.
     

Share This Page