Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Syntax for IF statement Post Reply Post New Topic
Author Message
MikeC
Newbie
Newbie


Joined: 10 Jun 2008
Online Status: Offline
Posts: 17
Quote MikeC Replybullet Topic: Syntax for IF statement
    Posted: 16 Jun 2008 at 12:40pm
Hi,
I am brand new to Crystal so please bear with me :-)
 
I need to calculate and display an hourly longevity amount based on an employee's years of service.  I'm connecting to a SQL view called AAIASALRPT.  Among the fields in the view are one called adj_service_date (which is the date the employee started) and a field called hourtype (which may contain one of 4 values: lls, lr, coor or null).  Each employee has an adj_service_date and a value or null in the hourtype field.
 
The longevity is based on the number of years of service (see below in the code) and will vary depending on which hourtype and number of years the employee has.
 
Here is the code that I've been trying to make work but obviously it doesn't.  With this code I get the message "The ) is missing".
 
Any help with this code or pointers on what I should do to display the correct value for years of service (yos) would be greatly appreciated.
 
local NumberVar yos;
    yos := truncate ((datediff("d",{AAIASALRPT.adj_service_date},currentdate)) / 365);

if {AAIASALRPT.HOURTYPE} = 'lls'
    then   
(if (yos >=5) and (yos <10)
    then (.10)    
if (yos >=10) and (yos <15)
    then (.15)    
if (yos >=15) and (yos <20)
    then (.20)    
if (yos >=20)
    then (.25))
else if {AAIASALRPT.HOURTYPE} = 'lr'
   then   
(if (yos >=5) and (yos <10)
    then (.50)    
if (yos >=10) and (yos <15)
    then (..55)    
if (yos >=15) and (yos <20)
    then (.60)    
if (yos >=20)
    then (.65))
else if {AAIASALRPT.HOURTYPE} = 'coor'
   then   
(if (yos >=5) and (yos <10)
    then (.75)    
if (yos >=10) and (yos <15)
    then (.80)    
if (yos >=15) and (yos <20)
    then (.85)    
if (yos >=20)
    then (.90))

else (0.00);

IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 16 Jun 2008 at 1:36pm
I assume this is a copy/paste error, but in the 'lr' test you have (..55) as a return value. There are too many decimals there. Also, you need semi-colons after many of those If conditions. You can't put statements one right after the other without separating them with semi-colons. I would bet that this fixes it.

Secondly, as another tip, what I do in these situations is take a complex formula and break it up into simple formulas. Copy the whole thing to Notepad and then copy/paste individual sections into CR and check the syntax. This makes it easy to isolate which section is causing the problem and fix it.

If you're new to CR, you might want to check out my Encyclopedia book. I have three chapters covering formulas and sample code for how they work. You can find out more about my books at Amazon.com or reading the Crystal Reports eBooks online.

Edited by BrianBischof - 16 Jun 2008 at 1:38pm
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
MikeC
Newbie
Newbie


Joined: 10 Jun 2008
Online Status: Offline
Posts: 17
Quote MikeC Replybullet Posted: 16 Jun 2008 at 2:19pm
I actually have the book-it was the first reference that I purchased.
 
When I add the semi colons it halts at the line
 
else if {AAIASALRPT.HOURTYPE} = 'lr'
 
with the error message "The remaining text does not appear to be part of the formula".  Is there a way that I can tell crystal that it is a part of the formula and to continue to evaluate it?
 
Thanks for your help!
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 16 Jun 2008 at 2:44pm
Thanks for buying the book! 

I'm guessing that you put a semi-colon after the parentheses right before the 'else'. Is that true? If so, you don't want it there b/c then it stops the line prior to else being tested. You want to put them between the individual If statements.

Can you post the three lines of code leading up to (and including) the line with the error? I need to see how the semi-colons are placed.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
MikeC
Newbie
Newbie


Joined: 10 Jun 2008
Online Status: Offline
Posts: 17
Quote MikeC Replybullet Posted: 17 Jun 2008 at 7:19am

This passes the formula checker test with no errors, however it does not seem to calculate everything.

I should have around 26 employees that should show a number under the 'lr' hourtype, and 6 that should show a number for the 'lls' hourtype.  However, the only results I get are for the hourtype 'lr' and the yos variable is >20 (it shows .65 as it should).  Everything else shows up as 0.00.
 
Any ideas as to what else I've done wrong?
 
Thanks for your help!
 

local NumberVar yos;

    yos := truncate ((datediff("d",{AAIASALRPT.adj_service_date},currentdate)) / 365);

 

if {AAIASALRPT.HOURTYPE} = 'lls'

    then   

(if (yos >=5) and (yos <10)

    then (.10);    

if (yos >=10) and (yos <15)

    then (.15);    

if (yos >=15) and (yos <20)

    then (.20);    

if (yos >=20)

    then (.25))

 

else if {AAIASALRPT.HOURTYPE} = 'lr'

   then   

(if (yos >=5) and (yos <10)

    then (.50);    

if (yos >=10) and (yos <15)

    then (.55);    

if (yos >=15) and (yos <20)

    then (.60);    

if (yos >=20)

    then (.65))

 

else if {AAIASALRPT.HOURTYPE} = 'coor'

   then   

(if (yos >=5) and (yos <10)

    then (.75);    

if (yos >=10) and (yos <15)

    then (.80);    

if (yos >=15) and (yos <20)

    then (.85);    

if (yos >=20)

    then (.90))

 

else (0.00);

IP IP Logged
MikeC
Newbie
Newbie


Joined: 10 Jun 2008
Online Status: Offline
Posts: 17
Quote MikeC Replybullet Posted: 17 Jun 2008 at 2:09pm
Resolved:  I placed each "section" into its own formula and placed the variable into its own formula, then called them in an if/else formula I placed on the report.  Works perfect.
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 17 Jun 2008 at 2:51pm
Good job on getting creative.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.