Seperate Notes/Memo field into seperate data fields

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
 
L

Les

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.
 
J

Joe Williams

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
 
J

John Vinson

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.
 
J

John Vinson

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