Accounting Months & Years

[njberg]

Hi - using CR 9 and need to extract information from a specific month and year to the current month and year. Problem is 06 is before 01 for the parameter for the month. Would like to default the minimum or starting month and year as 06 2004 and just allow the user to input the maximum (ending month) and year. i.e.: 06 2004 thru 01 2005.

Help is appreciated.
Thank you.

 

[synapsevampire]

Create a formula to derive it:

@enddate:
dateserial(cdate({?yearprompt},{?monthprompt}+1,1))-1

Now in the report->edit selection formula->record, use:

{table.date} >= cdate(2004,6,1)
and
{table.date} <= @enddate

-k

 

[njberg]

Thanks for a quick answer - will this work if the Month field and Year field are two separate numeric fields?
Thank you

 

[synapsevampire]

Then the approach is different, btw, that's a bad way to store data:

In the report->edit selection formula->record, use:

(
totext({table.year}) & totext({table.month}) <= totext({?MyMonthPrompt})&totext({?MyYearPrompt})
)

This probably won't pass the sql to the database, so creating a single intelligent field on the database would be better, such as a View on the database or as a Crystal SQL Expression.

These are based on Crystal version and database used, neither of which you shared.

A SQL Expression should be simple to figure out, read the help on it.

-k