Extract month and year

  • Thread starter Thread starter Silvio
  • Start date Start date
S

Silvio

I have a date field that stores date and time. How can I extract in a query
only the month and year (e.g. March-1992 or 3-1992 - I am not concerned with
the date format)

Thank you,
Silvio
 
If the date/time field is named Date1, you could type an expression like
this into the Field row in query design:
Format([Date1], "mmmm-yyyy")

That will generate text which doesn't sort correctly, e.g. April before
January, and not by year. So a better solution might be to just set the
Format property of the field (in the Properties box in query design.) Use:
mmmm-yyyy
 
Allen, I need to extract month and year so when I run reports I dont need to
specify from the beginning to the end of each month (e.g. 1/1/08 to 1/31/08)
but I can simply select Jan-2008 report or Jan 2008 to Mar-2008 (I am not
sure how many days each month have for each calendar month) and so on so it
is not just a matter of format.

Allen Browne said:
If the date/time field is named Date1, you could type an expression like
this into the Field row in query design:
Format([Date1], "mmmm-yyyy")

That will generate text which doesn't sort correctly, e.g. April before
January, and not by year. So a better solution might be to just set the
Format property of the field (in the Properties box in query design.) Use:
mmmm-yyyy

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Silvio said:
I have a date field that stores date and time. How can I extract in a query
only the month and year (e.g. March-1992 or 3-1992 - I am not concerned
with
the date format)

Thank you,
Silvio
Click to expand...
Click to expand...
 
Silvio said:
Allen, I need to extract month and year so when I run reports I dont
need to specify from the beginning to the end of each month (e.g.
1/1/08 to 1/31/08) but I can simply select Jan-2008 report or Jan
2008 to Mar-2008 (I am not sure how many days each month have for
each calendar month) and so on so it is not just a matter of format.
Click to expand...

If I am understanding you correctly, the path you are going down now will
cause you to be applying criteria against an expression rather than against
a field. This is a bad practice and should be avoided whenever possible
because the query engine cannot use an index when you filter on an
expression and thus you force a full table scan.

You can construct your query so that you are only asking the user for the
month and year, but still applying a proper criteria to your date field. It
just takes a bit more thought.

It looks like you plan on providing a drop-down list to the user to select
the month. So I assume you are getting your criteria for the query from an
open form. Is that correct or are you just using a self-prompting parameter
in the query?

Tips:

This always returns the first day of the month for a supplied date...
DateSerial(Year(SomeDate), Month(SomeDate), 1)

This always returns the last day of the month for a supplied date...
DateSerial(Year(SomeDate), Month(SomeDate)+1, 0)

....and yes both of these work across year-end boundaries and account for
leap-years.
 
Back
Top