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.

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.c om...
> 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.

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.

blob field or memo field cant be used in formula field

I have a field with more then 250 size characters.When i add the field as Data base field , i can add and see the values.But i want to apply formula on the field to match the value of the field with other table field.
When i do so , i am getting "blob field or memo field can't be used in formula".

can any one suggest me a way to convert the memo field into text /string so i can compare with other field.
i tried with if (mid ({table.field),1,125} = table2.field)
but i am still getting the errorWithin the Crystal Reports formula editor, blog and image fields are not available. If you need to manipulate a text field that is more than 255 characters in length, or use the value in a large text field within a formula, you will need to do this outside of Crystal.
I suggest creating a stored procedure in your DB that returns all the field and/or portions of fields (substrings) you need for your report. Any parameters used by the report should also be included in the procedure. You might want to use the query Crystal has created for you existing report as a guide for created the Stored Proc.
Then create a report using the stored procedure as the data source. If stored procedures are not available as a data source, check your database options tab from the Crystal file menu->options