Date Time .... help

[khekking]

INFORMATION:
Helping a friend who is using a client side scheduler to run a crystal report of rentals initiated to his field sales team. The scheduler emails the report each day at 3:00 pm. Their end of business is at 5:00 pm.

THE PROBLEM:
There have been rentals initiated between 3:00 and 5:00 pm that are not captured by the report. Currently the report selects data as follows:

if {@DayOfTheWeek} = "Monday" then {SO_Detail.CreationDate} in DateAdd("d", -2, CurrentDate) to CurrentDate
and {SO_Detail.PRTNUM_28} like "RENTHFV*"
and {SO_Detail.STATUS_28} = "3"
and isNull ({SO_Detail.SHPDTE_28})

else {SO_Detail.CreationDate} = CurrentDate
and {SO_Detail.PRTNUM_28} like "RENTHFV*"
and {SO_Detail.STATUS_28} = "3"
and isNull ({SO_Detail.SHPDTE_28})

THE SOLUTION:
How does one select 3:00 pm from the previous day to "now"?

 

[MCuthill]

khekking,

The function [Blue]DateTime[/blue]() will need to be integrated into your selection criteria in place of the [blue]DateAdd[/blue]() and [blue]CurrentDate[/blue] functions at present. I *think* the following should work, but I have not used the function extensively and am unable to test syntax at the moment.

DateTime() can be defined in three ways:
1) DateTime(Date, Time)
2) DateTime(YYYY, MM, DD)
3) DateTime(YYYY, MM, DD, HH, MM, SS)
It is the first syntax that I am hoping to utilize for your request, though I have only used the 3rd syntax in the past.

Changes to your selection criteria are in Red, though may require some fine tuning.

if {@DayOfTheWeek} = "Monday" then 
(
   {SO_Detail.CreationDate} in [red]DateTime(DateAdd("d", -2, CurrentDate),Time(15,0,0))[/red] to CurrentDate
   and {SO_Detail.PRTNUM_28} like "RENTHFV*"
   and {SO_Detail.STATUS_28} = "3" 
   and isNull ({SO_Detail.SHPDTE_28})
)
else 
(
   {SO_Detail.CreationDate} [red]in DateTime(DateAdd("d", -1, CurrentDate),Time(15,0,0)) to[/red] CurrentDate
   and {SO_Detail.PRTNUM_28} like "RENTHFV*"
   and {SO_Detail.STATUS_28} = "3" 
   and isNull ({SO_Detail.SHPDTE_28})
)

Just to ask, on Monday's -- is the report looking to Generate from Saturday 3pm or from Friday at 3pm? The above, subtracting 2 days from Monday will mean Saturday 3pm til Monday at the report generation time. If you are looking for Friday 3pm til Monday, please change the "-2" in the first DateAdd() function to a "-3".

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

PS: if Crystal is unhappy using a "DateTime" to "CurrentDate" range, you may need to modify the "CurrentDate" to be "CurrentDateTime"

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."