change date format from aggregate query

[Office User]

I'm working on a report that pulls from the following query:
SELECT Format([Date],"mmmm") AS [Month], Avg([All Calls].[Avg Speed Ans]) AS
[Avg Ans Speed], Sum([All Calls].[ACD Calls]) AS [ACD Calls], Avg([% ACD
Time]/100) AS [%ACD], Sum([All Calls].[Aban Calls]) AS [Aban Calls], Avg([%
Aband Calls]/100) AS [% Aban], Sum([All Calls].[Flow Out]) AS [Voice Mail
Calls], Avg([% Flow Out Calls]/100) AS [% Voice Mail]
FROM [All Calls]
WHERE ((([All Calls].Date)>#1/1/2005#))
GROUP BY Format([Date],"mmmm"), Format(Date,"mm")
ORDER BY Format([Date],"mm");

The All Calls table has multiple records for each date so the grouping and
formatting is used to display stats by month. I want to put the year only on
report header. I've tried variations of the following in an unbound text box:
=Format([All Calls]!Date,"yyyy")

However, then I get prompted for the field. Any ideas how I can put the
Year on my report automatically. My "fix" for now is to type it in a label.
However, years down the road I don't want to have to remember at the
beginning of the year to change the lable.

Thanks for any input.
Marcia

 

[David Lloyd]

Marcia:

If the year you want on your report is always the current year, one option
would be to set your unbound textbox to something like:

=Format(Now(),"yyyy")


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I'm working on a report that pulls from the following query:
SELECT Format([Date],"mmmm") AS [Month], Avg([All Calls].[Avg Speed Ans]) AS
[Avg Ans Speed], Sum([All Calls].[ACD Calls]) AS [ACD Calls], Avg([% ACD
Time]/100) AS [%ACD], Sum([All Calls].[Aban Calls]) AS [Aban Calls], Avg([%
Aband Calls]/100) AS [% Aban], Sum([All Calls].[Flow Out]) AS [Voice Mail
Calls], Avg([% Flow Out Calls]/100) AS [% Voice Mail]
FROM [All Calls]
WHERE ((([All Calls].Date)>#1/1/2005#))
GROUP BY Format([Date],"mmmm"), Format(Date,"mm")
ORDER BY Format([Date],"mm");

The All Calls table has multiple records for each date so the grouping and
formatting is used to display stats by month. I want to put the year only
on
report header. I've tried variations of the following in an unbound text
box:
=Format([All Calls]!Date,"yyyy")

However, then I get prompted for the field. Any ideas how I can put the
Year on my report automatically. My "fix" for now is to type it in a label.
However, years down the road I don't want to have to remember at the
beginning of the year to change the lable.

Thanks for any input.
Marcia

 

[Office User]

I had thought of that but also considered if someone is running the report
after the first of the year (say January 2nd, 2006), the data on the report
would still be from 2005 so it should have 2005 printed on report.

Thanks,
Marcia