Last day of month given the year and month 1

[Harki06]

I am using Crystal Reports 10. I am accepting the year1, year2(to compare against year1 data), start month, end month. I'd like the record selection to limit to transaction dates that are in the range - start month year1 to end month year1, start month year2 to end month year2.

e.g Trans_date between Jan 2006 to March 2006 and Jan 2007 to March 2007.

Thanks!

 

[lbass]

What parameters are you using? What fields are you working with and what datatype are they?

-LB

 

[Harki06]

I am accepting start year,end year, start month, end month. The trans_date is from the database which is SQL server. The trans_date is a datetime field. Year and month are number type in parameter. Do you think I need not create a date field per se but just use the month and year for comparison against the trans_date month and year?

Thanks!

 

[raja2008]

Hello!


guessing that you have the parameters for start year ,start month,end year and end month.

create four formulas : use your own conventions iam giving them so it will be easy to understand.

startyearstartmonth
startyearendmonth
endyearstartmonth
endyearendmonth


Startyearstartmonth : date({?start year},{?start month},1)

do the same way for others

and in the selectexpert:

((transdate>=startyearstartmonth) and (transdate<=startyearendmonth)) or
((transdate>=endyearstartmonth) and
(transdate<=endyearendmonth))

i tried it works,but the only problem is with the date,may be we have to find a formula that gets the end date according to the month and year.

Thank You

 

[lbass]

You could just use the month and year functions like:

year({table.date}) in [{?year1},{?year2}] and
(
month({table.date}) >= {?startmo} and
month({table.date} <= {?endmo}
)

You would get better performance if you created SQL expressions:

[{%year}:]
{fn year(table.`date`)}

[{%month}:]
{fn month(table.`date`)}

Then use the SQL expressions in the earlier record selection formula.

-LB

 

[raja2008]

Hello lbass!


you made it so easy.

 

[Harki06]

Thanks a lot lbass! That was great!

 

[Harki06]

Raja,

Thanks to you too-