Welcome to SPN

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

Sign Up Now!
  1. Guest ji, please consider donating today!
      Become a Supporter    ::   Make a Contribution   
    Monthly Recurring Target: $300 :: Achieved: $95

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.
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page