Seperate Notes/Memo field into seperate data fields

  • Thread starter Thread starter Joe Williams
  • Start date Start date
J

Joe Williams

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.
 
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
 
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
Click to expand...

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.
 
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 <CR><LF>
immediately following the number, and then use Mid$ again on the
substring to "cut it down to size".

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
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
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)
Click to expand...

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).
 
Back
Top