Year Rpt with changes

I have a rpt based on the following query

SELECT DISTINCT tblCity.State, [Name List].ReferDate, [Name List].Studied,
[Name List].Status, *
FROM tblCity INNER JOIN [Name List] ON tblCity.CityID = [Name List].City
ORDER BY tblCity.State;

there are txtboxes in the city area giving totals of all records in db

=Abs(Sum([Status]="Active")) & " ACTIVE"
=Abs(Sum([Status]="Pending")) & " PENDING "

etc...

There are 2 sections
1) ALL RECORDS (seen above)
2) NEW [=Format(Date(),"yyyy")]

I want to have #2 (parallel row of same data filtered by [ReferDate]>(THIS
YEAR ie. 2008) but I don't want to hard code it to 2008

I can't figure out how to change the control source to do what I want
combining the =Abs(Sum([Status]="Pending")) & " PENDING " and the date
filter.

It works if I change the whole query to:

SELECT DISTINCT tblCity.State, [Name List].ReferDate, [Name List].Studied,
[Name List].Status, *
FROM tblCity INNER JOIN [Name List] ON tblCity.CityID = [Name List].City
WHERE ((([Name List].ReferDate)>2008))
ORDER BY tblCity.State;

but then I still don't have rows of ALL info plus rows of current year info.

Please help! Thanks!

lmv

I think this is what you want.
=Abs(Sum([Status]="Active" AND Year(ReferDate]) = Year(Date()) )) & " ACTIVE"
=Abs(Sum([Status]="Pending" AND Year(ReferDate]) = Year(Date()))) & "
PENDING "

--
Duane Hookom
Microsoft Access MVP


"lmv" wrote:

> I have a rpt based on the following query
>
> SELECT DISTINCT tblCity.State, [Name List].ReferDate, [Name List].Studied,
> [Name List].Status, *
> FROM tblCity INNER JOIN [Name List] ON tblCity.CityID = [Name List].City
> ORDER BY tblCity.State;
>
> there are txtboxes in the city area giving totals of all records in db
>
> =Abs(Sum([Status]="Active")) & " ACTIVE"
> =Abs(Sum([Status]="Pending")) & " PENDING "
>
> etc...
>
> There are 2 sections
> 1) ALL RECORDS (seen above)
> 2) NEW [=Format(Date(),"yyyy")]
>
> I want to have #2 (parallel row of same data filtered by [ReferDate]>(THIS
> YEAR ie. 2008) but I don't want to hard code it to 2008
>
> I can't figure out how to change the control source to do what I want
> combining the =Abs(Sum([Status]="Pending")) & " PENDING " and the date
> filter.
>
> It works if I change the whole query to:
>
> SELECT DISTINCT tblCity.State, [Name List].ReferDate, [Name List].Studied,
> [Name List].Status, *
> FROM tblCity INNER JOIN [Name List] ON tblCity.CityID = [Name List].City
> WHERE ((([Name List].ReferDate)>2008))
> ORDER BY tblCity.State;
>
> but then I still don't have rows of ALL info plus rows of current year info.
>
> Please help! Thanks!
>
> lmv
>