Problems with MONTH() function in VBA

[Dougpeplow]

I have a variable holding a date, in the format "dd/mm/yy"
The functions day() and Year() work fine when applied to this variable, but month() doesn't

example code:-

Dim MyDate as Date
Dim MyMonth as Integer
MyDate = 02/03/01
MyMonth = Month(MyDate) ' MyMonth should return 3 but a "Type Mismatch" error occurrs.

Please Help

 

[dsi]

Do you have quotes around the date? My system sees the date in the format mm/dd/yy when translating the variable.

Sub Test
Dim MyDate As Date
Dim MyMonth As Integer
MyDate = "02/03/01" 'dd/mm/yy
MyMonth = Month(MyDate)
MyDay = Day(MyDate)
MyYear = Year(MyDate)

MsgBox MyMonth & vbCrLf & MyDay & vbCrLf & MyYear
End Sub

This returns:
2 Month
3 Day
2001 Year DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[Dougpeplow]

Thanks, but I tried your test routine and the month function still gave the same error!! (the year and day functions are OK)- quite weird

 

[BatFace]

Doupelow,

I was having the same problems - I changed the 'dim mymonth as integer to ' dim mymonth as variant - and this worked (?)...

Let me know if you got this to work another way

 

[Loomah]

In a slightly related issue can anyone tell me briefly the difference between enclosing the date in quotes and in hashes??

ie "16/12/02" vs #16/12/02# if I use hashes the date seems to format itself as mm/dd/yyyy.
%-)

 

[BatFace]

Loomah - the hashes mean the date is literal rather than variable, and still needs to formatted to suit your needs