Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Match on Name (2003)

    I have an Excel spreadsheet with the following fields:
    Name (which is a company name)
    Street
    City
    State
    Zip
    County

    I have to bring this spreadsheet into an Access database and match those fields to the records in the database by the above fields.
    The main match would be the name.
    My problem is that I am not going to get an exact match on the name.
    How would I do a match on name that would not be exact but give me some portion of the name field?
    What would be the best way to do this?
    I want to match on the name first and then see if I get an address match too. I am not sure how to do this.
    Any help would be greatly appreciated.

    Is there a way to exclude "The" and match the first 10 letters?

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match on Name (2003)

    Linda

    Not really sure what you're after or if I can help.

    You can use wild card characters to filter a name ie:

    The Money Game
    A Money Game

    A wild card as in "*Money*" would filter anything in the field with the word money
    Or you can do a search on the inStr$ function.

    Sorry I can't help further.

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

    Re: Match on Name (2003)

    You could create a query based on the imported or linked Excel table with a calculated field such as:

    ShortName: Left(Trim(Replace([Name],"the","")),10)

    and a similar query based on the existing Access table. You can then create a third query based on these two, joined on the ShortName field. You could try something similar for the address.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match on Name (2003)

    Can this formula be changed to exclude commas too. I thought it was doing this but I noticed that it didn't.

    ShortName: Left(Trim(Replace([tblCBA.Group Name],"the","")),24)

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

    Re: Match on Name (2003)

    Try

    <code>ShortName: Left(Trim(Replace(Replace([tblCBA.Group Name],"the",""),",",""),24)</code>

    Note that Replace is used twice, first to replace "the" with an empty string "", then to replace "," with "".

Posting Permissions

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