Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lengthy Parsing (2003 Sp2)

    Is there a way to parse a potentially lengthy string? For example: Name, Phone, Address, State, PostCode. In other words: Joe Bloggs, 5555 5678, 123 Smith St, Smithfield, QLD, 1234. The only other trouble is, phone numbers and/or addresses and/or state and/or postcode may be missing. At least there will always be a name, and if any other data is missing, there will be a string of commas until the next data appears, if any.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lengthy Parsing (2003 Sp2)

    The easiest way is to export (or paste) the data to Excel, then to use the Data | Text to Columns menu option. Specify Delimited as type, and Comma as delimiter in the next step. Excel will parse the strings into as many columns as needed. You can import the data into Access again.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lengthy Parsing (2003 Sp2)

    I thought about that idea but it needs to be an automated process each month so I thought is was easier in the long run to keep it as simple as possible and all in Access. But I guess setting up an Excel macro is probably the most straightforward, and after all, it's only one more keystroke.

    Thanks again

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lengthy Parsing (2003 Sp2)

    If you prefer to do it in Access itself, create the following function in a standard module:
    <code>
    Public Function SplitPart(varValue, strDelimiter As String, n As Integer)
    SplitPart = Null
    On Error Resume Next
    SplitPart = Split(varValue, strDelimiter)(n - 1)
    End Function
    </code>
    You can now use expressions like this in a query:
    <code>
    Name: Trim(SplitPart([LongText], ",", 1))

    Phone: Trim(SplitPart([LongText], ",", 2))
    </code>
    Again, it is essential that the commas are used consistently, otherwise the data will shift.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lengthy Parsing (2003 Sp2)

    Wow! That's amazing! Thank you so much - it works like a dream.

Posting Permissions

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