why the year function dosn't work in access 2013

i use a CDate  to get a date from a text value (i have a combo box that has two column one name ot the month and the other the number of the month) and i write a code like this
Dim a As Date
a = CDate(Me.MonthCombo & "/1/" & Me.Year)
 MsgBox Month(a)
MsgBox Year(a)

Open in new window



and i get a mesgbox with the month name, but when it's has to appear the message box with the year i get an error message : run time error 13 type mishmatch


why is that?

thanks a lot
bill201Asked:
Who is Participating?
 
Jim P.Connect With a Mentor Commented:
Dim a As Date
a = CDate(Me.MonthCombo & "/1/" & Me.[Year].Value)
 MsgBox Month(a)
MsgBox Year(a)

Open in new window


But I still recommend that you change the name of the text box to anything else than just "Year" such as YearTxt.
0
 
Jim P.Commented:
The word "Year" is a built in function in many databases and should not be be tied to a form.

Your code should be
Dim a As Date
a = CDate(Me.MonthCombo & "/1/" & Year(Now()))
 MsgBox Month(a)
MsgBox Year(a)

Open in new window

0
 
bill201Author Commented:
i want to be able to selet a year and not just the current year
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Jim P.Commented:
Then rename your combo box to something like YearCombo.

Then it would be
 Dim a As Date
a = CDate(Me.MonthCombo & "/1/" & Me.YearCombo.Value)
 MsgBox Month(a)
MsgBox Year(a)

Open in new window


Year, Month, Day, Date, Now are all functions/variables within Access. Using them is always problematic. You can get around it by using square brackets ([Name]) but it is a stupid issue when you forget them.
0
 
bill201Author Commented:
Is this the only solution that I prefer that that the year will not be a combo box
0
 
bill201Author Commented:
where i can  use square brackets, i try like this:

Dim a As Date
a = CDate(Me.MonthCombo & "/1/" & [Year])
                        
MsgBox Month(a)
MsgBox Year([a])

Open in new window


But that did not solve the problem
0
 
mbizupConnect With a Mentor Commented:
As Jim is showing you the issue is with your naming conventions.  Access is getting confused because 'Year' means multiple things... it refers to both the Year() function and your Year textbox.

This will work too... specifying the VBA prefix for the Year function:

Dim a As Date
a = CDate(Me.MonthCombo & "/1/" & Me.Year)
 MsgBox Month(a)
MsgBox VBA.Year(a)

Open in new window


But the BEST solution is to adhere to standard naming conventions.  With standard naming conventions such as txtYear, txtName cboMonth (prefixing your control names with control types), code like you originally posted will work *consistently* because the names do not conflict with built in functions.

See the best practice/standard naming conventions in the following article.  The standards that most of the regulars here use for Access VBA are found midway down the page:
http://www.xoc.net/standards/rvbanc.asp
0
 
bill201Author Commented:
thanks alot for your excellent solution and ambizup also for the link
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.