Sorting by Month and Year in Query

[Jon M.]

I am trying to run a query that will allow me to select Months and Years from
a real date field. Example: my field is date and it is a short date;
1/1/2008. I want to run a query that will sum all my fields by month, so if
I enter January 2008 or even 1/2008 it will display just the totals of that
month. I tried formatting by Month in the query; "mmmm" but it still
displays each day of the month individually, it just doesn't show the actual
date it just shows January. Basically I want to run a monthly sum based on
month and year. As always any help is greatly appreciated.

 

[Yanick]

add a field to your query like this :

YearMonth : Format([YourDateField],"yyyy") & Format([YourDateField],"mmmm")

Then groupby that field and do your sum

Yanick

 

[Pat Hartman]

This is not likely to give you the desired sort sequence since April isn't
the first month of the year. Your sort needs to be on the numeric month so
you need two fields, one to sort on and one to display.
Format(YourDate,"yyyymm") - for sorting
Format(YourDate, "mmmm yyyy") - for display

add a field to your query like this :

YearMonth : Format([YourDateField],"yyyy") &
Format([YourDateField],"mmmm")

Then groupby that field and do your sum

Yanick

I am trying to run a query that will allow me to select Months and Years
from
a real date field. Example: my field is date and it is a short date;
1/1/2008. I want to run a query that will sum all my fields by month, so
if
I enter January 2008 or even 1/2008 it will display just the totals of
that
month. I tried formatting by Month in the query; "mmmm" but it still
displays each day of the month individually, it just doesn't show the
actual
date it just shows January. Basically I want to run a monthly sum based
on
month and year. As always any help is greatly appreciated.