Pull year and month from date field using parameter query

[Guest]

I would like to create a parameter query that prompts the user to enter a
year and then a month and pull the data from a table that has a date field
called "Counseling Date" formatted using Medium Date. I can get it to work
by creating a parameter query asking for beginning and end dates, but I just
want to create a monthly report by typing in a month, or even better, by
using combo boxes to select the months and years. Which ever works better
and easier I'll use, though.

 

[Allen Browne]

Try something like this:

PARAMETERS TheYear Long, TheMonth Long;
SELECT Table1.[Counseling Date]
FROM Table1
WHERE (([Counseling Date] >= DateSerial(TheYear, TheMonth, 1))
And ([Counseling Date] < DateSerial(TheYear, TheMonth + 1, 1)));

The query asks for a year (enter 4-digits) and a month (enter 1 to 12.)
It then shows all records where the Counseling Date is in that month.

The WHERE clause is crafted so that:
a) It can use any index on the Counseling Date field, and
b) It includes records from the last day of the month, even if there is a
Time component in the field.

 

[Guest]

I'm relatively new to Access, so this may be a dumb question, but do I type

PARAMETERS TheYear Long, TheMonth Long;
SELECT Table1.[Counseling Date]
FROM Table1
WHERE (([Counseling Date] >= DateSerial(TheYear, TheMonth, 1))
And ([Counseling Date] < DateSerial(TheYear, TheMonth + 1, 1)));

in the SQL view of the query? Thanks for your patience.

Try something like this:

PARAMETERS TheYear Long, TheMonth Long;
SELECT Table1.[Counseling Date]
FROM Table1
WHERE (([Counseling Date] >= DateSerial(TheYear, TheMonth, 1))
And ([Counseling Date] < DateSerial(TheYear, TheMonth + 1, 1)));

The query asks for a year (enter 4-digits) and a month (enter 1 to 12.)
It then shows all records where the Counseling Date is in that month.

The WHERE clause is crafted so that:
a) It can use any index on the Counseling Date field, and
b) It includes records from the last day of the month, even if there is a
Time component in the field.

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

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

I would like to create a parameter query that prompts the user to enter a
year and then a month and pull the data from a table that has a date field
called "Counseling Date" formatted using Medium Date. I can get it to
work
by creating a parameter query asking for beginning and end dates, but I
just
want to create a monthly report by typing in a month, or even better, by
using combo boxes to select the months and years. Which ever works better
and easier I'll use, though.

 

[Allen Browne]

Yep. That's the idea.

You can then see it in Design view.
(The parameters show up under Parameters on the Query menu.)

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

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

I'm relatively new to Access, so this may be a dumb question, but do I
type

PARAMETERS TheYear Long, TheMonth Long;
SELECT Table1.[Counseling Date]
FROM Table1
WHERE (([Counseling Date] >= DateSerial(TheYear, TheMonth, 1))
And ([Counseling Date] < DateSerial(TheYear, TheMonth + 1, 1)));

in the SQL view of the query? Thanks for your patience.

Try something like this:

PARAMETERS TheYear Long, TheMonth Long;
SELECT Table1.[Counseling Date]
FROM Table1
WHERE (([Counseling Date] >= DateSerial(TheYear, TheMonth, 1))
And ([Counseling Date] < DateSerial(TheYear, TheMonth + 1, 1)));

The query asks for a year (enter 4-digits) and a month (enter 1 to 12.)
It then shows all records where the Counseling Date is in that month.

The WHERE clause is crafted so that:
a) It can use any index on the Counseling Date field, and
b) It includes records from the last day of the month, even if there is a
Time component in the field.

I would like to create a parameter query that prompts the user to enter
a
year and then a month and pull the data from a table that has a date
field
called "Counseling Date" formatted using Medium Date. I can get it to
work
by creating a parameter query asking for beginning and end dates, but I
just
want to create a monthly report by typing in a month, or even better,
by
using combo boxes to select the months and years. Which ever works
better
and easier I'll use, though.

 

[Guest]

Also (sorry 'bout this), is there anyway that I can change the display that
pops up to read something like "Enter the Year (i.e. 2007)" and "Enter the
number of the Month (i.e. "3" for March)"? I tried changing what was in the
brackets []for "TheYear" and "TheMonth", but that didn't work.

Try something like this:

PARAMETERS TheYear Long, TheMonth Long;
SELECT Table1.[Counseling Date]
FROM Table1
WHERE (([Counseling Date] >= DateSerial(TheYear, TheMonth, 1))
And ([Counseling Date] < DateSerial(TheYear, TheMonth + 1, 1)));

The query asks for a year (enter 4-digits) and a month (enter 1 to 12.)
It then shows all records where the Counseling Date is in that month.

The WHERE clause is crafted so that:
a) It can use any index on the Counseling Date field, and
b) It includes records from the last day of the month, even if there is a
Time component in the field.

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

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

I would like to create a parameter query that prompts the user to enter a
year and then a month and pull the data from a table that has a date field
called "Counseling Date" formatted using Medium Date. I can get it to
work
by creating a parameter query asking for beginning and end dates, but I
just
want to create a monthly report by typing in a month, or even better, by
using combo boxes to select the months and years. Which ever works better
and easier I'll use, though.

 

[Allen Browne]

Replace both occurances of:
TheYear
with:
[Enter the Year (i.e. 2007]

Likewise:
[Enter the number of the Month (i.e. '3' for March)]
for:
TheMonth

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

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

Also (sorry 'bout this), is there anyway that I can change the display
that
pops up to read something like "Enter the Year (i.e. 2007)" and "Enter the
number of the Month (i.e. "3" for March)"? I tried changing what was in
the
brackets []for "TheYear" and "TheMonth", but that didn't work.

Try something like this:

PARAMETERS TheYear Long, TheMonth Long;
SELECT Table1.[Counseling Date]
FROM Table1
WHERE (([Counseling Date] >= DateSerial(TheYear, TheMonth, 1))
And ([Counseling Date] < DateSerial(TheYear, TheMonth + 1, 1)));

The query asks for a year (enter 4-digits) and a month (enter 1 to 12.)
It then shows all records where the Counseling Date is in that month.

The WHERE clause is crafted so that:
a) It can use any index on the Counseling Date field, and
b) It includes records from the last day of the month, even if there is a
Time component in the field.

I would like to create a parameter query that prompts the user to enter
a
year and then a month and pull the data from a table that has a date
field
called "Counseling Date" formatted using Medium Date. I can get it to
work
by creating a parameter query asking for beginning and end dates, but I
just
want to create a monthly report by typing in a month, or even better,
by
using combo boxes to select the months and years. Which ever works
better
and easier I'll use, though.

 

[Guest]

I tried that, and I get an error: " is not a valid name. Make sure that it
does not include invalid characters or punctuation and that it is not too
long.

Is it too long???
Do I need to come up with something simpler to say?

Replace both occurances of:
TheYear
with:
[Enter the Year (i.e. 2007]

Likewise:
[Enter the number of the Month (i.e. '3' for March)]
for:
TheMonth

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

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

Also (sorry 'bout this), is there anyway that I can change the display
that
pops up to read something like "Enter the Year (i.e. 2007)" and "Enter the
number of the Month (i.e. "3" for March)"? I tried changing what was in
the
brackets []for "TheYear" and "TheMonth", but that didn't work.

Try something like this:

PARAMETERS TheYear Long, TheMonth Long;
SELECT Table1.[Counseling Date]
FROM Table1
WHERE (([Counseling Date] >= DateSerial(TheYear, TheMonth, 1))
And ([Counseling Date] < DateSerial(TheYear, TheMonth + 1, 1)));

The query asks for a year (enter 4-digits) and a month (enter 1 to 12.)
It then shows all records where the Counseling Date is in that month.

The WHERE clause is crafted so that:
a) It can use any index on the Counseling Date field, and
b) It includes records from the last day of the month, even if there is a
Time component in the field.

I would like to create a parameter query that prompts the user to enter
a
year and then a month and pull the data from a table that has a date
field
called "Counseling Date" formatted using Medium Date. I can get it to
work
by creating a parameter query asking for beginning and end dates, but I
just
want to create a monthly report by typing in a month, or even better,
by
using combo boxes to select the months and years. Which ever works
better
and easier I'll use, though.

 

[Guest]

I got it to work by removing the punctuation and parantheses and just using
text. What is the proper syntax for utilizing the punctuation?

I tried that, and I get an error: " is not a valid name. Make sure that it
does not include invalid characters or punctuation and that it is not too
long.

Is it too long???
Do I need to come up with something simpler to say?

Replace both occurances of:
TheYear
with:
[Enter the Year (i.e. 2007]

Likewise:
[Enter the number of the Month (i.e. '3' for March)]
for:
TheMonth

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

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

Also (sorry 'bout this), is there anyway that I can change the display
that
pops up to read something like "Enter the Year (i.e. 2007)" and "Enter the
number of the Month (i.e. "3" for March)"? I tried changing what was in
the
brackets []for "TheYear" and "TheMonth", but that didn't work.

:

Try something like this:

PARAMETERS TheYear Long, TheMonth Long;
SELECT Table1.[Counseling Date]
FROM Table1
WHERE (([Counseling Date] >= DateSerial(TheYear, TheMonth, 1))
And ([Counseling Date] < DateSerial(TheYear, TheMonth + 1, 1)));

The query asks for a year (enter 4-digits) and a month (enter 1 to 12.)
It then shows all records where the Counseling Date is in that month.

The WHERE clause is crafted so that:
a) It can use any index on the Counseling Date field, and
b) It includes records from the last day of the month, even if there is a
Time component in the field.

I would like to create a parameter query that prompts the user to enter
a
year and then a month and pull the data from a table that has a date
field
called "Counseling Date" formatted using Medium Date. I can get it to
work
by creating a parameter query asking for beginning and end dates, but I
just
want to create a monthly report by typing in a month, or even better,
by
using combo boxes to select the months and years. Which ever works
better
and easier I'll use, though.

 

[Allen Browne]

The square brackets allow you to use some non-standard characters, such as
spaces, or beginning with a number.

Check help under Specifications for valid names.

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

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

I got it to work by removing the punctuation and parantheses and just using
text. What is the proper syntax for utilizing the punctuation?

I tried that, and I get an error: " is not a valid name. Make sure that
it
does not include invalid characters or punctuation and that it is not too
long.

Is it too long???
Do I need to come up with something simpler to say?

Replace both occurances of:
TheYear
with:
[Enter the Year (i.e. 2007]

Likewise:
[Enter the number of the Month (i.e. '3' for March)]
for:
TheMonth

Also (sorry 'bout this), is there anyway that I can change the
display
that
pops up to read something like "Enter the Year (i.e. 2007)" and
"Enter the
number of the Month (i.e. "3" for March)"? I tried changing what was
in
the
brackets []for "TheYear" and "TheMonth", but that didn't work.

:

Try something like this:

PARAMETERS TheYear Long, TheMonth Long;
SELECT Table1.[Counseling Date]
FROM Table1
WHERE (([Counseling Date] >= DateSerial(TheYear, TheMonth, 1))
And ([Counseling Date] < DateSerial(TheYear, TheMonth + 1, 1)));

The query asks for a year (enter 4-digits) and a month (enter 1 to
12.)
It then shows all records where the Counseling Date is in that
month.

The WHERE clause is crafted so that:
a) It can use any index on the Counseling Date field, and
b) It includes records from the last day of the month, even if there
is a
Time component in the field.

I would like to create a parameter query that prompts the user to
enter
a
year and then a month and pull the data from a table that has a
date
field
called "Counseling Date" formatted using Medium Date. I can get
it to
work
by creating a parameter query asking for beginning and end dates,
but I
just
want to create a monthly report by typing in a month, or even
better,
by
using combo boxes to select the months and years. Which ever
works
better
and easier I'll use, though.