MEMO with vbNewLine Query Help

[Jim Lou]

Hello,
I have a form that writes a record when anything is changed. This record
is written to a MEMO field with a vbCrLf after each one. This is done so
when the form is opened, the text box will have each change written as a
New Line.

The data is written like so:
Forms!OrderHeader!Changes = Forms!OrderHeader!Changes & vbCrLf & _
Format(Date) & " Agency Disc. was changed from " & Adisc.OldValue

If I look at the Data in the table, it returns like so:
________________________________________________________
| Field 1 |Field 2 |
|______________________________________________|________|
11/19/1998 CiC No. was changed from 54241 | 53 |
9/19/1998 CiC No. was changed from 542418 | |
11/28/2004 Line ID 4 Ad ID was changed from 26 | |
11/29/2004 Agency Disc. was changed from 5 | |
_______________________________________________|________|

The above is simply executing: SELECT [Field 1] FROM tblTest WHERE
[Field 2]=53


I've been asked to create a report based on the LAST change date. In
this case 11/29/2004 (Line 4 in Field 1). My question is: what's the
best way to go through this 197, Line Break blob and return the last
date?
- Somehow use RIGHT() and INSTR to determine the Last Date?
- Somehow find the position of, in this case, the 3rd New Line Character
and then SELECT the next LEFT up to the INSTR " " ?

I've been dying over this for two days now. Can someone give me a
pointer. Thank you much.

 

[Mark Phillipson]

Hi,

Once you have the whole memo field stored in a string variable (strMemo)
loop through it like this:

Dim strTempDate As String
Dim dteLastDate As Date
Dim dteTempDate As Date
Dim intCRLFPosn As Integer
Dim strCurrentLine As String
Dim strTemp As String
Dim strMemo As String

strTemp = strMemo & vbCrLf & "Dummy Line"
Do 'Start a Loop
intCRLFPosn = InStr(strTemp, vbCrLf) 'Store position of first CRLF
characters
strTempDate = Trim(Left(strTemp, 12))
If IsDate(strTempDate) Then dteTempDate = CDate(strTempDate)
If dteTempDate > dteLastDate Then 'Store Last Date
dteLastDate = dteTempDate
strCurrentLine = Left(strTemp, intCRLFPosn - 2)
End If

strTemp = Mid(strTemp, intCRLFPosn + 2) 'Chop off the line just
processed
Loop Until InStr(strTemp, vbCrLf) = 0
'Return results
MsgBox "Last Date is " & dteLastDate & vbCrLf & "The full line is " &
strCurrentLine

(It way well of been better to store the date in a column of it's own in the
first place)

--
HTH

Mark Phillipson

Free Add-Ins at; http://mphillipson.users.btopenworld.com/