Split a field into 2 new fields

[davea]

Hi All

I have a field in an access table which contains free text. The field
holds text which is input by users and is not related to any other
table.

What I need to do is split this field up into 2 new fields of length
30. (Resaon for this is that I need to import the data into another
system which has two 30 character fields for this text.). Can this be
done using an update query?

Any suggestions please?

Thanks

 

[Rick B]

Sure, first, make a backup copy of your table. Then, create your two new
fields in your original table.

Create an update query and pull the two new fields to it.
Under the first field, in the "Update To:" field, put the following:
=Left([YourCurrentFieldName])

Under the second field, in the "Update To:" field, put the following:
=Mid([YourCurrentFieldName],31)

 

[Al Campagna]

Dave,
Given your original text in a field called MyText, create two new fields in your
table, MyText1 and MyText2.
Use...
Left([MyText],30) 'to update MyText1
and
Mid([MyText],31) 'to update MyText2
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Al Campagna]

OOPs...
I'm sure you meant =Left([YourCurrentFieldName], 30)
Al Campagna

Sure, first, make a backup copy of your table. Then, create your two new fields in
your original table.

Create an update query and pull the two new fields to it.
Under the first field, in the "Update To:" field, put the following:
=Left([YourCurrentFieldName])

Under the second field, in the "Update To:" field, put the following:
=Mid([YourCurrentFieldName],31)

--
Rick B

Hi All

I have a field in an access table which contains free text. The field
holds text which is input by users and is not related to any other
table.

What I need to do is split this field up into 2 new fields of length
30. (Resaon for this is that I need to import the data into another
system which has two 30 character fields for this text.). Can this be
done using an update query?

Any suggestions please?

Thanks

 

[Rick B]

OOOPS Thanks, That is what I meant.


--
Rick B

OOPs...
I'm sure you meant =Left([YourCurrentFieldName], 30)
Al Campagna

Sure, first, make a backup copy of your table. Then, create your two
new fields in your original table.

Create an update query and pull the two new fields to it.
Under the first field, in the "Update To:" field, put the following:
=Left([YourCurrentFieldName])

Under the second field, in the "Update To:" field, put the following:
=Mid([YourCurrentFieldName],31)

--
Rick B

Hi All

I have a field in an access table which contains free text. The field
holds text which is input by users and is not related to any other
table.

What I need to do is split this field up into 2 new fields of length
30. (Resaon for this is that I need to import the data into another
system which has two 30 character fields for this text.). Can this be
done using an update query?

Any suggestions please?

Thanks

 

[John Vinson]

Hi All

I have a field in an access table which contains free text. The field
holds text which is input by users and is not related to any other
table.

What I need to do is split this field up into 2 new fields of length
30. (Resaon for this is that I need to import the data into another
system which has two 30 character fields for this text.). Can this be
done using an update query?

Well... I'm a bit reluctant to disagree with the three correct answers
to your question, but I'd suggest that you "unask" the question!

It is NOT necessary to create these two redudnant fields in order to
export the data! You can create a Query using two calculated fields,
and do the export from that Query:

FirstHalf: Left([fieldname], 30)
RightHalf: Mid([fieldname], 31, 30)

or if it's a 60 byte field, just leave off the 30 in the Mid
expression.


John W. Vinson[MVP]