Breaking a Field Into Parts (Access 2003)

[WriterCat]

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

[silverback]

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

[WriterCat]

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

I appreciate your fast responses.

Nancy

[HansV]

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.