Combo Boxes Make Up Date...but only for this year

  • Thread starter LeslieJ via AccessMonster.com
  • Start date
L

LeslieJ via AccessMonster.com

I have a form that counts the number of records created each month. I have a
combo box that has a list of years, 1;2007;2;2008;3;2009;4;2010. I have a
combo box that lists the months,
1;"January";2;"February";3;"March";4;"April";5;"May";6;"June";7;"July";8;"August";9;"September";10;"October";11;"November";12;"December".


After the month combo box is updated, there are two hidden text boxes that
are updated to be the first of the month and the last of the month.
Code is:
Me.txtStartDate = DateSerial(Year(), Me.cboMonth.Column(0), 1)
Me.txtEndDate = DateSerial(Year(), Me.cboMonth.Column(0) + 1, 0)

However, now that it is the new year I realized my super plan failed :eek:) The
year in the code obviously takes this year, and doesn't take into account the
year combo box selection.

How can I make it acknowledge the option selected in the year combo box and
concactenate it with the date serial code?
 
D

Douglas J. Steele

What's Year()?

The built-in Year function requires an argument.

Have you tried

Me.txtStartDate = DateSerial(Me.cboYear.Column(1), Me.cboMonth.Column(0), 1)
Me.txtEndDate = DateSerial(Me.cboYear.Column(1), Me.cboMonth.Column(0) + 1,
0)
 
L

LeslieJ via AccessMonster.com

When I try that the txtStartDate = 1905-02-01 and the txtEndDate = 1905-02-28
What's Year()?

The built-in Year function requires an argument.

Have you tried

Me.txtStartDate = DateSerial(Me.cboYear.Column(1), Me.cboMonth.Column(0), 1)
Me.txtEndDate = DateSerial(Me.cboYear.Column(1), Me.cboMonth.Column(0) + 1,
0)
I have a form that counts the number of records created each month. I have
a
[quoted text clipped - 17 lines]
and
concactenate it with the date serial code?
 
D

Douglas J. Steele

That doesn't make sense!

If you go to the Immediate Window (Ctrl-G), type

?Forms![NameOfTheForm]!cboYear.Column(1)

and hit Enter, what appears underneath?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


LeslieJ via AccessMonster.com said:
When I try that the txtStartDate = 1905-02-01 and the txtEndDate =
1905-02-28
What's Year()?

The built-in Year function requires an argument.

Have you tried

Me.txtStartDate = DateSerial(Me.cboYear.Column(1), Me.cboMonth.Column(0),
1)
Me.txtEndDate = DateSerial(Me.cboYear.Column(1), Me.cboMonth.Column(0) +
1,
0)
I have a form that counts the number of records created each month. I
have
a
[quoted text clipped - 17 lines]
and
concactenate it with the date serial code?
 
L

LeslieJ via AccessMonster.com

I didn't think it made sense - because I had tried that too before I posted,
but I tried it again because I thought maybe I did something wrong the first
time.

When I entered the information into the Immediate Window, I got a run time
error when I pressed enter. It is:
'2186': This property isn't available in design view.

This is exactly what I typed in:
?Forms![frmQAM'sMonthly]!cboYear.Column(1)

I don't understand, the value lists for each of the combo boxes are exactly
as I posted because I copied and pasted them into my first message.
That doesn't make sense!

If you go to the Immediate Window (Ctrl-G), type

?Forms![NameOfTheForm]!cboYear.Column(1)

and hit Enter, what appears underneath?
When I try that the txtStartDate = 1905-02-01 and the txtEndDate =
1905-02-28
[quoted text clipped - 17 lines]
 
D

Douglas J. Steele

You'll get that message if the form is open in Design view as opposed to
Normal view.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LeslieJ via AccessMonster.com said:
I didn't think it made sense - because I had tried that too before I
posted,
but I tried it again because I thought maybe I did something wrong the
first
time.

When I entered the information into the Immediate Window, I got a run time
error when I pressed enter. It is:
'2186': This property isn't available in design view.

This is exactly what I typed in:
?Forms![frmQAM'sMonthly]!cboYear.Column(1)

I don't understand, the value lists for each of the combo boxes are
exactly
as I posted because I copied and pasted them into my first message.
That doesn't make sense!

If you go to the Immediate Window (Ctrl-G), type

?Forms![NameOfTheForm]!cboYear.Column(1)

and hit Enter, what appears underneath?
When I try that the txtStartDate = 1905-02-01 and the txtEndDate =
1905-02-28
[quoted text clipped - 17 lines]
and
concactenate it with the date serial code?
 
L

LeslieJ via AccessMonster.com

I am so very sorry, thank you for your help and patience.

I tried it again, and I put in the year 2008 into the year combo box. I put
in February in the month combo box.
The two text boxes said the same as before 1905-02-01 and 1905-02-28.

Then when I put it in the immediate window it said it's getting the year 2008.

That doesn't make sense!

If you go to the Immediate Window (Ctrl-G), type

?Forms![NameOfTheForm]!cboYear.Column(1)

and hit Enter, what appears underneath?
When I try that the txtStartDate = 1905-02-01 and the txtEndDate =
1905-02-28
[quoted text clipped - 17 lines]
 
D

Douglas J. Steele

I just mocked up a form and it works fine for me!

Immediately before the lines of code

Me.txtStartDate = DateSerial(Me.cboYear.Column(1), Me.cboMonth.Column(0), 1)
Me.txtEndDate = DateSerial(Me.cboYear.Column(1), Me.cboMonth.Column(0) + 1,
0)

put

MsgBox "Year " & Me.cboYear.Column(1) & _
", Month " & Me.cboMonth.Column(0)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LeslieJ via AccessMonster.com said:
I am so very sorry, thank you for your help and patience.

I tried it again, and I put in the year 2008 into the year combo box. I
put
in February in the month combo box.
The two text boxes said the same as before 1905-02-01 and 1905-02-28.

Then when I put it in the immediate window it said it's getting the year
2008.

That doesn't make sense!

If you go to the Immediate Window (Ctrl-G), type

?Forms![NameOfTheForm]!cboYear.Column(1)

and hit Enter, what appears underneath?
When I try that the txtStartDate = 1905-02-01 and the txtEndDate =
1905-02-28
[quoted text clipped - 17 lines]
and
concactenate it with the date serial code?
 
L

LeslieJ via AccessMonster.com

Thankyou so very very much Douglas!!!! It works now! It's fabulous!
I just mocked up a form and it works fine for me!

Immediately before the lines of code

Me.txtStartDate = DateSerial(Me.cboYear.Column(1), Me.cboMonth.Column(0), 1)
Me.txtEndDate = DateSerial(Me.cboYear.Column(1), Me.cboMonth.Column(0) + 1,
0)

put

MsgBox "Year " & Me.cboYear.Column(1) & _
", Month " & Me.cboMonth.Column(0)
I am so very sorry, thank you for your help and patience.
[quoted text clipped - 19 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top