SOLVED: Parse Data

How could I take data and parse it into a new table?

Suppose I have a field called CustName:

Mr B Cooney


And what to break it out into 3 different fields; we'll call them CustSal, CustFI, CustLName, respectively:

CustSal: Mr
CustFI: B
CustLName: Cooney

Quickest way would be to use the Split() function to break the field down into separate sections and then use DAO or ADO to put the split components into the relevant fields.

Will the name always come in three parts or will you get data like:

Mr W Wobbly
Mrs Bloggs
Fred Bunberry
Hubert D. Booby

etc?

Can we assume 3 parts for now? :)
But I'd like to reserve the right... :)
We'll also add it to the KB.

OKay, since it is only three parts (and I'm actually more awake than I seem to have been yesterday, based on what I answered with) all you need is one function, one append query, and the source and destination tables.

Firstly, open a standalone module and paste this function into it:

[vba]Public Function GetPart(strValue As String, intPart As Integer) As String

Dim strParts() As String

strParts() = Split(strValue, Chr(32))
GetPart = strParts(intPart)

End Function[/vba]

Open a query and copy and past this SQL:

INSERT INTO tblDestination ( CustSal, CustFI, CustLName )
SELECT GetPart([Data],0) AS Expr1, GetPart([Data],1) AS Expr2, GetPart([Data],2) AS Expr3
FROM tblSource;

Remember to change tblDestination and tblSource to the names of your actual tables. Save the query.

Hi, Ab! Do you mind giving us another shot at this where it uses a comma as the delimiter? :)

Sorry about that! Just got the request clarified.

Just change the line in the function to this:

strParts() = Split(strValue, ",")

Thanks, Ab. :)
I've alerted the asker.
(I'm cheating again.)