Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Date Parameter Post Reply Post New Topic
Author Message
jdw1130
Newbie
Newbie


Joined: 20 Dec 2012
Online Status: Offline
Posts: 3
Quote jdw1130 Replybullet Topic: Date Parameter
     Posted: 20 Dec 2012 at 4:45am
I would like to have a user only enter one date parameter to look up multiple fields. Currently they have to enter a new parameter for each field. this is the formula i use for each field using different field names. Is there a way to enter a range and if any field matches that criteria it would show on the report?
 
I have six fields i would like to query.
 
{CUST_ContactsTable1_114806.CUST_Dog1BatteryPlanStartDate_103625984}>= {?Start Date} and {CUST_ContactsTable1_114806.CUST_Dog1BatteryPlanStartDate_103625984}<={?End Date
IP IP Logged
DaBoujibo
Newbie
Newbie


Joined: 21 Feb 2012
Online Status: Offline
Posts: 28
Quote DaBoujibo Replybullet Posted: 20 Dec 2012 at 6:00am
Hello.  Here is a sample.  With this type of formula, I only enter in my dates once even though I'm using the dates entered for multiple fields.  I think this is what you are asking at any rate.  I only enter in the start and end date once, but CR will use it for each field I identify.
 
fields:
{datefield1}
{datefield2}
{datefield3}
{datefield4}
{datefield5}
{datefield6}
 
Parameter Fields:
{?Start Date}
{?End Date}
 
Selection Criteria:
({datefield1} >= {?Start Date} AND
{datefield1} <= {?End Date})
OR
({datefield2} >= {?Start Date} AND
{datefield2} <= {?End Date})
OR
({datefield3} >= {?Start Date} AND
{datefield3} <= {?End Date})
OR
({datefield4} >= {?Start Date} AND
{datefield4} <= {?End Date})
OR
({datefield5} >= {?Start Date} AND
{datefield5} <= {?End Date})
OR
({datefield6} >= {?Start Date} AND
{datefield6} <= {?End Date})
 
IP IP Logged
jdw1130
Newbie
Newbie


Joined: 20 Dec 2012
Online Status: Offline
Posts: 3
Quote jdw1130 Replybullet Posted: 20 Dec 2012 at 7:11am
I used this formula to test just 2 fields.  The problem I have is that if {field one} does not meet the parameter it will not show {field 2} if it meets the parameter. 
({CUST_ContactsTable1_114806.CUST_Dog1BatteryPlanStartDate_103625984}>={?Start Date}
and{CUST_ContactsTable1_114806.CUST_Dog1BatteryPlanStartDate_103625984}<={?End Date})OR
({CUST_ContactsTable1_114806.CUST_Dog2Batteryplanstartdate_070133812}>={?Start Date}
and{CUST_ContactsTable1_114806.CUST_Dog2Batteryplanstartdate_070133812}<={?End Date})
IP IP Logged
DaBoujibo
Newbie
Newbie


Joined: 21 Feb 2012
Online Status: Offline
Posts: 28
Quote DaBoujibo Replybullet Posted: 20 Dec 2012 at 7:20am
From my experience when this would happen, I would pull all dates into a report just to see what start and end dates CR was seeing.  I found that the dates were displaying differently than expected in that situation and had to fix the formula accordingly.  (We have a database system that has dates as text fields in either 1, 3 or 4 different fields and can be 5 to 8 characters long! Wacko )  The formula has worked for me in the past except when the data in the field was not matching the data entered.  Try that analysis first as your syntax appears correct.
IP IP Logged
jdw1130
Newbie
Newbie


Joined: 20 Dec 2012
Online Status: Offline
Posts: 3
Quote jdw1130 Replybullet Posted: 22 Dec 2012 at 11:42am
I ran the report with out the date range parameter and all fields display correctly. When I apply the formula the"or" part does not work. The date 1 field always has to be in range or it will not display the other fields that are in range??
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