Date queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kirk P. - 02 Dec 2005 13:07 GMT

I'm querying a table with subscription information that contains customerID
and end date. I want to query for all subscriptions ending in December, so
I'm entering criteria of "between 12/1/2005 and 12/31/2005. That works fine,
but can I enter the criteria to query for all dates within a month, for
example Dec-2005?

It would just save a little keying.

Reply to this Message

John Spencer - 02 Dec 2005 15:34 GMT

Yes, you can, but the retrieval will be slover - with small databases the
speed difference may not be apparent.

One method, add a new column to your query

Field: MonthYear: Format([EndDate],"mmm-yyyy")
Criteria: ="Dec-2005"

Another method would be to use criteria like the following
Field:[End Date]
Criteria: Between DateSerial([What Year?],[What Month number?],1) And
DateSerial([What Year?],[What Month number?]+1,0)

This would pop up two dialogs when you run the query - asking you for the
year number and month number. You would enter 5 and 12 for December 2005.

Another method would be to use a form to get the date parameters and have
the query grab them from the form.

> I'm querying a table with subscription information that contains
> customerID
[quoted text clipped - 6 lines]
>
> It would just save a little keying.

Reply to this Message