Parsing Memo for date problem

[Guest]

I have a memo field named "CommentRaw". Examples of memo data:

EXAMPLE1
Can keep strobel construction. But o/s filler change to EVA diecut, last
has minor revision on lateral toe area to improve fit. Need remake sample.
-- Sample Date: 12/29/2006--Basic Status: Rejected

EXAMPLE2
Change construction to be board lasted. Need to remake basic fit sample. --
Sample Date: 12/29/2006--Extreme Status: Rejected

EXAMPLE3
Fit on Jack, ok. -- Sample Date: 12/29/2006--Basic Status: Approved


The CommentsRaw field always ends with
"-- Sample Date" nn/nn/nnnn--Basic Status: nnnnnnnn" or
"-- Sample Date" nn/nn/nnnn--Extreme Status: nnnnnnnn", where n is variable.

I am trying to extract the sample date to a new field named "SampleDate" in
an update query. I have "SampleDate" updating to:

IIf([CommentRaw] Like "*-- Sample Date: ##/##/####--Basic Status:
*",CDate(Mid([CommentRaw],InStr([CommentRaw],"-- Sample Date:
")+17,InStr([CommentRaw],"--Basic Status:")-InStr([CommentRaw],"-- Sample
Date: ")-17)),IIf([CommentRaw] Like "*-- Sample Date: ##/##/####--Extreme
Status: *",CDate(Mid([CommentRaw],InStr([CommentRaw],"-- Sample Date:
")+17,InStr([CommentRaw],"--Extreme Status: ")-InStr([CommentRaw],"-- Sample
Date: ")-17)),Null))


All works fine if date is single-digit month. Those with double-digit
months are not updated. Can you see why?

thanks so much.

 

[Allen Browne]

Okay, the reason it is difficult to get at this data is that the field is
not atomic, i.e. you are storing several things (comment, date, status, ...)
in the one field, and possibly several different comments that are supposed
to be made up of these things in the one field.

You could solve the problem by creating a related table to handle the
comments. This table will have fields like this:
WidgetID relates to the primary key of whatever table the
comment is for
Comment Memo
CommentDate Date/Time field
StatusID relates to a table of status codes.

Now it is really simple to do thing such as:
- Find all the comments for a widget.
- Sort them in date order.
- Find the comments where the latest status is "pending" so they need
further action
and so on.

 

[Guest]

Allen,

thank you much for your reply. I would LOVE to be able to do that.
Actually, that is exactly what I am attempting to do with this query. It is
indeed obvious to me that the table that it comes from is poorly constructed.
Unfortunately, this comment field data comes to me as a text data file,
downloaded from a "professional" sql database. this slap-dash construction
was a product of our strained IT dept and a "band-aid" approach to a
"need-it-now" request from somebody in our department.

So, this is what I have to work with for now. Do you have any other
suggestions for me?

Thanks again.

 

[Allen Browne]

Fair enough. First question would be whether the IT dept can provide it in a
better format: at least so that each comment is a single line of text. If it
comes from a relational database, it may be able to get it in every better
shape than that.

An alternative might be to Open the file for input. Input each line into a
string, looping until EOF. Parse the string, and append records to your
table, so you have the suggested structure, and you can query it. One you
have written this routine, you could assign it to the Click event of a
button so it's quick to import another file.

It may even be possible to use Split() to parse the monster comments string
at the right places into an array, though it's less obvious how the records
would be separated.

 

[John Spencer]

-- Sample Date: 12/29/2006--

Chop CommentRaw down to just the portion after "-- Sample Date: "
Mid(CommentRaw, Instr(1,CommentRaw,"-- Sample Date: ") + 16)

Get the portion of the above (x) that is before the "--"
Left(x,Instr(1,x,"--")-2)

Expanded that is
Left(Mid(CommentRaw, Instr(1,CommentRaw,"-- Sample Date: ") + 16)
, Instr(1,Mid(CommentRaw, Instr(1,CommentRaw,"-- Sample Date: ") + 16)
,"--")-2)

Convert that to a date
DateValue(Left( Mid(CommentRaw, Instr(1,CommentRaw,"-- Sample Date: ") + 16)
, Instr(1, Mid(CommentRaw, Instr(1,CommentRaw,"-- Sample Date: ") + 16)
,"--")-2))

Use a where clause to filter the data
WHERE CommentRaw Like "* -- Sample Date: #*/#*/####--*"

I think we need John Nurick and his Regex scripts to do a better job.
Quoting from a post of his
John Nurick:

For a while now I've been using the VBScript regular expression engine from
VBA for pattern matching that is impossible with Like and tedious with the
native string handling functions.

Recently I've built a stand-alone general purpose "data validation" function
which seems simple enough and sturdy enough for wider use, and Arvin has now
put it on the Access Web at
http://www.mvps.org/access/modules/mdl0063.htm

It's mainly intended for validating data entry (e.g. nasty things like UK
postcodes) but also works happily, if slowly, in queries and when iterating
through recordsets. It's generic VBA so will work in Word and Excel too.

From: John Nurick <[email protected]>

I tend to use regular expressions for this sort of parsing: it saves an
awful lot of fiddling with InStr() and Mid() and so on. If you use the
rgxExtract() function that I recently posted at
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm ,

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a memo field named "CommentRaw". Examples of memo data:

EXAMPLE1
Can keep strobel construction. But o/s filler change to EVA diecut, last
has minor revision on lateral toe area to improve fit. Need remake sample.
-- Sample Date: 12/29/2006--Basic Status: Rejected

EXAMPLE2
Change construction to be board lasted. Need to remake basic fit
ample. --
Sample Date: 12/29/2006--Extreme Status: Rejected

EXAMPLE3
Fit on Jack, ok. -- Sample Date: 12/29/2006--Basic Status: Approved


The CommentsRaw field always ends with
"-- Sample Date" nn/nn/nnnn--Basic Status: nnnnnnnn" or
"-- Sample Date" nn/nn/nnnn--Extreme Status: nnnnnnnn", where n is
variable.

I am trying to extract the sample date to a new field named "SampleDate"
in
an update query. I have "SampleDate" updating to:

IIf([CommentRaw] Like "*-- Sample Date: ##/##/####--Basic Status:
*",CDate(Mid([CommentRaw],InStr([CommentRaw],"-- Sample Date:
")+17,InStr([CommentRaw],"--Basic Status:")-InStr([CommentRaw],"-- Sample
Date: ")-17)),IIf([CommentRaw] Like "*-- Sample Date: ##/##/####--Extreme
Status: *",CDate(Mid([CommentRaw],InStr([CommentRaw],"-- Sample Date:
")+17,InStr([CommentRaw],"--Extreme Status: ")-InStr([CommentRaw],"--
Sample
Date: ")-17)),Null))


All works fine if date is single-digit month. Those with double-digit
months are not updated. Can you see why?

thanks so much.

 

[Guest]

Allen, are you saying there's no easier way to modify what I already have?
The query does do the job for dates with single-digit months. It seems
there's got to be a logical way to get it to work with double-digit months,
no?

 

[Guest]

Hi John,

Thank you for your response. When I use this, it gives me "invalid
procedure call". Also, is there a reason why you changed +17 to +16? I will
go ahead and check out the links you gave me.
Thanks.

 

[Allen Browne]

It might be possible to use an OR criteria that handles the single-digit
months too.

 

[John Spencer]

Since trying to do this as a calcuated field is subject to all types of
errors. I would use a vba function that looks like the one below to extract
the value.

Field: fGetDate([CommentRaw]]

Paste the following function into a vba Module and save it (module cannot be
named the same as the function)

'========== Code Starts ============
Public Function fGetDate(strMemo)
Dim vReturn As Variant

If Len(strMemo & "") = 0 Then
vReturn = Null

ElseIf strMemo Like "*-- Sample Date: *--*" Then

vReturn = Mid(strMemo, (InStr(1, strMemo, "-- Sample Date: ") + 16))
vReturn = Left(vReturn, InStr(1, vReturn, "--") - 1)
If IsDate(vReturn) Then
vReturn = DateValue(vReturn)
Else
vReturn = Null
End If

Else
vReturn = Null
End If

fGetDate = vReturn

End Function

'========== Code Ends ============

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..