Error in If Statement with Boolean values in Crystal Report -

Hello,

I received help with my if statement in Crystal Reports:

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_28227399.html

I am trying to display my check boxes in a Crystal Report.

If {table.IsHospital} = true then
     formula = Chr(254) 
Else
      formula = Chr (168)
End if

Open in new window


I am not certain if I need to declare my field as a Boolean value, or is that automatic in Crystal because of the field properties?

I am getting error when I test the formulas:

=true    produces: must be a number
=1        produces: needs to be a Boolean Value
=Yes     Produces: must be a number

I believe my datatype field is logical

list in tables as:   (udtlogical(bit),null)

Thanks for any help.

Brent
bvanscoy678Asked:
Who is Participating?
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.

mlmccCommented:
What does Crystal show the field as?

mlmcc
0
bvanscoy678Author Commented:
When I check the format field, it is asking me for Boolean Text and it is set to Yes / No

I'll try to figure out how to exactly tell what field it sees it as.

thanks
0
bvanscoy678Author Commented:
Yes, when I hoover over the fields in my Crystal Report, it shows (Boolean) after the field name.
0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

James0628Commented:
I don't use Basic syntax, but if the field is logical, the formula seems OK to me, and a similar formula passes a Basic syntax check here.

 You could try leaving out the test.  {table.IsHospital} by itself is the logical equivalent of ({table.IsHospital} = true).


If {table.IsHospital} then
     formula = Chr(254)
Else
      formula = Chr (168)
End if


 James
0
bvanscoy678Author Commented:
James,

I just ran your modified formula with Crystal Syntax are received the following error:

It highlights formula=

and says: a number, currency amount, Boolean, date, date=time or string is expected here

Thanks
0
bvanscoy678Author Commented:
I found in the help section what I suspected is that one of the formats is not correct. It's trying to compare apples to apples, but that's not what I have. I will have to go back to square one and figure out which data type is incorrect.
0
mlmccCommented:
If you want to use Crystal syntax

If {table.IsHospital} then
      Chr(254)
Else
       Chr (168)

mlmcc
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
bvanscoy678Author Commented:
Either way, I keep getting basically the same error, which is the data type is not matching up. I can see my data fields are Boolean, but not sure why Crystal doesn't recognize it. I am sure I am doing something incorrect, but it just might take a little more reading to figure it out.

I probably should pull the question until after the long holiday, but not sure how to handle it.

Thanks for looking at this and I'll check back as soon as I can figure some additional details.

Brent
0
James0628Commented:
The formula in your first post is Basic syntax, so I used the same thing.  Did you try that formula with CR set to use Basic syntax?  You can't really get an error about the data types not matching with that formula, because it's not comparing the field to anything.  It's just testing the field itself to see if it is True or False.

 James
0
bvanscoy678Author Commented:
James,

I thought I had tried using both ways and still received an error, but I will double check first chance I get back at work
This might not make any sense but, "What if it is testing the field for true or false, but the field isn't true or false? Maybe Null or possibly a a different value like a number or text? I see the values, so that is not the case, just trying to understand. Thanks.
0
mlmccCommented:
Canyou upload the rpt file?

mlmcc
0
bvanscoy678Author Commented:
Yes, I can tomorrow. I'll go by the office first chance I get.

Thanks.
0
James0628Commented:
If the field contains a null, that doesn't change the data type for the field in CR.

 Nulls _can_ affect formulas, so if the field could be null, you should check for that, or use an option in CR to have it convert nulls to the "default" value (I'm guessing that that's False for a Boolean, but I could be wrong).  You can use the IsNull function to test a field to see if it's null, and you need to do that _before_ you do anything else with that field in that formula.

 You said that CR is telling you that the field is a Boolean, in which case it presumably can't contain anything other than a true/false value, or null.  If the field is somehow defined as a Boolean, but still contains non-Boolean values (apart from null), then I'm not sure how you would handle that.

 James
0
bvanscoy678Author Commented:
Good morning,

Here is the CR report I am trying to figure out. I did not create it, but I am trying to figure it out while my boss in on vacation. He normally works with CR, but I am just starting to learn.  I understand VB, but not much about Crystal syntax.

Thanks for all the help.
Brent
S-Advanced-EMS-STEMI-Report.rpt
0
mlmccCommented:
Change to use Basic Syntax and it works just fine

mlmcc
0
bvanscoy678Author Commented:
That's weird, when I try it at work, I get an error code. Can you upload the report as you have it working, then I can take that report to work and test it. I'm not familiar enough with Crystal to know if there are settings on my work machine, that I need to adjust.

Thanks! I'll test it tomorrow and post back.
0
mlmccCommented:
There are no settings to adjust that I know of that affect formulas

mlmcc
S-Advanced-EMS-STEMI-Report-1-.rpt
0
bvanscoy678Author Commented:
Thanks. I'll look at it tomorrow, then award the points.
0
bvanscoy678Author Commented:
I think I might have figured out my problem. I went to work for a little bit and I notice something I did not see before. When I look at your CR, I see that you have a different "Box" (not sure what it is called maybe field?) for your formula check. On the attached screen shot the formula field you created shows wingdings (I changed to display the CHR correctly). I was attempting to put my formula into the YES box (field) right next to the EKG Transmitted: Yes

Does my formula go into a new field all by itself? or would it go into the Yes area.

I hope this makes sense, because I am not doing well with the terminology yet.

When I ran your formula at work, I did not get the error. I copied into my "Yes" box and I get an error. That might be my problem, I am putting the formula in the wrong area.

I'll do some reading.

*** After some reading, I wonder if what I want to do is Conditional Formatting?

Thanks! Brent
example-for-field.JPG
0
aikimarkCommented:
@bvanscoy678

Are you driving this from a VB6 or VB.Net application?

aikimark -- zone advisor
0
mlmccCommented:
The formula is in the report.  It is not affected by the application calling the report

mlmcc
0
bvanscoy678Author Commented:
James,

I missed your earlier comment about nulls. In the record I am testing, there is a value. But, your point is well made and I'll look at the default values tomorrow. I know that Nulls are allowed as part of the table properties, so it will be something I'll need to address once I get it working. The more I read, the more I understand it. This is my first interaction with Crystal and formulas, so I feel a bit out of place. Both answers for the  formulas are correct, so I will award point tomorrow. I just want to finish the thread for future queries on the subject.

Thank you both for your patience and help. Brent
0
bvanscoy678Author Commented:
After I loaded the formula into the original report, it works just fine!

Thanks for all the help and I'll post points.

Thanks,
Brent
0
bvanscoy678Author Commented:
Thanks for the help guys!

Brent
0
James0628Commented:
You're welcome.  I'm glad you were finally able to get it working.

 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.