1. Registered User
Join Date
Apr 2003
Posts
2

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

lastname, firstname middleinitial
ie.
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. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
what is unique about the second name (or initial)? if your example is typical, your format is:
last-comma-space-first-space-middle
or
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:
...do 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

izy

3. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
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
LFirstName=Left([FirstName],intpos-1)
ELSE
'no middle initial
End If

#### Posting Permissions

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