Date Range optional prameter

[former_member959185]

Optional Parameters and Nulls

Using Crystal Version CR Developer Version 14.0.4.738 RTM

Designing a report that has 3 different date fields and when it is ran, the user will only be providing a date range for 1 of the 3 fields.

Someone mentioned “optional parameters” and they thought this may help.

Been reading up on them and it seems like it would, but I have not been able to get the examples to work with my selection formula.

My current selecting formula is:
{MyTable.Ship Date} in DateTime ({?BeginShipDate}) to DateTime ({?EndShipDate}) and
{MyTable.State} like {?State} and
{MyTable.Ticket Number} like {?Ticket} and
{MyTable.Date Call Received} in DateTime ({?BeginDateCallReceived}) to DateTime ({?EndDateCallReceived}) and
{MyTable.Date Paid} in DateTime ({?BeginDatePaid}) to DateTime ({?EndDatePaid})

What would the syntax be to allow for no dates to be provided for 2 of the 3?

Thanks
Bennie

[former_member959185]

Brian,

Just now circling back to this report.

I tried your suggestion and when the report runs after providing the values for the parammeters, it is displaying an error: "Parameter has no value" and one of the parameter fields Ieft blank is highlighted. The parameter is set to optional.

Any thoughts as to what the issue may be and how to address?

Thanks

Ben

[former_member292966]

Hi Ben,

We can append an or to the condition to check for null dates using Date (0, 0, 0) like:

(If Not ({?BeginShipDate} In [Date (0, 0, 0), Date (9999, 12, 31)]) And Not ({?EndShipDate} In [Date (0, 0, 0), Date (9999, 12, 31)]) then 
    {MyTable.Ship Date} in DateTime ({?BeginShipDate}) to DateTime ({?EndShipDate}) 
Else {MyTable.Ship Date} < DateTime (9999, 12, 31, 0, 0, 0)) and
{MyTable.State} like {?State} and
{MyTable.Ticket Number} like {?Ticket} and 
(If Not ({?BeginDateCallReceived} In [Date (0, 0, 0), Date (9999, 12, 31)]) And Not ({?EndDateCallReceived} In [Date (0, 0, 0), Date (9999, 12, 31)]) then 
    {MyTable.Date Call Received} in DateTime ({?BeginDateCallReceived}) to DateTime ({?EndDateCallReceived}) 
Else {MyTable.Date Call Received} < DateTime (9999, 12, 31, 0, 0, 0)) and
(If Not ({?BeginDatePaid} In [Date (0, 0, 0), Date (9999, 12, 31)]) And Not ({?EndDatePaid} In [Date (0, 0, 0), Date (9999, 12, 31)]) then 
    {MyTable.Date Paid} in DateTime ({?BeginDatePaid}) to DateTime ({?EndDatePaid}) 
Else {MyTable.Date Paid} < DateTime (9999, 12, 31, 0, 0, 0)); 

The formatting got messed up in the copy-paste but it's now checking for a NULL date as well as the default date for each.

Brian

[former_member959185]

Thanks Brian,

Have not tried your suggestion but I do not see any allowance for Null or empty date fields. Before I posted my question I believe I tried something similar and records were not returned as the field was ether null or empty.

Does your suggestion account for null or empty fields?

Again thanks for your time and efforts.

Ben

[former_member292966]

Hi Ben,

When I create optional parameters, I add a default value to it. So for dates, I use a date completely out of range like 9999-12-31.

Now you can have your selection formula check for that value and query accordingly like:

(If {?BeginShipDate} <> Date (9999, 12, 31) And {?EndShipDate} <> Date (9999, 12, 31) then 
    {MyTable.Ship Date} in DateTime ({?BeginShipDate}) to DateTime ({?EndShipDate}) 
Else {MyTable.Ship Date} < DateTime (9999, 12, 31, 0, 0, 0)) and

{MyTable.State} like {?State} and
{MyTable.Ticket Number} like {?Ticket} and 

(If {?BeginDateCallReceived} <> Date (9999, 12, 31) And {?EndDateCallReceived} <> Date (9999, 12, 31) then 
    {MyTable.Date Call Received} in DateTime ({?BeginDateCallReceived}) to DateTime ({?EndDateCallReceived}) 
Else {MyTable.Date Call Received} < DateTime (9999, 12, 31, 0, 0, 0)) and

(If {?BeginDatePaid}<> Date (9999, 12, 31) And {?EndDatePaid} <> Date (9999, 12, 31) then 
    {MyTable.Date Paid} in DateTime ({?BeginDatePaid}) to DateTime ({?EndDatePaid}) 
Else {MyTable.Date Paid} < DateTime (9999, 12, 31, 0, 0, 0)); 

Not the parenthesis around each of the If statements.

Good luck,

Brian