Filter/group/sum by month

[Brian]

What is the most efficient way to filter or sum by month when individual
records have a date field?

I have tried this in my Where clause:

Where Month([FieldName])= Month([CriteriaDate]) AND Year([FieldName]) =
Year([CriteriaDate])

It just seems a little cumbersome to separate the month and year on both
sides, then compare against the month and year. I have considered calculating
the first/last days of each month, then doing a BETWEEN, but that might be
even more tricky.

Would it be more efficient to just generate a single number (e.g. MonthYear
of each value as Year(value)*100 + Month(value) and then compare the results?

Or is there some simple Month/Year function I have missed somewhere?

 

[Brian]

I searched a little farther through this forum and found the simple solution:

Format([DateField],"yyyymm")

Duh!

 

[John Vinson]

I searched a little farther through this forum and found the simple solution:

Format([DateField],"yyyymm")

Another way... one that takes advantage of any index on a date
field... uses one parameter twice:

= DateSerial([Enter year:], [Enter month:], 1) AND < DateSerial([Enter year:], [Enter month:] + 1, 1)

John W. Vinson[MVP]

 

[Van T. Dinh]

.... and John's solution is a lot more efficient, especially if the Source
Table has many records.

--
HTH
Van T. Dinh
MVP (Access)

I searched a little farther through this forum and found the simple
solution:

Format([DateField],"yyyymm")

Another way... one that takes advantage of any index on a date
field... uses one parameter twice:

= DateSerial([Enter year:], [Enter month:], 1) AND < DateSerial([Enter
year:], [Enter month:] + 1, 1)

John W. Vinson[MVP]

 

[Brian]

Thank you, John.

I searched a little farther through this forum and found the simple solution:

Format([DateField],"yyyymm")

Another way... one that takes advantage of any index on a date
field... uses one parameter twice:

= DateSerial([Enter year:], [Enter month:], 1) AND < DateSerial([Enter year:], [Enter month:] + 1, 1)

John W. Vinson[MVP]