Parameter month query

[EllenM]

I'd like to query date by the month. For instance keying in "Nov 2009" would
bring up all the dates of that month.

The date field is formatted as a short date, for example 11/1/2009.

Thanks in advance,
Ellen

 

[John Spencer]

Well, it can be done, but a better way would be to use a form for input and
enter the start and end date of a range into the form.

Using a parameter prompt which requires you to enter the month and a 4-digit
year you could use:

Between CDate([Enter month and Year])
and DateSerial(Year(CDate([Enter month and Year])),Month(CDate([Enter month
and Year])) +1,0)

Better would be to have a form with two controls that you would enter the
start and end dates.

Between [Forms]![Formname]![StartDateControl] and
[Forms]![FormName]![EndDateControl]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[BB]

Ellen

The simplest solution to your question is to have a column in your query (in
grid design view) where...

FIELD ROW: Format([MyDateField],"mmm yyyy")
CRITERIA ROW: [Enter Month to Run Query for (eg Nov 2009)]

(The statement "Enter Month..." is in square brackets and thus is a user
enterable parameter)

However, having said this, your query could run very slowly if you have a
lot of data. This is because the query is the query is running the Format
function against every record of data (i.e. the MyDateField). To improve
performance you want the WHERE cluase (or criteria row) to contain the
function.

In this case, you need to transform the date they enter (from"mmm yyyy"
format) into an explicit date. So you would have two columns for your
criteria, both would use the same date field for comparison, with differing
criteria, the first greater than or equal to the first day of the month, and
the second less than the first day of the next month. (to do this, use the
CDate function which converts a string to a date if it can)

COLUMN 1
FIELD ROW: [MyDateField]
CRITERIA ROW: >= CDate("01 " & [Enter Month to Run Query for (eg Nov 2009)])

COLUMN 2
FIELD ROW: [MyDateField]
CRITERIA ROW: <DateAdd("m",1,CDate("01 " & [Enter Month to Run Query for
(eg Nov 2009)])))

Be careful to use the same spelling for the parameter in each column. Else
you will be asked twice for the month/year.

This, obviously, is not the only way to do this, but it gives you a couple
of things to try.Alternate ways of do this are using a Form which allows you
to specify the month/year and click a button to run the query. The
functionality behind the button would set some global variables with the two
different dates, and the query would use a function to reference the
variables. In such an instance the WHERE clause of the query would be
something like: [MyDateField] >= gfuncDateFrom() and
[MyDateField]<gfuncDateTo().

 

[EllenM]

Thanks BB. Works perfectly.

One more question, is there a way to pull up the dates for the current month?

Ellen

The simplest solution to your question is to have a column in your query (in
grid design view) where...

FIELD ROW: Format([MyDateField],"mmm yyyy")
CRITERIA ROW: [Enter Month to Run Query for (eg Nov 2009)]

(The statement "Enter Month..." is in square brackets and thus is a user
enterable parameter)

However, having said this, your query could run very slowly if you have a
lot of data. This is because the query is the query is running the Format
function against every record of data (i.e. the MyDateField). To improve
performance you want the WHERE cluase (or criteria row) to contain the
function.

In this case, you need to transform the date they enter (from"mmm yyyy"
format) into an explicit date. So you would have two columns for your
criteria, both would use the same date field for comparison, with differing
criteria, the first greater than or equal to the first day of the month, and
the second less than the first day of the next month. (to do this, use the
CDate function which converts a string to a date if it can)

COLUMN 1
FIELD ROW: [MyDateField]
CRITERIA ROW: >= CDate("01 " & [Enter Month to Run Query for (eg Nov 2009)])

COLUMN 2
FIELD ROW: [MyDateField]
CRITERIA ROW: <DateAdd("m",1,CDate("01 " & [Enter Month to Run Query for
(eg Nov 2009)])))

Be careful to use the same spelling for the parameter in each column. Else
you will be asked twice for the month/year.

This, obviously, is not the only way to do this, but it gives you a couple
of things to try.Alternate ways of do this are using a Form which allows you
to specify the month/year and click a button to run the query. The
functionality behind the button would set some global variables with the two
different dates, and the query would use a function to reference the
variables. In such an instance the WHERE clause of the query would be
something like: [MyDateField] >= gfuncDateFrom() and
[MyDateField]<gfuncDateTo().
--
B.

Live Long and Prosper...

I'd like to query date by the month. For instance keying in "Nov 2009" would
bring up all the dates of that month.

The date field is formatted as a short date, for example 11/1/2009.

Thanks in advance,
Ellen

 

[KARL DEWEY]

pull up the dates for the current month?
The simplest solution to your question is to have a column in your query (in
grid design view) where...

FIELD ROW: Format([MyDateField],"mmm yyyy")
CRITERIA ROW: Format(Date(),"mmm yyyy")


--
Build a little, test a little.

Thanks BB. Works perfectly.

One more question, is there a way to pull up the dates for the current month?

Ellen

The simplest solution to your question is to have a column in your query (in
grid design view) where...

FIELD ROW: Format([MyDateField],"mmm yyyy")
CRITERIA ROW: [Enter Month to Run Query for (eg Nov 2009)]

(The statement "Enter Month..." is in square brackets and thus is a user
enterable parameter)

However, having said this, your query could run very slowly if you have a
lot of data. This is because the query is the query is running the Format
function against every record of data (i.e. the MyDateField). To improve
performance you want the WHERE cluase (or criteria row) to contain the
function.

In this case, you need to transform the date they enter (from"mmm yyyy"
format) into an explicit date. So you would have two columns for your
criteria, both would use the same date field for comparison, with differing
criteria, the first greater than or equal to the first day of the month, and
the second less than the first day of the next month. (to do this, use the
CDate function which converts a string to a date if it can)

COLUMN 1
FIELD ROW: [MyDateField]
CRITERIA ROW: >= CDate("01 " & [Enter Month to Run Query for (eg Nov 2009)])

COLUMN 2
FIELD ROW: [MyDateField]
CRITERIA ROW: <DateAdd("m",1,CDate("01 " & [Enter Month to Run Query for
(eg Nov 2009)])))

Be careful to use the same spelling for the parameter in each column. Else
you will be asked twice for the month/year.

This, obviously, is not the only way to do this, but it gives you a couple
of things to try.Alternate ways of do this are using a Form which allows you
to specify the month/year and click a button to run the query. The
functionality behind the button would set some global variables with the two
different dates, and the query would use a function to reference the
variables. In such an instance the WHERE clause of the query would be
something like: [MyDateField] >= gfuncDateFrom() and
[MyDateField]<gfuncDateTo().
--
B.

Live Long and Prosper...

I'd like to query date by the month. For instance keying in "Nov 2009" would
bring up all the dates of that month.

The date field is formatted as a short date, for example 11/1/2009.

Thanks in advance,
Ellen