BLOB Field with vbNewLine Query Help

Hello,
I have a Access 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.
This MEMO Field is written to a Link Table in SQL 2000.
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 come up with a
query to return the last date?
- Somehow use RIGHT() ans go backwards to find the date?
- Somehow find the position of, in this case, the 3rd New Line Character
and then SELECT the next LEFT up to the end of the date ?
I've been dying over this for two days now. Can someone give me a
pointer. Thank you much.Answered in .programming. Please don't multi-post.
http://www.aspfaq.com/
(Reverse address to reply.)
"Jim Lou" <jim@.no_spam_no.com> wrote in message
news:MPG.1c165b5cff12dc6a9896a6@.msnews.microsoft.com...
> Hello,
> I have a Access 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.
> This MEMO Field is written to a Link Table in SQL 2000.
> 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 come up with a
> query to return the last date?
> - Somehow use RIGHT() ans go backwards to find the date?
> - Somehow find the position of, in this case, the 3rd New Line Character
> and then SELECT the next LEFT up to the end of the date ?
> I've been dying over this for two days now. Can someone give me a
> pointer. Thank you much.