Selecting year & month

[Guest]

I currently have the following in my select query but I want to be able to
select a year as well. How would I incorporate that into my query? This is
what I have in there so far:

Between DateSerial(Year(Date()),[Enter Starting Month],1) And
DateSerial(Year(Date()),[Enter Ending Month]+1,0)

 

[John Spencer]

Add More parameters to get the year. But that is going to make this more
complex and tedious for the person using it.

You could ask them for the Start Date and End Date. If you need the entire
month then you can adjust it.

Between DateSerial( Year([Enter Start Date]),Month([Enter Start Date]),1)
And
DateSerial( Year([Enter EndDate]),Month([Enter EndDate])+1,0)

You could change the message a bit to [Enter date in start month]

Check out this article for a detailed discussion on using a form to get the
input.
http://www.fontstuff.com/access/acctut08.htm


A brief quote from an John Vinson (Access MVP) posting on using a form

You'll need to create a small unbound Form (let's call it frmCriteria) with
a Combo Box control (cboCrit) on it. Use the combo box wizard to select the
table for the selections, and be sure that the bound field of the combo is
the value you want to use as a criterion. Save this form.

Now use

=[Forms]![frmCriteria]![cboCrit]

as the criterion in your Query.

It's convenient to base a second Form or Report on the resulting query to
display the results; if you put a button on frmCriteria to launch that form
or report, the user can enter the criterion and view the results in one
simple operation!

Quoting John Vinson

 

[Guest]

Thanks for your help John, but is there a way for it just to ask them once
for the year instead of twice?

Add More parameters to get the year. But that is going to make this more
complex and tedious for the person using it.

You could ask them for the Start Date and End Date. If you need the entire
month then you can adjust it.

Between DateSerial( Year([Enter Start Date]),Month([Enter Start Date]),1)
And
DateSerial( Year([Enter EndDate]),Month([Enter EndDate])+1,0)

You could change the message a bit to [Enter date in start month]

Check out this article for a detailed discussion on using a form to get the
input.
http://www.fontstuff.com/access/acctut08.htm


A brief quote from an John Vinson (Access MVP) posting on using a form

You'll need to create a small unbound Form (let's call it frmCriteria) with
a Combo Box control (cboCrit) on it. Use the combo box wizard to select the
table for the selections, and be sure that the bound field of the combo is
the value you want to use as a criterion. Save this form.

Now use

=[Forms]![frmCriteria]![cboCrit]

as the criterion in your Query.

It's convenient to base a second Form or Report on the resulting query to
display the results; if you put a button on frmCriteria to launch that form
or report, the user can enter the criterion and view the results in one
simple operation!

Quoting John Vinson

I currently have the following in my select query but I want to be able to
select a year as well. How would I incorporate that into my query? This is
what I have in there so far:

Between DateSerial(Year(Date()),[Enter Starting Month],1) And
DateSerial(Year(Date()),[Enter Ending Month]+1,0)

 

[Michel Walsh]

HI,



Between DateSerial([Enter year] , [Enter Starting Month],1) And
DateSerial([Enter year] , [Enter Ending Month]+1,0)




should do, assuming it is the same year in both cases, else, use a different
name for the ending date.

Alternatively

Between DateSerial([Enter year] , [Enter Starting Month],1)
And
DateSerial( iif( Enter Ending Month] <= [Enter Starting Month], [Enter
year]+ 1, [Enter year] ) , [Enter Ending Month]+1,0)


which will add 1 to the year if the ending month is less of equal to the
starting month.



Hoping it may help,
Vanderghast, Access MVP

 

[Rick Brandt]

I currently have the following in my select query but I want to be
able to select a year as well. How would I incorporate that into my
query? This is what I have in there so far:

Between DateSerial(Year(Date()),[Enter Starting Month],1) And
DateSerial(Year(Date()),[Enter Ending Month]+1,0)

Between DateSerial([Enter Starting Year],[Enter Starting Month],1) And
DateSerial([Enter Ending Year],[Enter Ending Month]+1,0)