parsing out dates from text

[Steve]

I have a customer tracking program (ADP), and it was originally designed
with teh customer notes as a "text" field. Then then customer notes woudl
be entered at various times, it would be entered by automatically adding a
lineedn cahracter, followed by the date, followed by the user name in
parentheses. However, the customer notes are not very useful for searching
or for filtering for various types of comments. A sample of a customer notes
data woudl look like this:

10/26/05 (Stephen): Fax Submitted to Amy regarding Shipment Approval
11/2/05 (julie):approved extension
1/24/06 (jchi): Faxed Submitted to Amy regarding COD Approval.
3/21/06 (anna): Fax Submitted to Amy regarding COD Approval
5/11/06 (Stephen): Fax Submitted to Amy regarding NSF
7/13/06 (jchi): Fax Submitted to Amy Smith regarding COD Approval
8/21/06 (jchi): Fax Submitted to Amy Smith regarding COD Approval
9/20/06 (jchi): Fax Submitted to Amy Smith regarding COD Approval
11/30/06 (Stephen): Received fax regarding ownership change. Changes made to
E. J Smith in the system
1/17/07 (jchi): Fax Submitted to Amy Smith regarding COD Approval
2/21/07 (jchi): Fax Submitted to Amy Smith regarding COD Approval
5/3/07 (jchi): Fax Submitted to Amy Smith regarding COD Approval

I am looking to change teh design of the customer notes section so that it
has a separate record for each entry. Effectively, each date would have
aseparate record. I am looking to be able to have a query or some VB code
that can me parse through the text of the customer notes and then create
separate those records.

Is there a way to write some code or use SQL to look at the text field and
find a date within the text, then find the next date, so it might do the
following:

Find DATE1, and FIND DATE2
Put DATE1 into FIELD1(i.e.note date) of new record
find name in parenthese between DATE1 and DATE2 and copy that into FIELD2
(i.e. username)
Put all other text up to (but not including DATE2) into FIELD3 (i.e. Notes)
of new record

If this possible? I guess I can write some VB script myself, but I don't
know how to tell the code to LOOK for a date string within the larger text
string.

help?

 

[scubadiver]

I think the best thing would be to use the left function and grab the first
8 characters.

expr1: left([notes],8)

then I think you may be able to put a date format function around that.

--
http://www.patriotsquestion911.com
http://www.911truth.org
http://www.wtc7.net
http://www.st911.org
http://www.journalof911studies.com
http://www.pilotsfor911truth.org
http://ae911truth.org

 

[John W. Vinson]

10/26/05 (Stephen): Fax Submitted to Amy regarding Shipment Approval

I am looking to change teh design of the customer notes section so that it
has a separate record for each entry. Effectively, each date would have
aseparate record. I am looking to be able to have a query or some VB code
that can me parse through the text of the customer notes and then create
separate those records.

Is there a way to write some code or use SQL to look at the text field and
find a date within the text, then find the next date, so it might do the
following:

Find DATE1, and FIND DATE2
Put DATE1 into FIELD1(i.e.note date) of new record
find name in parenthese between DATE1 and DATE2 and copy that into FIELD2
(i.e. username)
Put all other text up to (but not including DATE2) into FIELD3 (i.e. Notes)
of new record

If this possible? I guess I can write some VB script myself, but I don't
know how to tell the code to LOOK for a date string within the larger text
string.

I'm more familiar with VBA than VBScript so there may be some differences
here, but I expect they'll use the same functions. The problem will be with
records which DON'T fit the paradigm (and you can bet your bottom dollar there
will be such records!!!). Here's some sketchy air code:

Dim strMemo As String
Dim strLine As String
Dim strDate As String
<open a recordset rs on your table>
<load the memo field into strMemo>
Do Until strMemo = ""
' extract the first line
strLine = Left(strMemo, InStr(strMemo & vbCrLf, vbCrLf) - 1)
' trim off the first line
strMemo = Mid(strMemo, InStr(strMemo & vbCrLf, vbCrLf) + 2)
strDate = Left(strLine, InStr(strLine, " ") - 1)
If IsDate(strDate) Then
rs!Date1 = CDate(strDate)
Else
<handle the erroneous data>
End If
rs!Field2 = Mid(strLine, InStr(strLine, "(") + 1), InStr(strLine, ")") - 1)
rs!Notes = Trim(Mid(strLine, InStr(strLine, ")") + 1)


John W. Vinson [MVP]

 

[Steve]

John -

This was excellent! It gave me 80% of the background code. I then had to
write some custom exception code to handle all of the weird syntax that was
non standard. In the end, it was GREAT

Thank you.