asked on

Error in formula <Record Selection> A Memo field cannot be used in a formula

Reporting Tools : Crystal Report 7.0
FrontEnd : Visual Basic 6.0 (Classic)
BackEnd : SQL Server 2008 R2

Hi i am trying to print from vb using following query.

CrystalReport1.SelectionFormula = "{CustDtl.RawID} = '" & Text1(0).Text & "' and {CustDtl.Name} = '" & Text1(3).Text & "'"

Open in new window

But getting Run Time error 20515
Error in file C:\Cust.RPT: Error in formula <Record Selection> '{CustDtl.RawID} ='1' and {CustDtl.Name} = 'CRYSTAL" A Memo field cannot be used in a formula

Both field are memo field ..
and problem is i can not change field type from nvarchar to varchar. because clients are dumping records into this SQL table.

how can i get result so i can print.

Getting result into msflexgrid from vb but Can not print.

Mike McCracken

NVarChar is not the problem. NVarChar can handle Unicode text but VarChar can't.

The issue is length of the field. If the RawId field is numeric text you probably can shorten it to a length of 10. Name can probably be shortened to 30.

What version of Crystal?

mlmcc

Altaf Patni

ASKER

CR 7.0

Mike McCracken

CR7 limits text fields to 255 characters and cannot test a memo field except for IsNull

If you can't change the database you might try Left({CustDtl.Name},25)

Since it is CR7, the issue might be NVarChar. CR7 doesn't support Unicode.

mlmcc

Altaf Patni

ASKER

Thanks Mlmcc
Tried Your suggestion But

Same Error..

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Altaf Patni

ASKER

can you please explain me more about
create a view in the database that returns the left most 25 or 40 characters.

Altaf Patni

ASKER

Thanks for helping me.. :-)

you mean to create stored procedure in SQL
and then filter its detail from vb..?

If yes.. then Please show me how to create stored procedure in SQL Server.

Mike McCracken

If you don't know how, you need to get with the DBA and have him build it.
You don't need a stored procedure just a view/query

mlmcc

Altaf Patni

ASKER

i have created a query
select RawID, Name from CustTrasact.dbo.Customer group by RawID, Name

and saved this query as
PrintCust.sql

Now what should i do.

vasto

to create a view run this script in SSMS

CREATE VIEW [dbo].[vCustomer]
AS
SELECT RawID,[Name]
FROM (SELECT CONVERT(nvarchar(50),RawID) AS RawID, CONVERT(nvarchar(50),[Name]) AS [Name] FROM CustTrasact.dbo.Customer) v 
GROUP BY RawID, Name 
GO

Open in new window

Then go to your crystal reports and replace the table Customer with the newly created view vCustomer

A stored procedure will have a better performance , but you will need to rewrite the report fom scratch - do you want to do that ?

Altaf Patni

ASKER

view query is working but,
but i want better performance, so i want to go with Stored Procedure..
Please Explain me step by step..
( i am ready to rewrite all my reports from scratch, after all i want better performance )

Altaf Patni

ASKER

I've requested that this question be deleted for the following reason:

i have learned and created stored procedure.. and its working.. but it took so long..<br />Project date expired.. <br />

vasto

The question was answered, why do you want to delete it ?

Altaf Patni

ASKER

Oh yes yes.....
I am asking multiple question within single question.
( Because of same module and parallel functionality)
i was just monitoring on STORED PROCEDURE.
apologize for that... i supposed to accept answer.

my main question was
"A Memo field cannot be used in a formula"
and i got answer by mlmcc Posted on 2012-09-02 at 10:57:34ID: 38359095

Altaf Patni

ASKER

Thanks mlmcc

Error in formula &lt;Record Selection&gt; A Memo field cannot be used in a formula

Error in formula <Record Selection> A Memo field cannot be used in a formula