How do I select a month without using a 'Between' clause?

  • Thread starter Thread starter ZBC
  • Start date Start date
Z

ZBC

There is a date field (mm/dd/yyyy) in a table which is the basis of
several reports.
I wish to have the user easily select a month and year for a report
without have to type something like "Between #9/1/03# And #9/30/03#" in
a textbox.
It would prefer using something like comboboxes, so the user does not
have to know that there are only 30 days in September, etc.
Suggestions appreciated.
 
YOu could use a combo box with the month number and name as columns. Then in
the query, add a criteria like:
WHERE Month([MyDateField]) = Forms!frmA!cboMonth
Be cautious though since this will select all years.
 
Since my table contains several years, I guess this will not work for me.
Would 2 combo boxes (one for month and one for year) work?
 
Afterthought ... why did you say a "combo box with the month number and
name" ... do I need both?
 
Dear Linda? Bob?:

Date fields, if they are a date/time datatype, are not mm/dd/yyyy or
any other readable format. They are a binary numeric value not
"readable" to humans. That can be VERY important to keep in mind.

They also contain a time portion, not just a date. However, in many
applications, the time portion is zeroed, meaning midnight and at the
very beginning of the day.

There is a MONTH() function that will extract the number of each month
(1-12) which might be of use, although you would probably need to
filter for the year as well. Otherwise you would get September 2002,
September 2001, etc. as well as September 2003.

You might want to have two combo boxes, one for the name of a month
and one for the year. Use a hidden column for the month combo so you
can reference the month number 1-12 rather than the name, unless you
want to present the user with month numbers instead of month names.

The user could then enter or pick a month and year, and your query
could filter by those:

WHERE MONTH(YourDate) = [Forms]![YourForm]![YourMonth]
AND YEAR(YourDate] = [Forms]![YourForm]![YourYear]

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

This is the Bob portion of LindaBob ...
If I understand you correctly, I can use comboboxes on a form to
manipulate a query for my reports? Is this correct?
I want to keep it simple. I was planning to use a form to select my
reports, but I had gotten the feeling that my best approach might be to
use an OpenReport Method in code. I am obviously not over-skilled at
Access (did some work on VB6 about a year ago) ... mostly, I want to
keep it simple for both me and the user.

If I use the design view of a query, what would my entries look like?

I do appreciate the time you have spent explaining this!
Bob


Tom said:
Dear Linda? Bob?:

Date fields, if they are a date/time datatype, are not mm/dd/yyyy or
any other readable format. They are a binary numeric value not
"readable" to humans. That can be VERY important to keep in mind.

They also contain a time portion, not just a date. However, in many
applications, the time portion is zeroed, meaning midnight and at the
very beginning of the day.

There is a MONTH() function that will extract the number of each month
(1-12) which might be of use, although you would probably need to
filter for the year as well. Otherwise you would get September 2002,
September 2001, etc. as well as September 2003.

You might want to have two combo boxes, one for the name of a month
and one for the year. Use a hidden column for the month combo so you
can reference the month number 1-12 rather than the name, unless you
want to present the user with month numbers instead of month names.

The user could then enter or pick a month and year, and your query
could filter by those:

WHERE MONTH(YourDate) = [Forms]![YourForm]![YourMonth]
AND YEAR(YourDate] = [Forms]![YourForm]![YourYear]

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

There is a date field (mm/dd/yyyy) in a table which is the basis of
several reports.
I wish to have the user easily select a month and year for a report
without have to type something like "Between #9/1/03# And #9/30/03#" in
a textbox.
It would prefer using something like comboboxes, so the user does not
have to know that there are only 30 days in September, etc.
Suggestions appreciated.
Click to expand...
Click to expand...
 
Sure. It is probably a bit more efficient (espcially if
you have large number of Records in the Table and
YourDateField is indexed) to use the criteria:

....
WHERE YourDateField
BETWEEN DateSerial(cboYear, cboMonth, 1)
AND DateSerial(cboYear, cboMonth + 1, 0)

HTH
Van T. Dinh
MVP (Access)
 
If you have a form with a command button to open the report, consider adding a couple combo boxes with the Year and Month as Tom described. Your code to open the report might look like:

Dim strWhere as String
strWhere = "Year([DateField]) = " & Me.cboYear & _
" AND Month([DateField]) = " & Me.cboMonth
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

--
Duane Hookom
MS Access MVP


Tom,

This is the Bob portion of LindaBob ...
If I understand you correctly, I can use comboboxes on a form to manipulate a query for my reports? Is this correct?
I want to keep it simple. I was planning to use a form to select my reports, but I had gotten the feeling that my best approach might be to use an OpenReport Method in code. I am obviously not over-skilled at Access (did some work on VB6 about a year ago) ... mostly, I want to keep it simple for both me and the user.

If I use the design view of a query, what would my entries look like?

I do appreciate the time you have spent explaining this!
Bob


Tom Ellison wrote:

Dear Linda? Bob?:

Date fields, if they are a date/time datatype, are not mm/dd/yyyy or
any other readable format. They are a binary numeric value not
"readable" to humans. That can be VERY important to keep in mind.

They also contain a time portion, not just a date. However, in many
applications, the time portion is zeroed, meaning midnight and at the
very beginning of the day.

There is a MONTH() function that will extract the number of each month
(1-12) which might be of use, although you would probably need to
filter for the year as well. Otherwise you would get September 2002,
September 2001, etc. as well as September 2003.

You might want to have two combo boxes, one for the name of a month
and one for the year. Use a hidden column for the month combo so you
can reference the month number 1-12 rather than the name, unless you
want to present the user with month numbers instead of month names.

The user could then enter or pick a month and year, and your query
could filter by those:

WHERE MONTH(YourDate) = [Forms]![YourForm]![YourMonth]
AND YEAR(YourDate] = [Forms]![YourForm]![YourYear]

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

There is a date field (mm/dd/yyyy) in a table which is the basis of
several reports.
I wish to have the user easily select a month and year for a report
without have to type something like "Between #9/1/03# And #9/30/03#" in
a textbox.
It would prefer using something like comboboxes, so the user does not
have to know that there are only 30 days in September, etc.
Suggestions appreciated.
 
I believe I understand! ... Thanks.

I am trying also to understand the design view of queries ... what would
the entries be in design view of a query be to do the same thing?


Duane said:
If you have a form with a command button to open the report, consider
adding a couple combo boxes with the Year and Month as Tom described.
Your code to open the report might look like:

Dim strWhere as String
strWhere = "Year([DateField]) = " & Me.cboYear & _
" AND Month([DateField]) = " & Me.cboMonth
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

--
Duane Hookom
MS Access MVP



"ZBC" <[email protected]
Tom,

This is the Bob portion of LindaBob ...
If I understand you correctly, I can use comboboxes on a form to
manipulate a query for my reports? Is this correct?
I want to keep it simple. I was planning to use a form to select
my reports, but I had gotten the feeling that my best approach
might be to use an OpenReport Method in code. I am obviously not
over-skilled at Access (did some work on VB6 about a year ago) ...
mostly, I want to keep it simple for both me and the user.

If I use the design view of a query, what would my entries look like?

I do appreciate the time you have spent explaining this!
Bob


Tom said:
Dear Linda? Bob?:

Date fields, if they are a date/time datatype, are not mm/dd/yyyy or
any other readable format. They are a binary numeric value not
"readable" to humans. That can be VERY important to keep in mind.

They also contain a time portion, not just a date. However, in many
applications, the time portion is zeroed, meaning midnight and at the
very beginning of the day.

There is a MONTH() function that will extract the number of each month
(1-12) which might be of use, although you would probably need to
filter for the year as well. Otherwise you would get September 2002,
September 2001, etc. as well as September 2003.

You might want to have two combo boxes, one for the name of a month
and one for the year. Use a hidden column for the month combo so you
can reference the month number 1-12 rather than the name, unless you
want to present the user with month numbers instead of month names.

The user could then enter or pick a month and year, and your query
could filter by those:

WHERE MONTH(YourDate) = [Forms]![YourForm]![YourMonth]
AND YEAR(YourDate] = [Forms]![YourForm]![YourYear]

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

There is a date field (mm/dd/yyyy) in a table which is the basis of
several reports.
I wish to have the user easily select a month and year for a report
without have to type something like "Between #9/1/03# And #9/30/03#" in
a textbox.
It would prefer using something like comboboxes, so the user does not
have to know that there are only 30 days in September, etc.
Suggestions appreciated.
Click to expand...
Click to expand...
Click to expand...
 
You could add two calculated columns in the queryYear([DateField]) and Month([DateField]). Then set the criteria under these to: Forms!frmA!cboYear and Forms!frmA!cboMonth.

--
Duane Hookom
MS Access MVP


I believe I understand! ... Thanks.

I am trying also to understand the design view of queries ... what would the entries be in design view of a query be to do the same thing?


Duane Hookom wrote:

If you have a form with a command button to open the report, consider adding a couple combo boxes with the Year and Month as Tom described. Your code to open the report might look like:

Dim strWhere as String
strWhere = "Year([DateField]) = " & Me.cboYear & _
" AND Month([DateField]) = " & Me.cboMonth
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

--
Duane Hookom
MS Access MVP


Tom,

This is the Bob portion of LindaBob ...
If I understand you correctly, I can use comboboxes on a form to manipulate a query for my reports? Is this correct?
I want to keep it simple. I was planning to use a form to select my reports, but I had gotten the feeling that my best approach might be to use an OpenReport Method in code. I am obviously not over-skilled at Access (did some work on VB6 about a year ago) ... mostly, I want to keep it simple for both me and the user.

If I use the design view of a query, what would my entries look like?

I do appreciate the time you have spent explaining this!
Bob


Tom Ellison wrote:

Dear Linda? Bob?:

Date fields, if they are a date/time datatype, are not mm/dd/yyyy or
any other readable format. They are a binary numeric value not
"readable" to humans. That can be VERY important to keep in mind.

They also contain a time portion, not just a date. However, in many
applications, the time portion is zeroed, meaning midnight and at the
very beginning of the day.

There is a MONTH() function that will extract the number of each month
(1-12) which might be of use, although you would probably need to
filter for the year as well. Otherwise you would get September 2002,
September 2001, etc. as well as September 2003.

You might want to have two combo boxes, one for the name of a month
and one for the year. Use a hidden column for the month combo so you
can reference the month number 1-12 rather than the name, unless you
want to present the user with month numbers instead of month names.

The user could then enter or pick a month and year, and your query
could filter by those:

WHERE MONTH(YourDate) = [Forms]![YourForm]![YourMonth]
AND YEAR(YourDate] = [Forms]![YourForm]![YourYear]

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

There is a date field (mm/dd/yyyy) in a table which is the basis of
several reports.
I wish to have the user easily select a month and year for a report
without have to type something like "Between #9/1/03# And #9/30/03#" in
a textbox.
It would prefer using something like comboboxes, so the user does not
have to know that there are only 30 days in September, etc.
Suggestions appreciated.
 
Back
Top