parameter for different years

[pacificpear]

access/cr10

need some help with the parameter field
my query looks like this
code amount startdate enddate
MBA0001 93450 2/2/2002 5/17/2004
MBA0001 55250 2/16/2002 4/21/2004
MBA0001 55250 3/15/2004 6/22/2005
MBA0001 155258 3/15/2004 11/22/2004
MBA0001 46762 5/1/2004 6/22/2007
MBA0018 10000 4/12/2004 6/8/2004

in the parameter field i need to include all the year i.e from 2002 to 2007
and when we run the report and ask the year

if we select 2004 it should give all the record which have the start date and end date between 1/1/2004 to 12/31/2004
expected result

code amount startdate enddate
MBA0001 155258 3/15/2004 11/22/2004
MBA0018 10000 4/12/2004 6/8/2004


same result when we select any year ie start date and end date is between that year

can anyone help
pp

 

[Madawc]

If you want every record that started in a given year, ended in that year or has a start-to-end range including that year, then put the following in record selection:

ParamYear >= Year(StartDate)
or 
ParamYear <= Year(EndDate)

Entering '2003' would get the 1st and 2nd records. '2005' would get the 3rd and 5th.

Is this what you want?

Madawc Williams (East Anglia, UK)

 

[lbass]

Try:

{?parm} = year({table.start}) and
{?parm} = year({table.end})

-LB

 

[pacificpear]

hi
it should only select the amount when the parameter is passed
expected result

code amount
MBA0001 155258
MBA0018 10000

these two records of the yaer 2004, when the parameter will be pass of the start date(1/1/2004) to end date(12/31/2004)
*dates are originally in datetime

pp

 

[synapsevampire]

I would suggest you try something like:

{table.date} >= cdate(year({?YearParameter}),1,1,0,0,0)
and
{table.date} <= cdate(year({?YearParameter}),12,31,11,59,59)

-k