YTD and LastFullMonth Question...Maybe 1

[ChiTownDiva]

I need to pull records based on Jan 1st of the current year until the last day of the previous month...what do I do when I have to pull Jan through Dec in January of next year?

I've been going around and around with this, but I still can't figure it out. Help!!!

ChiTownDivaus

 

[GJParker]

Try this as your Record Selection Formula

if month(currentdate) = 1 Then
    {MyTable.MyField} >= Date(Year (Today) -1,01,01) and
    {MyTable.MyField} <= DateAdd ( 'd',-DatePart ('d',Today ) ,Today )    
Else
    {MyTable.MyField} >= Date(Year (Today),01,01)
    [MyTable.MyField} <= DateAdd ( 'd',-DatePart and ('d',Today ) ,Today )

Be aware that this will slow processing down as this uses crystal functions, therefore records will be processed on the local PC.

A better way would be to do this on the database using a database view or a stored procedure.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[vidru]

This has a pretty good chance at passing to the db, but wouldn't return any data for January:

{Table.DateField} In
[Minimum (YearToDate) to Maximum (LastFullMonth)]

-dave

 

[kskid]

I use the following which is passed to the DB

{TABLE.DATE} in
[dateserial(year(dateserial(year(currentdate),month(currentdate)-1,1)),1,1) to_
dateserial(year(currentdate),month(currentdate),1)]

 

[jestrada101]

wichitakid,

Suppose I have a prompt that asks for a data and the user keys in April 3, 2004 (e.g. 4/3/2004)

How can I get this to just query March information? And also April information, but of 2003?

Thanks!
JE

 

[jestrada101]

Figured it out.. Thanks...

for last month only...

{TABLE.DATE} in
[dateserial(year({?BeginDate}),month({?BeginDate})-1,1) to_
dateserial(year({?BeginDate}),month({?BeginDate}),1)]

for YEAR

{TABLE.DATE} in
[dateserial(year({?BeginDate})-1,month({?BeginDate}),1) to_
dateserial(year({?BeginDate})-1,month({?BeginDate})+1,1)]

 

[dgillz]

I am not sure this is a good formula. What if the month is January?

How would your expression month({?BeginDate})-1 work?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[lbass]

For last full month, you could use:

{table.date} in dateadd("m",-1, {?begindate}-day({?begindate})+1) to
{?begindate}-day({?begindate})

For YTD, you could use:

{table.date} in dateadd("yyyy",-1,{?begindate}-day({?begindate})+1) to
{?begindate}-day({?begindate})

-LB

 

[jestrada101]

Not sure exactly how it's working.. but it works....

i input a date of 1/1/2004 and I get the results for >=12/1/2003 and < 1/1/2004

Interesting... but if you all say the following is safer.. i'll use that...

{table.date} in dateadd("m",-1, {?begindate}-day({?begindate})+1) to
{?begindate}-day({?begindate})

Thanks all!!!

 

[kskid]

That's why I like using dateserial.... it automatically adjusts the date, even if the month is January.

if the datex is 1/15/2004, then

dateserial(year({datex}),month({datex})-1,1) will result in a date of 12/1/2003