Creating fields from Existing Data

[tinyb]

I am not sure that I am in the Correct Section for this question but I am sure I can be forgiven for that :

I am attempting to build a db for a friend who has all his data in one table. During normalisation I have seperated the various threads of data into various tables of usable information. My problem is that I need to seperate his clients fullname into First & Surname i.e. Fullname: Mick Burke to FirstName: Mick and Surname: Burke - in other words make two fields from one.

Any help in doing this would be well appreciated.

 

[macca the hacke]

What you could do is search for the [space] between the First and Last name, and then take everything to the left of the space as one field, and everything to the right as another.
You will need to work out 4 things for this:
1) Position of space in string using InStr function
2) Length of string using Len function
3) What is to left of space, using combination of Left function and positioning of space
4) What is to right of space, using combination of Right Function, Length of String, and Positioning of Space in String

If you have any problems writing query then come back, but you should be able to write OK

 

[Adeptus]

An easier way is using the split function.
firstname = split(fullname, " ")(0)
lastname = split(fullname, " ")(1)

However, this will cause problems if there are more spaces in the name eg
John de Ath
would set firstname to "John" and lastname to "de"