By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,261 Members | 1,658 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,261 IT Pros & Developers. It's quick & easy.

Year Function

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. Dim mydate, myyear
  3.  
  4. mydate = Date
  5. myyear = Year(mydate)
  6.  
  7. 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
Feb 19 '08 #1
Share this Question
Share on Google+
9 Replies


cori25
P: 83
I suggest using this to get the year:

Dim strMonth As String

strMonth = Format(Date), "mmm-yyyy")
Feb 19 '08 #2

Scott Price
Expert 100+
P: 1,384
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
Feb 19 '08 #3

P: 8
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. Dim mydate As Date
  3. Dim myyear As Date
  4.  
  5. mydate = Date
  6. myyear = Year(mydate)
  7.  
  8. End Sub
but I now get 'can't find the field Year referred to in your expression' as an error message?
Feb 19 '08 #4

Scott Price
Expert 100+
P: 1,384
I just ran this without problems in my test database:


Expand|Select|Wrap|Line Numbers
  1. Dim MyDate As Date
  2. Dim MyYear As String
  3.  
  4. MyDate = Date
  5. MyYear = Year(MyDate)
  6. 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
Feb 19 '08 #5

P: 8
Thanks - it seems to be working now!
Feb 19 '08 #6

Scott Price
Expert 100+
P: 1,384
Glad it's working for you!

Thanks for posting back to let us know.

Regards,
Scott
Feb 19 '08 #7

Expert Mod 2.5K+
P: 2,545
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
Feb 19 '08 #8

NeoPa
Expert Mod 15k+
P: 31,768
...
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).
Feb 20 '08 #9

Scott Price
Expert 100+
P: 1,384
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
Feb 20 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.