Date Range Formulas 1

[NattyCat]

Crystal 9, SQL Server 7.0

I have a total mind block, can someone help?

I'm writing a report that has to return data for 5 months ago. Ie, if I run the report on 1st January, it must return the full months data for the whole month of August. It will be going back over years (2005-2004) so I can't just use month - 5. I've got this far:

local datetimevar sdate;
local datetimevar edate;

//to get day 1 of whatever month 5 months ago
sdate := dateadd("m",-5,currentdate);
sdate := DateTime (year(sdate),month(sdate) ,01 ,00 ,00 ,00 );

//to get last day of the month 5 months ago
edate := DateTime (year(sdate),month(sdate) ,?? ,00 ,00 ,00 )

^^ I have a problem with this part, because I don't know how many days will be in the month

Any ideas?

 

[dgillz]

Try this in your record selection formula:

{YourDateField} in Date(Year(Dateadd("m",-5,CurrentDate)),Month(Dateadd("m",-5,CurrentDate)),1) to
Dateadd("d",-1,Date(Year(Dateadd("m",-4,CurrentDate)),Month(Dateadd
("m",-4,CurrentDate)),1))

 

[NattyCat]

Hi,

Thanks for getting back to me. The records returned are still not working as the records are not being restricted if I leave all the parameter fields blank. I think there may have to be a statement to restrict the records if all of the parameters are left blank, which they often are. I need the date,PFO code and CharityReportType record selection to ALWAYS apply, with optional formatting changes depending on the parameters used. The entire record selection is as follows:



if {?Output medium} = "" and {?Recipient}="" and {?Outputformat}=""
then
true // select all recipients who want the report, and format it as a report.
else
if {?Output medium} = "Paper"
then
({DisbursementAndPledgeDonorVw.NominationPFOCode} = "" or isnull({DisbursementAndPledgeDonorVw.NominationPFOCode})) and
{DisbursementAndPledgeDonorVw.CharityReportTypeID} = 4.00 and
{DisbursementAndPledgeDonorVw.NominationCreationDate} in Date(Year(Dateadd("m",-5,CurrentDate)),Month(Dateadd("m",-5,CurrentDate)),1) to
Dateadd("d",-1,Date(Year(Dateadd("m",-4,CurrentDate)),Month(Dateadd("m",-4,CurrentDate)),1)) // select all who want the report ON PAPER, and format it as a report.
else
if {?Outputformat} = "XML"
then
({DisbursementAndPledgeDonorVw.NominationPFOCode} = "" or isnull({DisbursementAndPledgeDonorVw.NominationPFOCode})) and
{DisbursementAndPledgeDonorVw.CharityReportTypeID} = 4.00 and
{DisbursementAndPledgeDonorVw.NominationCreationDate} in Date(Year(Dateadd("m",-5,CurrentDate)),Month(Dateadd("m",-5,CurrentDate)),1) to
Dateadd("d",-1,Date(Year(Dateadd("m",-4,CurrentDate)),Month(Dateadd("m",-4,CurrentDate)),1)) // select all who want the report by email or diskette and fomat XML.
else
if {?Recipient}<>""
then {?Recipient} = {DisbursementAndPledgeDonorVw.EmployeeExternalSystemID} and
({DisbursementAndPledgeDonorVw.NominationPFOCode} = "" or isnull({DisbursementAndPledgeDonorVw.NominationPFOCode})) and
{DisbursementAndPledgeDonorVw.CharityReportTypeID} = 4.00 and
{DisbursementAndPledgeDonorVw.NominationCreationDate} in Date(Year(Dateadd("m",-5,CurrentDate)),Month(Dateadd("m",-5,CurrentDate)),1) to
Dateadd("d",-1,Date(Year(Dateadd("m",-4,CurrentDate)),Month(Dateadd("m",-4,CurrentDate)),1))

 

[kskid]

I use the DateSerial function as it will automattically crossover the years. I also use "to_" in my range as it will exclude the ending date.

{table.date} in 
[
dateserial(year(currentdate),month(currentdate)-5,1)
to_
dateserial(year(currentdate),month(currentdate)-4,1)
]