Results 1 to 3 of 3

Thread: InStr

  1. #1
    Join Date
    Apr 2003

    Unanswered: InStr

    I have a field that lists names in the following format:

    lastname, firstname middleinitial
    smith, bob f

    I have a query that will separate the first and middle from the last. The problem i have is when i try to separate the middle from the first. Not all fields have a middleinital. So when i run the query I am getting error on the ones that don't have middleinital.

    here is the code.

    LFirstName: Left([FirstName],InStr([FirstName]," ")-1)

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    what is unique about the second name (or initial)? if your example is typical, your format is:
    last-comma-space-first (if there is no middle)

    so you need a function to return everything after the second space (or nothing if there is nothing).

    i am not aware of an intrinsic function that handles this, so you need to make one (must be global for the query to find it!)

    something like
    public function getMiddleName(anyString as string) as string
    dim finger as integer 'a pointer
    finger = instr(anyString, " ", 1)
    finger = instr(anystring, " ", finger+1)
    getMiddleName = mid$(anyString, finger+1)

    don't forget to: something intelligent if either one fails to find a " "
    ...use some ltrim$ & rtrim$

    you could try to stuff this directly into the query, but it will be messy and error-prone.
    something like:

    = mid$(anyString, instr(anyString, " ", instr(anyString, " ", 1)))

    maybe some iif() could fix the possible errors, but then it's even more messy, particulary if you also try to ltrim/rtrim


  3. #3
    Join Date
    Jul 2003
    You need to test for the existence of the middle initial before performing the mathematical calculation:

    intPos = Instr([FirstName]," ")
    If intPos>0 then
    'there must be a middle initial
    'no middle initial
    End If
    Inspiration Through Fermentation

Posting Permissions

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