Breaking apart a field.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi people.
Someone put two values together that I didn't want put together, I don't
know how it was done, and I need to take the field back apart. Problem:
In the column in question, each cell looks like this: 123:456 Sometimes
the numbers before the ":" (colon) are two numbers, sometimes three. Same
after the ":". I need to take these numbers out and create two columns, one
with the first set of numbers, the other with the second set of numbers.
Under ACCESS/QUERIES section of the ACCESS newsgroup I was given this
explanation. However, I don't know what to do with it. Could someone please
tell me exactly what to do with this? Or maybe another way to go about it?

Put the two following expressions in empty fields in a query:

LeftNumbers:Left(NameOfNumbersField,Instr(NameOfNumbersField,":")-1)
RightNumbers:Mid(NameOfNumbersField,Instr(NameOfNumbersField,":")+1)

You can now export these two fields in the query to Excel.

Any other suggestions?
Thank you.
RickyDee
 
Well, I would create two new fields in my table, then run an update query.
In the first field, I'd update it with...
LeftNumbers:Left(NameOfNumbersField,Instr(NameOfNumbersField,":")-1)

and in the second field, I'd update it with..
RightNumbers:Mid(NameOfNumbersField,Instr(NameOfNumbersField,":")+1)

Then, I'd delete the original field.

Rick B
 
Nope. tried it again. I made it an UPDATE query on the original sheet.
This gave me the "UPDATE TO" line. I copied this line EXACTLY to the "UPDATE
TO" line/cell in the query design mode. I got an error stating that there is
either an invalid DOT, OPERATOR or PARENTHESIS somewhere in there. I (of
course) changed the names of the "NAME OF NUMBERS FIELD", still nothing.
Little robot keeps telling me that there is invalid stuff in the formula.
This is where I keep getting stuck. (It's probably easy enough, but I'm
getting old).
 
Sorry, try...

Left(NameOfNumbersField,Instr(NameOfNumbersField,":")-1)

Mid(NameOfNumbersField,Instr(NameOfNumbersField,":")+1)
 
Back
Top