Year function & type mismatch error

[Tony Nichols]

Has anyone seen the Year() function create an error
indicating there is a data type mismatch?

Below is the a bit of code that works in one form but not
in another and is puzzling me

Dim sSQL As String
Dim db As Database
Dim rec As Recordset
Dim sECR As String
Dim nECR As Integer
Dim dYear As Integer
Dim sYear As String
Dim dDate As Date

Set db = CurrentDb()
dDate = Date
dYear = Year(dDate)
sYear = dYear
sYear = Mid(sYear, 3, 2)

The error number 13 (Type mismatch) occurs at the dYear =
Year(dDate) line.

Any assistance would be greatly appreciated.

Tony Nichols

 

[Tony Nichols]

A little more information about this issue.

I tried using the right click definition option on the
funtion while in the class module and the definition was
pointing to a property in my form. Yet there is no
property with the name of Year in the form.

There is however a field in the underlying table with the
name of Year.

This is obviously causing the problem. The question is
what can I do to force it not to find the field name
short of changing the feild name?

Thanks in advance for any assistance.

Tony Nichols

 

[Tony Nichols]

Nothing like having a conversation with myself for the
world to see. I guess this is like a blog.

I discovered a solution using the format statement of
dYear = format(Date,"yyyy")

as an option. Works great.

Thanks for all the help. LOL.

 

[Graham Mandeno]

Hi Tony

I don't know if you are still listening, having found a "solution", but the
Format option is not an ideal one. What it is doing is formatting the date
part of the year as a 4-digit *string* ("2004") and then VBA is doing an
implicit conversion back to an integer (the declared data type of dYear).

You can force Access to use the VBA Year function instead of referring to
the local property by using the VBA. prefix:
dYear = VBA.Year(Date)

However, a *much* preferred solution is to avoid all Access and VBA reserved
words when you are naming fields and controls and variables.