Need some help on selecting 'Month-to-Date' formula.

[stormtrooper]

Hi there. I have a report in CR7 that is scheduled to run every morning at 3am. Now, my problem is that I have a selection formula that selects records for month-to-date. For example, today is Nov.1/02, the report runs at 3am Nov.1/02. I will only get records that are present from 12am to 3am, which is correct. But if the report is run on the previous day (Oct.31/02), I will never see data that occurs on Oct.31/02 except for the data that occurs between 12am Oct.31 to 3am Oct.31. What I'm getting at is that for the period of 3am Oct.31/02 to 11:59pm Oct.31/02, that data will never be reported on.

This is what I'm thinking of:

If Day({dt_field}) = 1
Then select the month number of the previous month
Else ({dt_field}) In MonthToDate

That's all I ave thought of so far. I hope this isn't too confusing. Any help would be appreciated.

Thanks

 

[synapsevampire]

Your theory seems OK:

If Day({dt_field}) = 1
({dt_field}) In LastFullMonth
Else
({dt_field}) In MonthToDate

If LastFullMonth is not supported, then work out the date:

({dt_field}) <= date(year(currentdate-1),month(currentdate-1),1)
and
({dt_field}) < currentdate

Lots of variations possible here.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[stormtrooper]

Hey thanks synapsevampire for the confirmation.

 

[stormtrooper]

Ok. The scenario suddenly has been changed on me. Now I need month to previous day.

eg.
Report is run at 3am, Oct.31. I need all data for the month up to 11:59pm, Oct.30.

Report is run at 3am, Nov.1. I need all data for the month of October.

Report is run at 3am, Nov.2. I need all data from Nov.1 to 11:59pm, Nov.1.

etc...

So is this going to be a previous day thing? Or is it more complicated?

Thanks

 

[dgillz]

{DateField} in MonthtoDate and {DateField} in AllDatesToYesterday

Try that. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[stormtrooper]

dgillz, I used your formula and compared it against the one I planned to use

If Day(CurrentDate) = 1
Then ({BILLG_ITM.BILLG_ITM_DTM}) In LastFullMonth
Else Month({BILLG_ITM.BILLG_ITM_DTM}) >= Month(CurrentDate) And
Day({BILLG_ITM.BILLG_ITM_DTM}) <= Day(CurrentDate) - 1

and the same number of records came up. So they must be the same, I hope. The reason I ask is that I would like to test it against my scenario above but those dates have already past. I cannot manipulate the data in any of our databases so is there anything I can do within Crystal itself?

 

[stormtrooper]

Got it. I found the set print date option. Thanks for all your help.