Splitting a field into TWO!!! 1

[FuzzyBear9]

Hi,

I have used an expression in a query in Access 97 to join two fields: FirstName and Surname together to produce one field, eg.

FirstName: Michael
Surname: Page

Contact: Michael Page

PROBLEM:

How can I REVERSE the process!!! I want to take a field and split it into FirstName and Surname! (I will need to exclude the 'space' as well). Is this quite simple. I think I have to use LEN, but I am not sure what to do.

Any help would be greatly appreciated

 

[aplusc]

You can use Mid(field,start,length) if the names are always the same length. Im not 100% sure, but Access might also support InStr (you can use it to find " &quot.

 

[jhall156]

But beware the infamous Ihava Middlename Smith, Johann Von FunkyName or
Vincent St. Vincent and other such problems. JHall

 

[tlbroadbent]

Here is a simple example. It doesn't handle middle names but should get you started.

Select
FirstName=Left(FullName, Instr(FullName, " &quot-1),
LastName=Right(FullName, Len(FullName) - Instr(FullName, " &quot)
From TableName

You may want to create a function to handle the parsing. Access does have a Split function (basSplit in Acc 97) that is very useful. There are many threads on Tek-Tips dealing with parsing and the Split function. Use advanced search to find threads and FAQs.

Check out these threads.

thread181-109159
thread181-60744
thread181-60326 Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[FuzzyBear9]

Thanks guys! Especially tlbroadbent!