Help in Spliting Strings 1

[aagustin]

I've got a field containing a person's full name separated by ".":

Doe.Jane.P

and I need to separate them into three fields: last, first, mi.

I've figure the instr function for last and first, just having problems with the middle initial part. Any help would be appreciated.

A.

 

[nickjar2]

A.

The instr is what u will need

dim s as string
dim iPos as integer
dim sSurname as string

s = "Doe.Jane.P"

iPos = instr(1,s,".&quot

sSurname = left$(s,ipos-1)

Nick

 

[nickjar2]

Sorry,

It should be

Dim s As String
Dim iPos As Integer
Dim sSurname As String
Dim sFirst As String
Dim iPos2 As Integer
Dim sMiddle As String

s = "Doe.Jane.P"

iPos = InStr(1, s, ".&quot

sSurname = Left$(s, iPos - 1)

iPos2 = InStr(iPos + 1, s, ".&quot

sFirst = Mid$(s, iPos + 1, iPos2 - iPos - 1)

sMiddle = Right$(s, Len(s) - iPos2)


Nick

 

[aagustin]

Thanks Nick! Exactly what I needed. Worked great.

 

[Guest_imported]

A. Agustin:

I wrote a whole VB module on splitting strings and stripping characters from strings. You can find it at:

http://www.codeguru.com/vb/articles/2119.shtml

Hope that helps. The directions should be pretty self-explanatory. I wrote this code specifically for working in an Access 97 database. You may need to add a few loop counter variables (I'm not sure why, because I tested it thouroughly, but now, coming back to it years later, I was getting an error that a variable wasn't defined, but that's an easy problem to fix!!!). Other than that, it works and works well. Check out the reviews on it if you have any doubts.