Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: One Date or Another Date Post Reply Post New Topic
Author Message
Sesonic
Newbie
Newbie


Joined: 03 May 2011
Online Status: Offline
Posts: 4
Quote Sesonic Replybullet Topic: One Date or Another Date
     Posted: 10 May 2011 at 5:08am
Hi, just wondering would someone be able to help me with the following:

 

I Have the following 2 formulas and am Using 1 Table {Schedules}

Crystal 8.5 and SQL Server
 

Formula 1 (@Moved)

if {SCHEDULES.MOVE_DTTM} in {?param1} to {?param2}

then ("Moved")

else ("Not Moved")

 

Formula 2 (@Cancelled)

if {SCHEDULES.CANCR_DTTM} in {?param1} to {?param2}

then ("Cancelled")

else ("Not Cancelled")

 

I am trying to return records that have appointments that are moved or cancelled using the ollowing statements in the record selection:

 

if ({@Moved} = ("Moved"))

then ({SCHEDULES.MOVE_DTTM} in {?Param1} to {?Param2})

else if ({@Cancelled} = ("Cancelled"))

then ({SCHEDULES.CANCR_DTTM} in {?Param1} to {?Param2})

 

When I run the report it is returning records that only match the first part of the if statement i.e.Moved it doesn't return records that are just cancelled.

 

Any ideas on this? Subreport Is out for various reasons

This is probably really obvious, any help would be much appreciated.
 
Thanks,
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 May 2011 at 5:21am
{SCHEDULES.MOVE_DTTM} in {?param1} to {?param2}
or
{SCHEDULES.CANCR_DTTM} in {?param1} to {?param2}
IP IP Logged
Sesonic
Newbie
Newbie


Joined: 03 May 2011
Online Status: Offline
Posts: 4
Quote Sesonic Replybullet Posted: 10 May 2011 at 5:35am
Hi
 
Thanks for the reply.
 
I initially tried what you suggested below but I have the same problem.
The OR evaluates only the first part of the equation and seems to ignore the second part.
 
When i run the report with the OR it will only bring back records that are Moved and it ignores the Records that are Cancelled.
 
If I swap the formula to:
 
{SCHEDULES.CANCR_DTTM} in {?param1} to {?param2}
or
{SCHEDULES.MOVE_DTTM} in {?param1} to {?param2}
 
It will then bring back the Cancelled records and it ignores the moved records.
 
I just tried in now again but am still having the same issue.
 
Any ideas?


Edited by Sesonic - 10 May 2011 at 5:36am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 May 2011 at 5:44am

you have nulls in here?

IP IP Logged
Sesonic
Newbie
Newbie


Joined: 03 May 2011
Online Status: Offline
Posts: 4
Quote Sesonic Replybullet Posted: 10 May 2011 at 6:13am
Originally posted by DBlank

you have nulls in here?

 
Hi,
Either field could be null depending on whether record has been moved or cancelled. If it's moved the date it was moved fills in to {SCHEDULES.MOVE_DTTM} and if it cancelled the date it was Cancelled fills in to {SCHEDULES.CANCR_DTTM}. If neither, the fields remain as null.
 
Do the Nulls have an impact?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 May 2011 at 6:17am
I beleive the nulls are your issue. the formula dies on the nulls instead of moving to the ELSE portion
 
maybe...
 
(not (isnull({SCHEDULES.CANCR_DTTM})) and {SCHEDULES.CANCR_DTTM} in {?param1} to {?param2})
or
(not (isnull({SCHEDULES.MOVE_DTTM})) and {SCHEDULES.MOVE_DTTM} in {?param1} to {?param2})


Edited by DBlank - 10 May 2011 at 8:01am
IP IP Logged
Sesonic
Newbie
Newbie


Joined: 03 May 2011
Online Status: Offline
Posts: 4
Quote Sesonic Replybullet Posted: 11 May 2011 at 5:27am
Hi,
 
Tried you formula today and it works perfectly.
Wasn't aware that nulls could have an effect on the formula.
 
Many Thanks for you help.
IP IP Logged
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