Query criteria: Count number of distinct months in date field
Posted on 2016-11-05
I'm trying to use query criteria to count the number of distinct months (including distinct years) in a date field stored as a short date in the underlying table. I think I have to use Select, Count, and Distinct, but I can't figure out how to extract the month and year from the date field. The statements I've tried throw syntax errors or tell me I can't do aggregate functions on that field.
This gives a syntax error:
=select count(*) from (select distinct(DateSerial(Year(*),Month(*),Day()));
As does this:
=select count(*) from (select distinct(DateSerial(Year([AsnmDate]),Month(AsnmDate),Day()));
Thanks in advance for any help.