Crystal Reports syntax

This question is related to the discussion here: -
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_26921083.html

I have an invoice that can be run at our Head office by linking directly to SQL data and entering a parameter (invoice number). It also runs on our EPOS tills.

I want to display line comments as a summary and used this formula: -

FormulaField1
if not IsNull ({TransLines.lMessage}) and {TransLines.lMessage} <> "" then
{TransLines.lMessage}
else
ChrW (255)

Formula Field2:
if length(Maximum ({TransLines.lMessage}))>2 then
if Minimum ({@FormulaField1}) <> Maximum ({TransLines.lMessage}) then
Minimum ({@FormulaField1})& "   " & Maximum ({TransLines.lMessage})
else
maximum({TransLines.lMessage})

This works great at head office but I get an error on the tills:
Error in formula 'if not IsNull ({TransLines.lMessage}) and {TransLines.lMessage} <> "" then '

A number, currency amount, boolean etc. expected here.

I believe the version of Crystal on the tills is older so that might be it?

When I check the formula using Crystal syntax I get no errors. If I change to basic syntax I get the error above.
HKFueyAsked:
Who is Participating?
 
James0628Connect With a Mentor Commented:
I just thought of something.  It doesn't seem like this would cause the error that you mentioned, but you could give it a try.

 I used ChrW in that first formula, but you can also use Chr (without the "W").  The function is listed as ChrW in the CR 10 Help, and there is no mention of Chr, but it works too.  My assumption has always been that the function name used to be Chr and they changed it to ChrW at some point, but still support Chr in CR 10 for backwards compatibility.

 The point of all of that is that, if I'm right about Chr being the old name, and the other systems are running an older version of CR, they may not recognize the ChrW function name.  Like I said, that doesn't really seem like it would cause the error that you mentioned, but you could try changing ChrW to Chr and see if that helps.

 If using Chr fixes the problem, you can remove the ToText functions that you added.  Actually, if the lMessage field is showing as text on the old systems, you can remove the ToText functions whether the change to Chr fixes the problem or not, since you're just using ToText on a text field, which doesn't accomplish anything (although it probably doesn't do any harm either).

 James
0
 
Alpesh PatelAssistant ConsultantCommented:
'if IsNull ({TransLines.lMessage})=false and Tostring({TransLines.lMessage}) <> "" then
0
 
HKFueyAuthor Commented:
Hi Patel,

I replaced Tostring with Totext, it works on mine but not on the Tills. (same error)
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
James0628Commented:
Crystal and Basic syntax are different.  A formula written in one syntax, won't work in the other.  That formula was written in Crystal syntax, which is why you get the error when you try to switch to Basic.  I have no idea if there are versions of CR that don't support the Crystal syntax.

 I wouldn't expect the version of CR to be an issue, unless it's a really old version.

 It seems like maybe TransLines.lMessage is not being seen as a string field.  Can you check the report on those systems?  You'd open the report in the CR designer and find TransLines.lMessage in the Field Explorer and see what type of field CR thinks it is.  If you don't see the type, you can right-click on "Database Fields" and select the "Show Field Type" option.  Or, you could put the TransLines.lMessage field on the report somewhere (and suppress the field or section if you like) and let the mouse hover over the field, and CR should tell you the field name and type.

 James
0
 
James0628Commented:
FWIW, for Patel's suggestion, you'd also have to add ToText to the field after the "then":

if not IsNull ({TransLines.lMessage}) and ToText ({TransLines.lMessage}) <> "" then
ToText ({TransLines.lMessage})
else
ChrW (255)


 The report might run with that, but that leaves the underlying question of why the field is a string on one system and not the others.  If it is some kind of "comment", it presumably should be a string.  A number or boolean value wouldn't make much of a comment.

  James
0
 
HKFueyAuthor Commented:
Hi James,

I will try your suggestion.

The field shows as string in both reports.

I have checked both SQL databases and the field is set as varchar(4000)

I can see Visual Studio .net 2003 installed on the tills I assume the Crystal version is part of that.
0
 
HKFueyAuthor Commented:
Hi James,

Still does not work, I'm going to put the question to our software providers.
0
 
mlmccCommented:
Is that the complete formula?

mlmcc
0
 
HKFueyAuthor Commented:
Hi ML,
Yes the formula in James's comment above is complete (it feed in to another formula).

Still waiting for our software providers to get back to me...
0
 
HKFueyAuthor Commented:
Hi James,

That works! thanks very much.

H
0
 
James0628Commented:
You're welcome.  Glad we were able to figure it out.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.