Seperate Notes/Memo field into seperate data fields

I have a notes field on our database that has information I need to extract
into seperate fields. Thenotes fields are formatted very consistently, so
the information I need is on a certain line but I am not sure how to get it
out. Here is an example of the data in each record:

DRY MATL. 3-4 HRS @ 220-240 DEGREES
************************************************** *****

TOOL #: T0298
MANUAL
CYCLE 60

What I am trying to extract is the tool number (T0298 in this case). So
basically I want everything on line 4 AFTER the : mark for each record.

Is there a function or query I can use to extract this? Please help. Thanks!

- joe

Joe,
You can use the INSTR function to locate "TOOL #: "

The following line tells you the position that your string
starts at. Add 8 to get to the position where your tool
number starts.

fldstart:InStr(1, yourfield, "TOOL #: ", 1) + 8


Then, use MID function. It will extract from your field,
starting at position where tool number is located, for 5
positions. I guessed that your tool numbers were all the
same length.

exttoolnum:MID(yourfield, fldstart, 5)

I usually do this in a function that I call from my query,
but you should be able to do it in the query grid.


-----Original Message-----
I have a notes field on our database that has information
I need to extract
into seperate fields. Thenotes fields are formatted very
consistently, so
the information I need is on a certain line but I am not
sure how to get it
out. Here is an example of the data in each record:

DRY MATL. 3-4 HRS @ 220-240 DEGREES
************************************************* ******

TOOL #: T0298
MANUAL
CYCLE 60

What I am trying to extract is the tool number (T0298 in
this case). So
basically I want everything on line 4 AFTER the : mark
for each record.

Is there a function or query I can use to extract this?
Please help. Thanks!

- joe


.

Les,

Thanks this is what I was working for. The only other thing is most but not
ALL of my tools numbers are five characters - some are longer, some are
shorter. How can I use the mid function to give me everything until the tool
number ends regardless of howlong or short it is? (Like until it sees a
space or something)

Thanks again!

- joe


"Les" wrote in message
...
Joe,
You can use the INSTR function to locate "TOOL #: "

The following line tells you the position that your string
starts at. Add 8 to get to the position where your tool
number starts.

fldstart:InStr(1, yourfield, "TOOL #: ", 1) + 8


Then, use MID function. It will extract from your field,
starting at position where tool number is located, for 5
positions. I guessed that your tool numbers were all the
same length.

exttoolnum:MID(yourfield, fldstart, 5)

I usually do this in a function that I call from my query,
but you should be able to do it in the query grid.


-----Original Message-----
I have a notes field on our database that has information
I need to extract
into seperate fields. Thenotes fields are formatted very
consistently, so
the information I need is on a certain line but I am not
sure how to get it
out. Here is an example of the data in each record:

DRY MATL. 3-4 HRS @ 220-240 DEGREES
************************************************* ******

TOOL #: T0298
MANUAL
CYCLE 60

What I am trying to extract is the tool number (T0298 in
this case). So
basically I want everything on line 4 AFTER the : mark
for each record.

Is there a function or query I can use to extract this?
Please help. Thanks!

- joe


.

There's probably a more elegant way to do this, but if the format is really consistent, it looks like you can do it with a few text manipulation functions, specifically, Mid and InStr.

If the data you want is always the 2nd through the 6th characters after the first ":" in the field (as it is below), you should be able to use the following in the "Field" line of your query:

Mid([notes],InStr([notes],":")+2,5)

The Mid function starts at a character number you specify and returns a specified number of characters from then on. The InStr bit finds the character number of the first ":" -- in this case it tells the Mid function where to start looking (2 characters after the colon).

If you have any fields with no ":", you'll get an error, and if you have any ":"s in a field before the one that precedes the tool #, you may have to get trickier. But hopefully that'll work.

Cheers,

Dave

"Joe Williams" wrote:

I have a notes field on our database that has information I need to extract
into seperate fields. Thenotes fields are formatted very consistently, so
the information I need is on a certain line but I am not sure how to get it
out. Here is an example of the data in each record:

DRY MATL. 3-4 HRS @ 220-240 DEGREES
************************************************** *****

TOOL #: T0298
MANUAL
CYCLE 60

What I am trying to extract is the tool number (T0298 in this case). So
basically I want everything on line 4 AFTER the : mark for each record.

Is there a function or query I can use to extract this? Please help. Thanks!

- joe

On Thu, 1 Jul 2004 09:37:47 -0400, "Joe Williams"
wrote:

I have a notes field on our database that has information I need to extract
into seperate fields. Thenotes fields are formatted very consistently, so
the information I need is on a certain line but I am not sure how to get it
out. Here is an example of the data in each record:

DRY MATL. 3-4 HRS @ 220-240 DEGREES
************************************************* ******

TOOL #: T0298
MANUAL
CYCLE 60

What I am trying to extract is the tool number (T0298 in this case). So
basically I want everything on line 4 AFTER the : mark for each record.

Is there a function or query I can use to extract this? Please help. Thanks!

- joe

This is a very good example of why it's a BAD IDEA to store discrete
items of information in a Memo field... :-{( Obviously you'ld only do
so because some other application is handing you this data as a large
indigestible bolus! My sympathy.

For this particular issue, let's say the memo field is named [notes].
To get the tool number use an expression:

Mid([Notes], InStr([Notes], "TOOL #:") + 8, InStr(InStr([Notes], "TOOL
#:"), [Notes], Chr(13)) - 1)

Air code, untested - the idea is to use the substringing function
Mid() to find the first occurance of the string "Tool #:", the next
carriage return Chr(13) character after it, and return the text in
between.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Your're going to find it very difficult to do this neatly in the query
grid - too many possibilities to sort through with IIF()s. I would
write a VBA function to do it - find the TOOL # as before, use Mid$ to
get a substring starting at the right place that is at least as long
as the longest possible value, use Instr() again to find the first
space or CR in it (depending on whether there is always a CRLF
immediately following the number, and then use Mid$ again on the
substring to "cut it down to size".

On Thu, 1 Jul 2004 11:33:27 -0400, "Joe Williams"
wrote:

Les,

Thanks this is what I was working for. The only other thing is most but not
ALL of my tools numbers are five characters - some are longer, some are
shorter. How can I use the mid function to give me everything until the tool
number ends regardless of howlong or short it is? (Like until it sees a
space or something)

Thanks again!

- joe


"Les" wrote in message
...
Joe,
You can use the INSTR function to locate "TOOL #: "

The following line tells you the position that your string
starts at. Add 8 to get to the position where your tool
number starts.

fldstart:InStr(1, yourfield, "TOOL #: ", 1) + 8


Then, use MID function. It will extract from your field,
starting at position where tool number is located, for 5
positions. I guessed that your tool numbers were all the
same length.

exttoolnum:MID(yourfield, fldstart, 5)

I usually do this in a function that I call from my query,
but you should be able to do it in the query grid.


-----Original Message-----
I have a notes field on our database that has information
I need to extract
into seperate fields. Thenotes fields are formatted very
consistently, so
the information I need is on a certain line but I am not
sure how to get it
out. Here is an example of the data in each record:

DRY MATL. 3-4 HRS @ 220-240 DEGREES
************************************************* ******

TOOL #: T0298
MANUAL
CYCLE 60

What I am trying to extract is the tool number (T0298 in
this case). So
basically I want everything on line 4 AFTER the : mark
for each record.

Is there a function or query I can use to extract this?
Please help. Thanks!

- joe


.




Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

On Thu, 1 Jul 2004 11:33:27 -0400, "Joe Williams"
wrote:

Les,

Thanks this is what I was working for. The only other thing is most but not
ALL of my tools numbers are five characters - some are longer, some are
shorter. How can I use the mid function to give me everything until the tool
number ends regardless of howlong or short it is? (Like until it sees a
space or something)

Yes: reposting my previous answer

Mid([Notes], InStr([Notes], "TOOL #:") + 8, InStr(InStr([Notes], "TOOL
#:"), [Notes], Chr(13)) - 1)

The second InStr finds the next carriage return character (the end of
the line).

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public