limit text in a querie

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

Guest

I want to be able to pull out an error code that could range from anr0001 to
anr9999 out of a long text string of about 20 words. How can I do this in a
query? Example: servername xyz ANR001 Disk01 utilization on servername is at
85.1 and migrations are not running. Migrations should have been started at
30 percent
 
Hi Kris,

If I understand your question correctly, you should be able to use the mid()
and instr() functions to do what you want. Instr([YourFieldName],"ANR") will
return the position of the first occurance of ANR within the string. If you
use that as the starting point within the mid() function, and use a length of
7, you should get what you want. Something like:

mid([YourFieldName],Instr([YourFieldName],"ANR"),7)

Hopefully I didn't mix up the order of the arguments, but I'm pretty sure
that is correct.

HTH, Ted Allen
 
Write a Function to do it.

The function would need to look repeatedly for the string "anr" ("
anr" would be more efficient if it can be guaranteed that the error
code will always be preceded by a space) and then check that it was
followed by exactly four numeric digits, before returning the whole
string. The coding of the function is left as an exercise for the
student (!), but it would be fairly straightforward.

I want to be able to pull out an error code that could range from anr0001 to
anr9999 out of a long text string of about 20 words. How can I do this in a
query? Example: servername xyz ANR001 Disk01 utilization on servername is at
85.1 and migrations are not running. Migrations should have been started at
30 percent
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Thank you Ted, I'll try this tommorrow at work. I'm pulling some database
messages out of a ticketing sytem to do metrics tracking. That's why I wanted
to pull out specifics. Anyway, thank you very much.

Ted Allen said:
Hi Kris,

If I understand your question correctly, you should be able to use the mid()
and instr() functions to do what you want. Instr([YourFieldName],"ANR") will
return the position of the first occurance of ANR within the string. If you
use that as the starting point within the mid() function, and use a length of
7, you should get what you want. Something like:

mid([YourFieldName],Instr([YourFieldName],"ANR"),7)

Hopefully I didn't mix up the order of the arguments, but I'm pretty sure
that is correct.

HTH, Ted Allen

kris said:
I want to be able to pull out an error code that could range from anr0001 to
anr9999 out of a long text string of about 20 words. How can I do this in a
query? Example: servername xyz ANR001 Disk01 utilization on servername is at
85.1 and migrations are not running. Migrations should have been started at
30 percent
Click to expand...
Click to expand...
 
Back
Top