Consulting

Results 1 to 7 of 7

Thread: SOLVED: Parse Data

  1. #1
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location

    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
    ~Anne Troy

  2. #2
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    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?

  3. #3
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Can we assume 3 parts for now?
    But I'd like to reserve the right...
    We'll also add it to the KB.
    ~Anne Troy

  4. #4
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    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.

  5. #5
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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.
    ~Anne Troy

  6. #6
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    Just change the line in the function to this:

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

  7. #7
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Thanks, Ab.
    I've alerted the asker.
    (I'm cheating again.)
    ~Anne Troy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •