• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2696
  • Last Modified:

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.
0
crystal_Tech
Asked:
crystal_Tech
  • 9
  • 4
  • 2
1 Solution
 
mlmccCommented:
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
0
 
crystal_TechAuthor Commented:
CR 7.0
0
 
mlmccCommented:
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
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
crystal_TechAuthor Commented:
Thanks Mlmcc
Tried Your suggestion But

Same Error..
0
 
mlmccCommented:
I expected that but I wasnt sure.

As I recall yu either have to change the field in the database or create a view in the database that returns the left most 25 or 40 characters.

mlmcc
0
 
crystal_TechAuthor Commented:
can you please explain me more about
create a view in the database that returns the left most 25 or 40 characters.
0
 
crystal_TechAuthor Commented:
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.
0
 
mlmccCommented:
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
0
 
crystal_TechAuthor Commented:
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.
0
 
vastoCommented:
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 ?
0
 
crystal_TechAuthor Commented:
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 )
0
 
crystal_TechAuthor Commented:
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 />
0
 
vastoCommented:
The question was answered, why do you want to delete it ?
0
 
crystal_TechAuthor Commented:
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
0
 
crystal_TechAuthor Commented:
Thanks mlmcc
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 9
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now