MEMO with vbNewLine Query Help

  • Thread starter Thread starter Jim Lou
  • Start date Start date
J

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.
 
Looking at your data layout in the memo field the first
thing that strikes me is that the date field in each record
is effectively space terminated. So to begin with I'd try
using InStr() to get the first (from left to right) space
then everything to the left is date...

varDateEnd = InStr([Field 1]," ") ' This should tell where
the date ends

strDate = Left$([Field 1],varDateEnd) ' This should get the
date into strDate.

I can't remember whether you might need to subtract one from
varDateEnd to get it exactly right.

You could put the above in a function (change [Field 1] to
some other name), pass it the value of [Field 1]. Use the
function in a SELECT.

Is that what you were thinking about?

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/
Download Free Copy
----

Jim said:
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 |
______________________________________________|________|
Click to expand...
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.
Click to expand...
 
Back
Top