Year Rpt with changes

  • Thread starter Thread starter lmv
  • Start date Start date
L

lmv

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 "
 
....that's the answer! Just needed your info and the [ before referdate to get
to the total.

Thanks Duane!

Duane Hookom said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top