Parsing Memo Fields

[Joe Williams]

How woluld I go about parsing a memo field in Access? I
have a memo field in our database that is used in the
following manner:


JOB INSTRUCTIONS
TOOL NUMBER: XXXXX
TOOL LOCATION: XXXXX
XX CAVITIES

What I am trying to do is extract the date (signified by
the Xs) from the memo field into three different fields.
The first line would be ignored as it contains no data,
then the next three lines have data but it might be of
different lengths plus you have to deal with the CR/LF
that are stored int he memo fields.

Can anyone help? Thanks

- joe

 

[Joe Williams]

-----Original Message-----
How woluld I go about parsing a memo field in Access? I
have a memo field in our database that is used in the
following manner:


JOB INSTRUCTIONS
TOOL NUMBER: XXXXX
TOOL LOCATION: XXXXX
XX CAVITIES

What I am trying to do is extract the date (signified by
the Xs) from the memo field into three different fields.
The first line would be ignored as it contains no data,
then the next three lines have data but it might be of
different lengths plus you have to deal with the CR/LF
that are stored int he memo fields.

Can anyone help? Thanks

- joe

.

 

[John Vinson]

How woluld I go about parsing a memo field in Access? I
have a memo field in our database that is used in the
following manner:


JOB INSTRUCTIONS
TOOL NUMBER: XXXXX
TOOL LOCATION: XXXXX
XX CAVITIES

So you're essentially duplicating the functionality of a relational
database by storing labels and values in a memo field? Why? Where is
this memo field coming from - an Import?

What I am trying to do is extract the date (signified by
the Xs) from the memo field into three different fields.
The first line would be ignored as it contains no data,
then the next three lines have data but it might be of
different lengths plus you have to deal with the CR/LF
that are stored int he memo fields.

Are the field values CONSISTANT? i.e. will the Tool Number sometimes
be the third line instead of the second? Will you sometimes have "TOOL
NO" or "TOOL #" or "TOOL NUBMER"? If you have to allow for such
variation you'll have a *VERY* hard time of it.

To do this you'll need some VBA code to pull the contents of the memo
field into a string variable and parse it. It might be marginally
possible with InStr() and Left() and Mid() functions, but these will
typically truncate a Memo field at 255 bytes so you may well lose
data.

 

[Albert D. Kallal]

If you are using the a2000 or latter, the "split" function can slice this up
like a hot knife through butter.

Here is a working solution. You data is assumed to be in sBuf


dim sData() as string

sData = Split(sBuf, vbCrLf)

Debug.Print "Tool num number = " & Split(sData(1), ": ")(1)
Debug.Print "Tool location = " & Split(sData(2), ": ")(1)
Debug.Print "Cavities = " & Split(sData(2), " ")(0)

The out put will be:

Tool num number = XXXXX
Tool location = XXXXX
Cavities = TOOL

 

[John Nurick]

Neat!

If you are using the a2000 or latter, the "split" function can slice this up
like a hot knife through butter.

Here is a working solution. You data is assumed to be in sBuf


dim sData() as string

sData = Split(sBuf, vbCrLf)

Debug.Print "Tool num number = " & Split(sData(1), ": ")(1)
Debug.Print "Tool location = " & Split(sData(2), ": ")(1)
Debug.Print "Cavities = " & Split(sData(2), " ")(0)

The out put will be:

Tool num number = XXXXX
Tool location = XXXXX
Cavities = TOOL

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[Joe Williams]

Thanks Albert, I seem to be on the right track with the
split function. One problem though: some of the notes
fields in the database don't have the structure as I have
described, i.e. they don't have as many lines. So when I
run the split function I get a "subscript out of range"
error for the records that don't have the information.
Now, I don't care about those records, but when I run the
query I get the error. Is there a way to tell the function
to skip these records or some other way to avoid this
error on records that don't conform to the format as I
have described it?

Thanks!

- joe

 

[Albert D. Kallal]

Gee my newsgread missed this!

yes, you can change the function:


sData = Split(sBuf, vbCrLf)
iMax = ubound(sdata,1)

if iMax >= 2 then
Debug.Print "Tool num number = " & Split(sData(1), ": ")(1)
end if

if iMax >= 3 then

etc..;