Year function in VBA

[williamlove]

I want to get the year, so I tried this:

Dim vYear As Variant
vYear = Year(Date)

When I run this I get run time error 13, type mismatch.

To sort this out, I tried running this sample code, straight out of the help.

Dim MyDate, MyYear
MyDate = #February 12, 1969# ' Assign a date.
MyYear = Year(MyDate)

When I run this I still get run time error 13, type mismatch.

I also tried this

Dim vYear As Variant
vYear = Year(#11/24/2007#)

and
vYear = Year("11/24/2007")

Still the type mismatch. I'm stumped. Can you identify the problem? I am running Access 2003 on a fully patched and updated XP Pro box.

 

[pbaldy]

That should work fine. What gets highlighted with the error?

 

[williamlove]

The line that gets highlighted is the line containing the Year function. Its driving me nuts.

 

[pbaldy]

That is weird. Have you checked the references to make sure none say "Missing"? If that's not it, can you post a sample db?

 

[boblarson]

I am running Access 2003 on a fully patched and updated XP Pro box.

I just tried it on an Access 2003 SP2 db and your initial

Dim vYear As Variant
vYear = Year(Date)

worked for me. Since you say, "fully patched" machine, you don't by chance have Office 2003 SP3 on your machine? This may be another one of those problems caused by it.

 

[ChrisO]

Do you have a variable or function called Year?

Regards,
Chris.

 

[williamlove]

To start sorting out the questions you guys asked I made a new form and don't have the same problem (that does not help much since I need to use my current form).
When I put the same code in the form I care about, I get the type mismatch. I tried changing the variable names to new ones certain to not be in use.

Private Sub cmdTestYear_Click()
Dim vMyDate, vMyYear
vMyDate = #2/12/1969# ' Assign a date.
vMyYear = Year(vMyDate)
End Sub

No matter what I do (on this form only) I get a type mismatch when I try to use the year function. I know there is a simple answer...this is a brain teaser not a complicated debug...but I can't get it.

 

[boblarson]

As Chris asked, you don't have a control or something on that form named YEAR do you? If so, that could cause an issue.

Also, your form may be corrupt. So, you may need to rebuild it, but for starters you can try importing everything into a new, blank mdb file to see if that might help.

But the first thing to do is to search the form, high and low, for any control or code that uses the word YEAR. Also, a field in your table with a lookup (at table level) can also cause problems like this.

 

[williamlove]

I had a field in the Access Database called Year. It was the control source for a text box on the form of interest. I changed the field name. The control source changed with it (I checked). This solved the type mismatch problem. But now just to spite me, I'm getting this:

Run time error '2465'

Microsoft Office Access can't find the field 'Year' referred to in your expression.

I am now trying to figure this one out.

 

[pbaldy]

Try

vYear = VBA.Year(Date)

 

[williamlove]

That worked! Thanks for your help. Any idea why that is needed?

 

[RuralGuy]

Since Paul's suggestion worked, we strongly suspect there is another function named Year() in your system.

 

[ChrisO]

And yet another possibility, even after deleting all other instances, is “Persistent Debris”.

Having gotten rid of all ‘Year’ references, you may still need to /decompile and/or import all form objects to a new form container.

Regards,
Chris.

 

[aikea]

Actually, I was expierenced the same problem years back. If you look carefully, the vba year function are actually "year()" without capital "Y".

The function you used shows up like "Year", so I immidiately realised that you have a field, function or variable named "Year".

 

[boblarson]

Actually, I was expierenced the same problem years back. If you look carefully, the vba year function are actually "year()" without capital "Y".

Aikea:

Umm, you are wrong about that. In fact, when you type it in and then go to another line, Access itself capitalizes it and in the help file it is capitalized.

It would actually be the other way around - if you see it in LOWER CASE then you likely have a function or item in your database with that name like that.