Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Testing partial names (2003)

    I have a DB which holds First Name and Last Name. The user wants to be able to produce a report for one person or all persons in the DB.
    I have created a query with a calculated field which concatenates the name fields and successfully tests against a parameter field ie user types in Brian Anderson and the query lists only one record for the chosen person.
    But they want to be able to type abbreviations for both names on a single input - eg. "Bri" space "And" (without the quotes of course) and still get a match to Brian Anderson.
    How do I go about this, please?
    Thanks
    Silverback
    Silverback

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Testing partial names (2003)

    You could use this in the criteria line for the calculated field:
    <code>
    Like IIf(InStr([Param]," ")>0,Left([Param],InStr([Param]," ")-1) & "*" & Mid([Param],InStr([Param]," ")+1),[Param]) & "*"
    </code>
    where [Param] must be replaced with the parameter prompt (between square brackets) or - preferably - a reference to a text box on a form.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Testing partial names (2003)

    I was facinated by your expression Hans. The first part Instr returns 4 so you subtract 1 to give you the first 3 characters then add the rest of the string, except I can't see why it stops with the space to give just the first name, but of course it does. The second part I thought would return 3 so I do not understand why you add 1. Maybe you can help me understand that.

    So I decided to give this a try. I built a query based on a table that has a field with a name, (a whole name) and entered the expression in the criteria of the name field.

    I build a simple form (Form1) with a text box and named it txtName. I then went back to the query and substituted [Param] with the reference to the text box, and named the query qryTestExpression. Then I went back to the form and expected to make the query its record source , but Access would not allow it saying the qryTestExpression was not a valid name.

    If I use the expression with the word [Param] in the query when I open the form it prompts for Param and returns the expected records, but I can't get it to work with a text box.

    My expression with the text box is:
    Like IIf(InStr([Forms!Form1.txtName]," ")>0,Left([Forms!Form1.txtName],InStr([Forms!Form1.txtName]," ")-1) & "*" & Mid([Forms!Form1.txtName],InStr([Forms!Form1.txtName]," ")+1),[Forms!Form1.txtName]) & "*"
    Thanks
    chuck

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Testing partial names (2003)

    Let's look at the expression using the parameter Param:

    Like IIf(InStr([Param]," ")>0,Left([Param],InStr([Param]," ")-1) & "*" & Mid([Param],InStr([Param]," ")+1),[Param]) & "*"

    The formula InStr([Param]," ") returns the position of the first space in Param if there is one, or 0 if there is no space. Let's designate InStr([Param]," ") with P. The expression becomes

    Like IIf(P>0,Left([Param],P-1) & "*" & Mid([Param],P+1),[Param]) & "*"

    If P>0, i.e. if Param contains a space, Left([Param],P-1) is the part of Param to the left of and not including the space, and Mid([Param],P+1) is the part of Param to the right of and not including the space. So if the user enters "Bri And", Left([Param],P-1) is "Bri" and Mid([Param],P+1) is "And".
    The total expression evaluates to

    Like "Bri*And*"

    In other words, search for anything that begins with Bri and contains And further on. By the way, this makes me realize that the expression can be simplified - I will post a new reply to Silverback. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    If P=0, i.e. if Param doesn't contain a space, the expression evaluates to [Param] & "*", i.e. search for anything beginning with the value of Param.

    To refer to a text box on a form, you must enclose each part in square brackets: [Forms]![Form1]![txtName]

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Testing partial names (2003)

    Chuckrau's question made me realize that the expression can be simplified:
    <code>
    Like Replace([Param]," ", "* ") & "*"
    </code>
    This will work in Access 2002 and later; the Replace function doesn't exist in Access 97 and doesn't work in expressions in Access 2000.
    Instead of [Param], you can use a reference to a text box on a form, e.g. [Forms]![frmParams]![txtName]

  6. #6
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Testing partial names (2003)

    Thanks for explaining Hans. I realize I was treating Instr as Len. If I live long enough I may someday get this stuff straight.

    The expression has gone from facinating to remarkable.
    Thanks
    chuck

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing partial names (2003)

    Hans
    I concur with Chuckrau. I am boggled by the first expression (which is all working successfully now, of course) and can't believe what I'm seeing with the 'simplified' expression, which I haven't tried yet.
    Grateful thanks
    Silverback
    Silverback

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •