Year Function

[beechclose]

I need to extract the year part of a date and I have the following code to test

Private Sub Form_Open(Cancel As Integer)
Dim mydate, myyear
 
mydate = Date
myyear = Year(mydate)
 
End Sub

But it gives me a type mismatch when trying to find the Year of mydate. The same problem occurs with the Month function but the Day function works fine. I have used this code before without problem and am now very confused. Any suggestions gratefully received.

Thanks

[cori25]

I suggest using this to get the year:

Dim strMonth As String

strMonth = Format(Date), "mmm-yyyy")

[Scott Price]

Explicitly declare your variables, this is a good general practice as well as a possible solution to your problem.

Dim myDate As Date

Be aware that VBA (unlike some other programming languages) defaults an implicit declaration to the Variant data type, which can have some unintended consequences:

Dim myDate, myMonth As Date results in one variable declared as a Date, the other as a variant. In VBA each variable must be dimensioned separately.

Regards,
Scott

[beechclose]

Explicitly declare your variables, this is a good general practice as well as a possible solution to your problem.

Dim myDate As Date

Be aware that VBA (unlike some other programming languages) defaults an implicit declaration to the Variant data type, which can have some unintended consequences:

Dim myDate, myMonth As Date results in one variable declared as a Date, the other as a variant. In VBA each variable must be dimensioned separately.

Regards,
Scott

I have amended my code to

Private Sub Form_Open(Cancel As Integer)
Dim mydate As Date
Dim myyear As Date
 
mydate = Date
myyear = Year(mydate)
 
End Sub

but I now get 'can't find the field Year referred to in your expression' as an error message?

[Scott Price]

I just ran this without problems in my test database:

Dim MyDate As Date
Dim MyYear As String
 
MyDate = Date
MyYear = Year(MyDate)
Debug.Print MyYear

The output is 2008 as expected. The MyYear needs to be a String data type, because a Date data type attempts to coerce the 2008 result into a Date with the output of 6/30/1905 for some strange reason :-)

If you continue having problems, I suggest you might have some corruption problems creeping in.

Regards,
Scott

[beechclose]

Thanks - it seems to be working now!

[Scott Price]

Glad it's working for you!

Thanks for posting back to let us know.

Regards,
Scott

[Stewart Ross]

Hi all. Year(), Month() and Day() all return integer components from a date - which can then be used within queries tocompare day, month and year ranges, or in generation of new dates (for example, using DateSerial(Year(Date), Month(Date), 1) to return the date corresponding to the first day of the current month).

There was a little confusion over types in parts of the responses, as MyYear was being given a Date type instead of an Integer value.

Cheers

Stewart

[NeoPa]

...
The output is 2008 as expected. The MyYear needs to be a String data type, because a Date data type attempts to coerce the 2008 result into a Date with the output of 6/30/1905 for some strange reason :-)
...

If you put the value 2008 into a Date field it will try to interpret it as THAT number of DAYS since 31 December 1899 (the base date for VBA dates).

[Scott Price]

If you put the value 2008 into a Date field it will try to interpret it as THAT number of DAYS since 31 December 1899 (the base date for VBA dates).

Oh, THAT'S the strange reason :-) Somewhere I knew that, but forgot the exact date it starts at.

Thanks NeoPa!

Regards,
Scott