Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    4

    Unanswered: Changing Company Names

    Hello,

    i have a Companies table which lists various things about each company we deal with.

    However when searching for specific company it can be a task as some are like this

    TARMAC LIMITED
    FLEXITALIC LIMITED
    etc...

    and others are like:

    D A THOMPSON ENGINEERING
    J THOMPSONS AND SONS LTD
    etc...

    The problem with searching for these names (J THOMPSONS...) is that they are known as THOMPSONS, but when searching for this it comes under J because of the first initial.

    I was wondering if there was a way of using a query to delete the initials if it was followed by a space (e.g the J). Otherwise it would chop the first character off other names which are fine.

    I would go through each name individually, but with 7,000 or them i would need to put in a little overtime ;0)

    thanks

    Siy

  2. #2
    Join Date
    Apr 2003
    Posts
    42

    Use the InStr function...

    Rather than edit your data why not create a 'search string function' that will search through your records and return those that include that specified string in it.

    Expand upon the code below to include a query of your database and there you have it!



    code:

    Dim strBusiessName As String
    Dim strSearchFor As String
    Dim MyPos As Variant

    strBusiessName = "D A THOMPSON ENGINEERING" ' String to search in.
    strSearchFor = "THOMPSON"

    ' A comparison starting at position 1
    MyPos = InStr(1, strBusiessName, strSearchFor, 1)
    MsgBox (MyPos)

    'note if string is not found then 0 is returned,
    ' otherwise the position of the string is returned
    If MyPos > 0 Then
    MsgBox ("string found")
    Else
    MsgBox ("string not found")
    End If

    :End Code

    I'll leave the rest of the coding to you.
    (read the HELP files to read exactly how InStr works)

Posting Permissions

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