Date Help

[Guest]

I have a date field that I would somehow like to break down to just a month.
The reason I would like to do this is so that when I run my monthly report a
form opens and gives me a combo box with a list of the 12 months, and then
the after update function would then run the report on the month that I have
selected. Can someone help?

 

[Al Campagna]

Ryan,
I would think you'd want the year too... next year, the month will need a year to
reterieve the correct reocords.
Month(YourDateField) 'yields integer (1-12)
Year(YourDateField) 'yields integer (2006, 2007)
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[BruceM]

To expand on what Al wrote, it sounds as if you are using an unbound form to
set the parameters. If so, I will call the form frmChoose, and the combo
box cboPickMonth. I will also assume there is a combo box on the form to
select the year. I will call that cboPickYear. The row source for
cboPickMonth could be based on a table that contains a number in the first
(hidden column) and the corresponding month in the second (visible) column.
The year combo box could contain a listing of years. Add two unbound text
boxes (hidden, if you like) to the form: txtStart and txtEnd. The After
Update event for cboPickMonth would be:

Me.txtStart = DateSerial(Year(Date), Me.cboMonth, 1)
Me.txtEnd = DateSerial(Year(Date), Me.cboMonth + 1, 0)

The criteria for the date field in the query could be:
Between [Forms]![frmChoose]![txtStart] And [Forms]![frmChoose]![txtEnd]

 

[Guest]

Is there a way to make it show the name of the month instead of the number of
the month?

 

[Al Campagna]

Yes, but you'll still need the Month value to properly sort your records 1-12.
Format(Year([YourDate],"mmm")
yields 3 character Month (Jan, Feb, Mar... etc)

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

How would I make it show the name of the month instead of the number of the
month?

To expand on what Al wrote, it sounds as if you are using an unbound form to
set the parameters. If so, I will call the form frmChoose, and the combo
box cboPickMonth. I will also assume there is a combo box on the form to
select the year. I will call that cboPickYear. The row source for
cboPickMonth could be based on a table that contains a number in the first
(hidden column) and the corresponding month in the second (visible) column.
The year combo box could contain a listing of years. Add two unbound text
boxes (hidden, if you like) to the form: txtStart and txtEnd. The After
Update event for cboPickMonth would be:

Me.txtStart = DateSerial(Year(Date), Me.cboMonth, 1)
Me.txtEnd = DateSerial(Year(Date), Me.cboMonth + 1, 0)

The criteria for the date field in the query could be:
Between [Forms]![frmChoose]![txtStart] And [Forms]![frmChoose]![txtEnd]

I have a date field that I would somehow like to break down to just a
month.
The reason I would like to do this is so that when I run my monthly report
a
form opens and gives me a combo box with a list of the 12 months, and then
the after update function would then run the report on the month that I
have
selected. Can someone help?

 

[BruceM]

As Al explained, you can Format the month to the desired format. Using
"mmm" will give you Jan, Feb, etc., while "mmmm" will give you January,
February, etc.
You can use the Month function as described to get the number value for the
month. Sort by the Month value, which can be in a hidden column in the
combo box, and have the name of the month in the visible column. I
confronted the same issue recently. If there is a way to sort directly by
the month name I have not been able to discover it.

How would I make it show the name of the month instead of the number of
the
month?

To expand on what Al wrote, it sounds as if you are using an unbound form
to
set the parameters. If so, I will call the form frmChoose, and the combo
box cboPickMonth. I will also assume there is a combo box on the form to
select the year. I will call that cboPickYear. The row source for
cboPickMonth could be based on a table that contains a number in the
first
(hidden column) and the corresponding month in the second (visible)
column.
The year combo box could contain a listing of years. Add two unbound
text
boxes (hidden, if you like) to the form: txtStart and txtEnd. The After
Update event for cboPickMonth would be:

Me.txtStart = DateSerial(Year(Date), Me.cboMonth, 1)
Me.txtEnd = DateSerial(Year(Date), Me.cboMonth + 1, 0)

The criteria for the date field in the query could be:
Between [Forms]![frmChoose]![txtStart] And [Forms]![frmChoose]![txtEnd]

I have a date field that I would somehow like to break down to just a
month.
The reason I would like to do this is so that when I run my monthly
report
a
form opens and gives me a combo box with a list of the 12 months, and
then
the after update function would then run the report on the month that I
have
selected. Can someone help?

 

[Guest]

Thank you for your help guys, I ended up making a date table, and then used a
subform to show the full date name, and hiding the bound column date #. Your
help was greatly apprieciated!