Extract number from String

[DOSRoss]

My ultimate objective is to parse thru a line similar to that below:



adocExecuteComplete| SQL Request exceeded reasonable time, adocVER2_CAD: 6627ms elapsed, type cmdText, status esOK, cursor ctUnspecified, lock ltReadOnly, records 1

I need to extract the "6627" as a number to a field ...
the number can be 1 to 99999
the number is almost always followed directly by "ms" (those are the only ones I need to collect right now)

I would also like to extract the string from the beginning up to the number into another field in the database

[ranman256]

=left[field],4)

[DOSRoss]

I am sorry ... I don't understand your reply.
I need to find the "ms" ... examine to see if a digit precedes it
If so, how many digits precedes the "ms" and extract those to a field

[CJ_London]

don't think left will hack it - range is 0-99999

providing the text always starts with a number try using the val function instead

val("123abc") with return 123
val("12345xyz") will return 12345

even works for decimals
val("12.3abc") with return 12.3

but not currency
val($12.3aaa") will return 0

[orange]

How about telling us the overall need/opportunity?
Don't focus on the 1 single issue facing you at the moment.
Plan what you are going to do based on the requirements you know.
You can implement your How's based on the business priorities; but designing 1 thing at a time is a poor strategy, generally.
What is the big picture from the 30,000 ft overview.

Why do you have to parse? Where does the data come from? If possible, can they provide what you need since they are giving you output?

This looks like error analysis of some sort????

[CJ_London]

Just realised it is the whole string

val(mid("adocExecuteComplete| SQL Request exceeded reasonable time, adocVER2_CAD: 6627ms elapsed, type cmdText, status esOK, cursor ctUnspecified, lock ltReadOnly, records 1",instr("adocExecuteComplete| SQL Request exceeded reasonable time, adocVER2_CAD: 6627ms elapsed, type cmdText, status esOK, cursor ctUnspecified, lock ltReadOnly, records 1",":")+1))

returns 6627

or code

val(mid([myField],instr([myfield],":")+1))

[DOSRoss]

I start with a text file and bring it into Access 2007
This is a log of 'actions' in a SQL db.

My whole objective is to get the "Line Number" which will is separated when the txt file is imported and the "ms" number.
That is really all I need to report on from the text file.
I am having a challenge to get that number precisely.

There would be many lines in the log that have "ms" (i.e. 'msg') but not a number precede it.
If there is a single digit before the 'ms' then there may be as many as five more.

[DOSRoss]

preceding the number that I am attempting to extract could be any number of text items ... the ':' is not always in front of the number.

[DOSRoss]

"pseudo code"

take the record of a database
inspect Field4 to see if the text "ms" is present
if so ...
check to see if there is a digit that precedes it .. i.e. '5ms'
if so ...
check to see how many digits precede the 'ms' ... i.e. '54321ms'
if there are as many as five
keep the five digit (or 4, or 2, etc) number - update it to field5 of the same record
Go on to the next record

[DOSRoss]

This works for every instance that there is a ":" preceding the number, but that is only about half of the time, if that many.

[CJ_London]

can you guarantee that 'ms' or perhaps 'ms ' will never appear prior to where you expect to see it?
are there records which have 'ms' or 'ms ' but in a different context (implication is you will)

[DOSRoss]

Just realised it is the whole string

val(mid("adocExecuteComplete| SQL Request exceeded reasonable time, adocVER2_CAD: 6627ms elapsed, type cmdText, status esOK, cursor ctUnspecified, lock ltReadOnly, records 1",instr("adocExecuteComplete| SQL Request exceeded reasonable time, adocVER2_CAD: 6627ms elapsed, type cmdText, status esOK, cursor ctUnspecified, lock ltReadOnly, records 1",":")+1))

returns 6627

or code

val(mid([myField],instr([myfield],":")+1))

Reply:
This works for every instance that there is a ":" preceding the number, but that is only about half of the time, if that many.

[Perceptus]

I vaguely remember something along the lines of SQL being slow when casting data to another type or converting.

[DOSRoss]

can you guarantee that 'ms' or perhaps 'ms ' will never appear prior to where you expect to see it?
are there records which have 'ms' or 'ms ' but in a different context (implication is you will)

Reply:
The 'log' that contains the source data is all time based. the 'ms' is for millisecond obviously.
The exact 'ms' construction follows the data I am trying to collect. ie 234ms, or 1234ms etc.
anything with 'ms ' or 'msg' or 'ms -anything' would not have a digit preceding it ... or multiple digits

[DOSRoss]

Finding the 'ms' and parsing backwards thru the string, until all the digits are collected, is probably the best direction.