Query Expression to find spaces between data

[JohnLee]

Good day folks,

I would like some assistance with the following:

I have some data which is imported into a table automatically; the fields are fixed width and are named as follows:

Name
Address
Post Code

The name field is set to a maximum of 50 characters, the address field is set to a maximum of 150 characters and the post code field is set to a maximum of 8 characters.

The first task I have to do is to break down the Name field into 3 separate fields as follows:

strTitle
strSurname
strForname

I have a new table which has fields set up as follows:

strTitle
strSurname
strForename
strAddress Line 1
strAddress Line 2
strAddress Line3
strAddress Line 4
strPostcode

I want to append the data from the import table [tblImport] to my new table with the Name field splitting out the information from the Name field into the 3 fields in my table called tblConvertedImport.

For instance Mrs Joan Brown, from the Name field in the tblImport table should be broken down to appear as follows in the tblConvertedImport table:

strTitle Field: Mrs
strSurname Field: Brown
strForename Field: Joan

The Title field is set to a maximum of 4 characters
The Surname field is set to a maximum of 30 characters
The forename field is set to a maximum of 20 characters

How do I create an expression that identifies the spaces between the data in the Name field of my tblImport table and split each segment into separate fields for appending to my tblConvertedImport table.

I know how to concatenate several fields into one field that is the reverse of what I have above for instance I have an expression as follows:

CustomerName: [strTitle] & " " & [strInitials] & " " & [strSurname]

So from another existing table I concatenate the above three fields into one, but what I need to do is the opposite of this for some data being received electronically which is not in the desired format.

Where I am having difficulty is in writing and expression that identifies the space between the customers Title, the Surname and the Forename. So for instance the expression finds the first space and all the characters to the left are the Title part of the customers salutation, it finds the next space and all the characters from the first space to the next space are the Forename of the customers salutation and then the next space and all the characters to the right of that space are the Surname of the customers salutation.

Can this be accomplished via an expression in an append query.

Your assistance would be most appreciated.

John

 

[DCrake]

You can use the Split() function that seperates different words in a string using the space as a delimiter

MyNameArray = Split("Mrs Joan Brown"," ")

MyNameArray(0) = "Mrs"
MyNameArray(1) = "Joan"
MyNameArray(2) = "Brown"

 

[Trevor G]

John you can use IntStr as formula to get the first and last like this, change Red to your Field Name.

Expr1: Left([PersonName],InStr([PersonName]," "))

Last: Right([PersonName],Len([PersonName])-InStrRev([PersonName]," "))

Also you can consider the following code which can be found on Allen Brownes website

http://allenbrowne.com/func-10.html

 

[JohnLee]

Hi DCrake,

Thanks for your response, how do I use that within the append query?

the field in the tblImport is called Name, so do I write an expression like sokmething like this in the field row:

MyNameArray = Split([Name])

Sorry for being a bit of a daunce, but I'm not clear on how to use your suggestion.

John

 

[DCrake]

If you are wanting to use it directly from a query then you may need to include a custom function

Public Function ExtractWord(AnyString As String, Element As Integer) As String

Dim Words 
Words = Split(AnyString,"," ")

ExtractWord = Words(Element)

End function

Query Usage:

MyTitle: ExtractWord([FullName],0)

MyForename: ExtractWord([FullName],1)

MySurname: ExtractWord([FullName],2)


This basic function relies on the fact that there will always be a Title, Forename & Surname in the string

 

[JohnLee]

Hi Trevor G,

Thanks for your response, so from your example, I can see how to get the first part and last part of the string, but not how to get the middle part.

I believe I need to use the mid function somehow, so would I need to use that in conjuction with the Left function to get my starting point for the data in the middle or is it more complex than that.

Your assistance is most appreciated.

I'm also trying DCrake's suggestion, but would also like to explore the option you provided more as well.

John

 

[JohnLee]

Good day DCrake & Trevor G,

Thanks for all you help yesterday, I tried both your options and the one you pointed me to by Allen Browne.

I have chosen to use the parseWord() function by Allen Browne, it works a treat and has given me a lot more scope to deal with other aspects of my Database.

Thanks once again.

John