Update Query?

[grace]

Is there a way to split a field into two using a query? I
have a field that contains data with 9 characters similar
to” “034500904”. I would like to break this into two
fields so that the “03450” is in a field called DeptNumber
and the “0904” is in a field called OfficeNumber.

Can I do this using an Update Query?

Thanks

 

[Betsy]

Is there a way to split a field into two using a query? I
have a field that contains data with 9 characters similar
to” “034500904”. I would like to break this into two
fields so that the “03450” is in a field called DeptNumber
and the “0904” is in a field called OfficeNumber.

Can I do this using an Update Query?

Hi Grace-

Yes, you can do this. You'd first create the destination fields in the
table design, so that the update query has somewhere to put the results.
Reading your post, it's a little hard to interpret how your data currently
reads. (May be something with the font you used...) However, let me guess:

If your current field reads:
8220-034500904-8221

And you'd like:
8220-03450-8221
and separately:
8220-0904-8221

This will not be hard if all your data is standardized into this reliable
pattern. In a select query (always perform a test before you update your
data...) try the following expressions:

Exp1: left([CurrentFieldName],9)&"-"&right([CurrentFieldName],4)
Exp2: left([CurrentFieldName],4)&"-"&right([CurrentFieldName],9)

If these produce what you wanted, morph the select query into an update
query and then cut and paste these expressions into the UpdateTo boxes for
the new fields you created in your table design.

HTH- Betsy