Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Breaking a Field Into Parts (Access 2003)

    I should know how to do this...

    I have a field that has two parts with a hyphen in the middle (data provided to us by a third party). I want to put the part to the left of the hyphen in one field (location - text) and the right (customer number) in another. The number of characters in the two parts varies.

    How to I break it apart? Can I do it with a couple of queries? I have almost 600,000 records where I need to to this.

    Thanks!

    Nancy

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking a Field Into Parts (Access 2003)

    I can't believe I'm doing this, given how much help I need from the lounge, but I've managed to do it with these calculated fields in a query.
    DB Attached
    Hope this helps.
    Silverback
    Attached Files Attached Files
    Silverback

  3. #3
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking a Field Into Parts (Access 2003)

    I used Hans' left side suggestion and Silverback's right. Thanks both of you!

    I appreciate your fast responses.

    Nancy

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

    Re: Breaking a Field Into Parts (Access 2003)

    Edited by HansV to correct error

    The expression to use for the first part is
    <code>
    Trim(Left([FieldName],InStr([FieldName],"-")-1))
    </code>
    and that for the second part
    <code>
    Trim(Mid([FieldName],InStr([FieldName],"-")+1))
    </code>
    where FieldName is the name of the field with a hyphen.
    You can use these as calculated columns in a select query. If you want to store the calculated values in the table, create an update query based on the table, add the Location and Customer Number fields to the query grid, and enter the above expressions in the 'Update to' line for these columns, then execute the query.

Posting Permissions

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