Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: contraining on date & time Post Reply Post New Topic
Author Message
Paula J
Groupie
Groupie
Avatar

Joined: 22 Aug 2011
Location: United States
Online Status: Offline
Posts: 51
Quote Paula J Replybullet Topic: contraining on date & time
    Posted: 11 Jul 2014 at 9:42am
I have a report that I need to run 3 times a day m-f at 9:00, 11:00, 13:00
The 9:00 report I want records that have a date/time of the current date and 7:00-8:30 to drop to the report
The 11:00 report I want records that have a date/time of the current date and 8:31-10:30 to drop to the report
The 13:00 report I want records that have a date/time of the current date and 10:31-12:30.
The above will be based on a record field CHNG_DT that contains the date and time.
Example
Acct #      Chng_Dt
100         7/11/14 8:40  
200         7/11/14 9:03
300         7/11/14 10:31
 
9:00 report will be blank
11:00 report would contain acct 100 & 200
13:00 report would contain acct 300
 
Also if the report is blank I want it to run and have a record of the blank report but I do not want to emailed the blank report.
 
Can anyone tell me how to set up the report to accomplish the 3 runs but records constraining based on current date and the times I have listed?
Paula J
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Jul 2014 at 10:25am
I am not sure how to schedule the reports as I don't use that function but you can handle the record limitations by using a select statement, something like this..
It assumes that you have the same repiort scheduled on the M-F @ the 3 specified times.
 
datediff('d',currentdate,field)=0
and
(
(currenttime  in time(8,59,00) and time(9,1,0)
and time(field) in time(7,00,00) and time(8,30,0))
or
(currenttime  in time(10,59,00) and time(11,1,0)
and time(field) in time(8,30,01) and time(10,30,0))
or
(currenttime  in time(12,59,00) and time(13,1,0)
and time(field) in time(10,30,01) and time(12,30,0))
)


Edited by DBlank - 11 Jul 2014 at 10:26am
IP IP Logged
Paula J
Groupie
Groupie
Avatar

Joined: 22 Aug 2011
Location: United States
Online Status: Offline
Posts: 51
Quote Paula J Replybullet Posted: 18 Jul 2014 at 10:26am
Thanks but this does not work when I verify the formula it tells me a time range is required and then highlights the time(8,59,00) portion of the first line in the formula.

Edited by Paula J - 18 Jul 2014 at 10:27am
Paula J
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 Jul 2014 at 10:41am
sorry-mixing in some non-crystal language
 
datediff('d',currentdate,field)=0
and
(
(currenttime  in time(8,59,00) to time(9,1,0)
and time(field) in time(7,00,00) to time(8,30,0))
or
(currenttime  in time(10,59,00) to time(11,1,0)
and time(field) in time(8,30,01) to time(10,30,0))
or
(currenttime  in time(12,59,00) to time(13,1,0)
and time(field) in time(10,30,01) to time(12,30,0))
)
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.031 seconds.