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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
James0628Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.