Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    InStr Function (2003)

    Hi folks, this is probably easy if you know how. I have two tables with manufacturers names. Table1.Name is long, and Table2.Name has been truncated to 10 chars. How can I search to see if Table2.Name appears anywhere in Table1.Name - have tried InStr, but am only getting direct matches.

    appreciate your help.

    Cheers.

    Ina

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

    Re: InStr Function (2003)

    You can create a query based on Table1 and Table2. Do not join the tables - if Access creates a join automatically, delete it.
    Add the Name field from both tables, and in the Criteria line for the one from Table1, enter
    <code>
    Like <!t>[Table2].[Name] & "*"
    </code>
    This will select those records whose Names match on the initial characters. If you want to match on any part, use
    <code>
    Like "*" & <!t>[Table2].[Name] & "*"</code>

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: InStr Function (2003)

    Cheers Hans, will give it a shot.

    Ina

  4. #4
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: InStr Function (2003)

    Hi Hans, Thanks for this - Have tried both of these options. The first returns me nothing, and the second seems to match everything to everything, so didn't have much luck. Am wondering whether I have misinterpreted something, perhaps the dot before the name, or if there are any other ways I can compare between two strings?

    thanks for help! Ina

  5. #5
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: InStr Function (2003)

    Hi Hans, no I was in such a hurry to do it, we must have crossed mails. No worries, have done it now and it works perfectly - another bit of black magic! thanks for your help.

    Ina :-)

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

    Re: InStr Function (2003)

    I edited my previous reply shortly after posting it - did you notice that it now has <!t>[Table2] before the point?

    Another way would be to remove the criteria, and add a calculated column
    <code>
    Pos: InStr(<!t>[Table1].[Name],<!t>[Table2].[Name])
    </code>
    Enter <code>>0</code> in the Criteria line.
    Clear the Show check box for this column.

Posting Permissions

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