Extracting Data

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

Guest

Hi,
I have been reading this and tried the help file etc. WIth no luck.
I have a column with numbers and a colon. For example:

28:34

Looking at the database, this is in text format. Format will always be the
same with one slight difference: 2-3 numbers before, 2-3 numbers after, the
":" (colon). For some reason the examples I have read about don't work. I
tried the following:
Left([myfield],2,)
I've replaced the '2' with a '3'. I'm not quite doing something just right.
All I want is everything on the left of the colon, and everything that may
be on the right of the colon. Separated, of course.

This will eventually be exported into EXCEL, so if it's easier there I'll
ask that newsgroup.
(Person responsible for this has been, shall we say, reassigned?)

Thanks, people,
RickyDee
 
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.
 
Why numbers with a colon, this is normally used in Time format, is that what
the data is?

Bruce
 
Hi Bruce. Well actually, it was a durned good inventory. Then someone
decided that it might be a good idea to put both numbers into the same
column/cell. Now I can't break them apart. I have an answer from PC
DATASHEET that I'll try. I'll be back (but probably not if this works). And
I have all confidense that it will.

RickyDee

Bruce said:
Why numbers with a colon, this is normally used in Time format, is that what
the data is?

Bruce
RickyDee said:
Hi,
I have been reading this and tried the help file etc. WIth no luck.
I have a column with numbers and a colon. For example:

28:34

Looking at the database, this is in text format. Format will always be the
same with one slight difference: 2-3 numbers before, 2-3 numbers after, the
":" (colon). For some reason the examples I have read about don't work. I
tried the following:
Left([myfield],2,)
I've replaced the '2' with a '3'. I'm not quite doing something just right.
All I want is everything on the left of the colon, and everything that may
be on the right of the colon. Separated, of course.

This will eventually be exported into EXCEL, so if it's easier there I'll
ask that newsgroup.
(Person responsible for this has been, shall we say, reassigned?)

Thanks, people,
RickyDee
Click to expand...
Click to expand...
 
PCDATASHEET,
Well, I've tried a number of different ways to do as you suggested, but
since I'm not that thoroughly 'enlightened' on EXCEL, it didn't work. I
created a query on the appropriate table, with the column with the ':'s in
it. I added two columns to place the information once broken apart. I keep
getting error messages stating that there is a . (Period) or parentheses or
some other such thing that isn't supposed to be there. The columns in the
query are as follows:
RESULTS (Table)
RESULTS (column where the colons are)
Sort (blank)
Everything else (blank)

RESULTS1
RESULTS
All else blank

RESULTS2
RESULTS
All else blank.

Now, here is where I'm at a loss as to where to place the string you sent.
Of course I renamed the parts for the columns where I want the information
sent to under the RESULTS1 and RESULTS2 columns in the query.

Sorry, but if you have time to send a little bit more detailed information
I'd certainly appreciate it.
Thanks again,
RickyDee
 
Back
Top