Filtering query by month and year

[cherman]

Hi there. I have an interesting query issue that I cannot figure out.

I have to pull records that fall between a beginning month/year and an
ending month/year.

The records do not have a date ref. There is a month field (like "January")
and there is a year field (like "2006").

I have a form that sets the criteria for my report. There is a Beginning and
Ending Month and a Beginning and Ending year fields.

Users can choose multiple months / years (like between October 2006 to
January 2007 OR October 2006 to December 2006 OR October 2006 to October
2006).

I did have it set up working with only 1 year for the criteria, but adding
the ending year is killing me.

Any suggestions would be greatly appreciated!

Thanks,
Clint

 

[Jeff Boyce]

Clint

Add a couple fields to your query...

(I'm assuming you have some kind of a date/time field that you want to use
this selection process against...)

One new field (name it as you wish, or let Access call it "Expr1"):
Expr1: Month([YourDateTimeField])

The second new field ...:
Expr2: Year([YourDateTimeField])

Then use the Month and Year selections made in the form as your selection
criteria beneath these two new fields.

You might need to use the Month # instead of the Month name...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[cherman]

Thanks for your reply. I have taken your suggestions into consideration but I
am not getting my desired results. I have added my SQL below for your review.
For my test of between January and December and between 2007 and 2007
(basically all data in 1 year), I get Jan records, but not Feb records. These
are the only records in the table for 2007 and all should return. The results
get worse when I try Jan - Dec, 2006 - 2007.

SQL:

SELECT BillingsID, Year, Month, OCTOrPCRCharge, RecurringCharge, SubTotal,
Year([DateStamp]) AS Expr2, Month([DateStamp]) AS Expr1
FROM tblBillingsAmounts LEFT JOIN tblMonths ON tblBillingsAmounts.Month =
tblMonths.MonthText
WHERE (((Year([DateStamp])) Between
[Forms]![frmBillingsRptCriteria]![cboBeginningYear] And
[Forms]![frmBillingsRptCriteria]![cboEndingYear]) AND ((Month([DateStamp]))
Between [Forms]![frmBillingsRptCriteria]![txtBeginningSortOrder] And
[Forms]![frmBillingsRptCriteria]![txtEndingSortOrder]))
ORDER BY tblBillingsAmounts.BillingsID;

Thanks again!
Clint

Clint

Add a couple fields to your query...

(I'm assuming you have some kind of a date/time field that you want to use
this selection process against...)

One new field (name it as you wish, or let Access call it "Expr1"):
Expr1: Month([YourDateTimeField])

The second new field ...:
Expr2: Year([YourDateTimeField])

Then use the Month and Year selections made in the form as your selection
criteria beneath these two new fields.

You might need to use the Month # instead of the Month name...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi there. I have an interesting query issue that I cannot figure out.

I have to pull records that fall between a beginning month/year and an
ending month/year.

The records do not have a date ref. There is a month field (like
"January")
and there is a year field (like "2006").

I have a form that sets the criteria for my report. There is a Beginning
and
Ending Month and a Beginning and Ending year fields.

Users can choose multiple months / years (like between October 2006 to
January 2007 OR October 2006 to December 2006 OR October 2006 to October
2006).

I did have it set up working with only 1 year for the criteria, but adding
the ending year is killing me.

Any suggestions would be greatly appreciated!

Thanks,
Clint

 

[Jeff Boyce]

Are you using the month number, or the month name?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for your reply. I have taken your suggestions into consideration
but I
am not getting my desired results. I have added my SQL below for your
review.
For my test of between January and December and between 2007 and 2007
(basically all data in 1 year), I get Jan records, but not Feb records.
These
are the only records in the table for 2007 and all should return. The
results
get worse when I try Jan - Dec, 2006 - 2007.

SQL:

SELECT BillingsID, Year, Month, OCTOrPCRCharge, RecurringCharge, SubTotal,
Year([DateStamp]) AS Expr2, Month([DateStamp]) AS Expr1
FROM tblBillingsAmounts LEFT JOIN tblMonths ON tblBillingsAmounts.Month =
tblMonths.MonthText
WHERE (((Year([DateStamp])) Between
[Forms]![frmBillingsRptCriteria]![cboBeginningYear] And
[Forms]![frmBillingsRptCriteria]![cboEndingYear]) AND
((Month([DateStamp]))
Between [Forms]![frmBillingsRptCriteria]![txtBeginningSortOrder] And
[Forms]![frmBillingsRptCriteria]![txtEndingSortOrder]))
ORDER BY tblBillingsAmounts.BillingsID;

Thanks again!
Clint

Clint

Add a couple fields to your query...

(I'm assuming you have some kind of a date/time field that you want to
use
this selection process against...)

One new field (name it as you wish, or let Access call it "Expr1"):
Expr1: Month([YourDateTimeField])

The second new field ...:
Expr2: Year([YourDateTimeField])

Then use the Month and Year selections made in the form as your selection
criteria beneath these two new fields.

You might need to use the Month # instead of the Month name...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi there. I have an interesting query issue that I cannot figure out.

I have to pull records that fall between a beginning month/year and an
ending month/year.

The records do not have a date ref. There is a month field (like
"January")
and there is a year field (like "2006").

I have a form that sets the criteria for my report. There is a
Beginning
and
Ending Month and a Beginning and Ending year fields.

Users can choose multiple months / years (like between October 2006 to
January 2007 OR October 2006 to December 2006 OR October 2006 to
October
2006).

I did have it set up working with only 1 year for the criteria, but
adding
the ending year is killing me.

Any suggestions would be greatly appreciated!

Thanks,
Clint

 

[cherman]

Hi there. I am using the month #.

Clint

Are you using the month number, or the month name?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for your reply. I have taken your suggestions into consideration
but I
am not getting my desired results. I have added my SQL below for your
review.
For my test of between January and December and between 2007 and 2007
(basically all data in 1 year), I get Jan records, but not Feb records.
These
are the only records in the table for 2007 and all should return. The
results
get worse when I try Jan - Dec, 2006 - 2007.

SQL:

SELECT BillingsID, Year, Month, OCTOrPCRCharge, RecurringCharge, SubTotal,
Year([DateStamp]) AS Expr2, Month([DateStamp]) AS Expr1
FROM tblBillingsAmounts LEFT JOIN tblMonths ON tblBillingsAmounts.Month =
tblMonths.MonthText
WHERE (((Year([DateStamp])) Between
[Forms]![frmBillingsRptCriteria]![cboBeginningYear] And
[Forms]![frmBillingsRptCriteria]![cboEndingYear]) AND
((Month([DateStamp]))
Between [Forms]![frmBillingsRptCriteria]![txtBeginningSortOrder] And
[Forms]![frmBillingsRptCriteria]![txtEndingSortOrder]))
ORDER BY tblBillingsAmounts.BillingsID;

Thanks again!
Clint

Clint

Add a couple fields to your query...

(I'm assuming you have some kind of a date/time field that you want to
use
this selection process against...)

One new field (name it as you wish, or let Access call it "Expr1"):
Expr1: Month([YourDateTimeField])

The second new field ...:
Expr2: Year([YourDateTimeField])

Then use the Month and Year selections made in the form as your selection
criteria beneath these two new fields.

You might need to use the Month # instead of the Month name...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi there. I have an interesting query issue that I cannot figure out.

I have to pull records that fall between a beginning month/year and an
ending month/year.

The records do not have a date ref. There is a month field (like
"January")
and there is a year field (like "2006").

I have a form that sets the criteria for my report. There is a
Beginning
and
Ending Month and a Beginning and Ending year fields.

Users can choose multiple months / years (like between October 2006 to
January 2007 OR October 2006 to December 2006 OR October 2006 to
October
2006).

I did have it set up working with only 1 year for the criteria, but
adding
the ending year is killing me.

Any suggestions would be greatly appreciated!

Thanks,
Clint

 

[Jeff Boyce]

So I'm confused. If you are asking for [Beginning Month #] and [Ending
Month #], then using those value as criteria for selection values of Expr1:
Month([YourDateField]), how are you not getting months between 1 and 12,
like, say, February?

If you hard-code the month #s into the selection criteria:
Between 1 And 12
does that work?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi there. I am using the month #.

Clint

Are you using the month number, or the month name?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for your reply. I have taken your suggestions into consideration
but I
am not getting my desired results. I have added my SQL below for your
review.
For my test of between January and December and between 2007 and 2007
(basically all data in 1 year), I get Jan records, but not Feb records.
These
are the only records in the table for 2007 and all should return. The
results
get worse when I try Jan - Dec, 2006 - 2007.

SQL:

SELECT BillingsID, Year, Month, OCTOrPCRCharge, RecurringCharge,
SubTotal,
Year([DateStamp]) AS Expr2, Month([DateStamp]) AS Expr1
FROM tblBillingsAmounts LEFT JOIN tblMonths ON tblBillingsAmounts.Month
=
tblMonths.MonthText
WHERE (((Year([DateStamp])) Between
[Forms]![frmBillingsRptCriteria]![cboBeginningYear] And
[Forms]![frmBillingsRptCriteria]![cboEndingYear]) AND
((Month([DateStamp]))
Between [Forms]![frmBillingsRptCriteria]![txtBeginningSortOrder] And
[Forms]![frmBillingsRptCriteria]![txtEndingSortOrder]))
ORDER BY tblBillingsAmounts.BillingsID;

Thanks again!
Clint


:

Clint

Add a couple fields to your query...

(I'm assuming you have some kind of a date/time field that you want to
use
this selection process against...)

One new field (name it as you wish, or let Access call it "Expr1"):
Expr1: Month([YourDateTimeField])

The second new field ...:
Expr2: Year([YourDateTimeField])

Then use the Month and Year selections made in the form as your
selection
criteria beneath these two new fields.

You might need to use the Month # instead of the Month name...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi there. I have an interesting query issue that I cannot figure
out.

I have to pull records that fall between a beginning month/year and
an
ending month/year.

The records do not have a date ref. There is a month field (like
"January")
and there is a year field (like "2006").

I have a form that sets the criteria for my report. There is a
Beginning
and
Ending Month and a Beginning and Ending year fields.

Users can choose multiple months / years (like between October 2006
to
January 2007 OR October 2006 to December 2006 OR October 2006 to
October
2006).

I did have it set up working with only 1 year for the criteria, but
adding
the ending year is killing me.

Any suggestions would be greatly appreciated!

Thanks,
Clint

 

[raskew via AccessMonster.com]

Hi -

Lack of date/time data type fields (as pointed out in the first post) makes
this messier than it need be. You may, however, use the DateValue() function
to create calculated fields which you may then specify criteria to filter a
date range.

Here are some examples from the debug (immediate) window using the DateValue()
function. Note that if it's fed just a month and year, it will automatically
return the 1st as Day (this isn't described in the help file, but it does
work like that).
**********************************************
'fields as integers
mymonth = 2
myyear = 2007
x = datevalue(mymonth & "/" & myyear)
2/1/07

'fields as strings1
mymonth = "2"
Myyear = "2007"
? datevalue(mymonth & "/" & myyear)
2/1/07

'fields as strings2
mymonth = "february"
myyear = "2007"
? datevalue(mymonth & "/" & myyear)
2/1/07

'fields as string / integer
mymonth = "february"
myyear = 2007
? datevalue(mymonth & "/" & myyear)
2/1/07
********************************************
Try creating two calculated fields in your query (sdte and edte), using the
examples shown above, then filtering on those fields.

HTH - Bob

So I'm confused. If you are asking for [Beginning Month #] and [Ending
Month #], then using those value as criteria for selection values of Expr1:
Month([YourDateField]), how are you not getting months between 1 and 12,
like, say, February?

If you hard-code the month #s into the selection criteria:
Between 1 And 12
does that work?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi there. I am using the month #.

[quoted text clipped - 92 lines]