How do I split information one cell into two?

T

Texas Ruegg

I have a table with a filed that contains course discriptions "Management
Strategy 3 Hours". Now I need to split the discription into two fields; one
with the discription "Management Strategy" and one with the hours,"3 Hours".
I have a report that is based on a query, an I need to split out this
information so I can line up the course discritpions in one column on the
report and the number of hours in another column. Can I split it in the
query or even at the table?
 
R

Rick B

A "cell" is in Excel, not Access.

You give us one example of the data. Waht are others? What can you tell
Access to look for in EVERY case that will let it split the data? Is it
always the 21st character where the "hours" starts? Is there always some
string followedd by a number, followed by more text?

We need details.
 
T

Texas Ruegg

Yes, sorry it is the field that I need to split. A few examples are:
Management Strategy 3 Hours
English Comp 3 Hours
General Electives 6 Hours

I need to split them into two fields like:
Field 1
Management Strategy
Field 2
3 Hours

The problem is that they are all different lengths and number of hours. I
actually have 10 fields (labeled: Courses 1 thru Courses 10)that I need to
split exactly the same way.

Thanks for any help,
Texas
 
S

Sprinks

Tex,

Assuming the courses are all under 10 hours, it's pretty simple, although
I'd do it in two update stages just be safe. Since there might be a variable
number of spaces between the coursename and the hours, the number and "hours"
and trailing, the first stage would remove any trailing spaces, then the last
five characters, and then another space trim. This should leave the Field1
field with the number of hours as the last character:

' Shown on 2 lines for readability
UPDATE YourTable SET YourTable.CourseName =
RTrim(Left(RTrim([CourseName]),Len(RTrim([CourseName]))-5));

The next update query would:
- Remove the last character from the description, then trim any trailing
spaces
- Write the value of the last character to the numeric Hours field

' Shown on several lines for readability
UPDATE YourTable
SET YourTable.CourseName =
RTrim(Left([CourseName],Len([CourseName])-1)),
YourTable.Hours = Val(Right([CourseName],1));

Hope that helps.
Sprinks
 
S

Sprinks

One other thing, Tex. As with any update attempts, be sure to make a backup
copy of the original data for unanticipated results.

Sprinks
 
Top