Help with date functions requested

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dim Thisdate, ThisMonth as date
ThisDate = #11/15/2004#
MsgBox ThisDate
ThisMonth = Month(ThisDate)

Is there a problem using the "Month" function in VBA code? Why do I get the
error message "Object doesn't support this property or Method" for the
"Month" function shown above?

Thanks for your help.
 
There's no problem using the Month function in VBA code. There is, however,
a problem with assigning its value to a Date field.

The Month function returns an integer between 1 and 12. Dates are actually 8
byte floating point numbers, where the integer portion represents the number
of days relative to 30 Dec, 1899 and the decimal portion represents the time
as a fraction of a day. That means that when the Month function returns 1
(for January), your ThisMonth date field is going to treat that as 31 Dec,
1899. A Month value of 2 (for February) is 1 Jan, 1900, and so on up to a
Month value of 12 (for December) being 11 Jan, 1900.

BTW, your Declare statement

Dim Thisdate, ThisMonth as date

probably isn't doing what you think it is. That's declaring the variable
Thisdate as a Variant, and ThisMonth as a date. Assuming you want them both
to be dates, you need:

Dim Thisdate As Date, ThisMonth as date

Of course, what you actually want is:

Dim Thisdate As Date, ThisMonth as Integer
 
Lee Hunter said:
Dim Thisdate, ThisMonth as date
ThisDate = #11/15/2004#
MsgBox ThisDate
ThisMonth = Month(ThisDate)

Is there a problem using the "Month" function in VBA code? Why do I
get the error message "Object doesn't support this property or
Method" for the "Month" function shown above?

Thanks for your help.
Click to expand...

There are errors in that code, but none that ought on the face of it
give you the particular error you're reporting. When I paste that code
into a new Sub and run it, I don't get that error. Are you sure that's
where the error you're getting is being raised?

The code ought probably to be:

Dim Thisdate As Date
Dim ThisMonth as Integer

ThisDate = #11/15/2004#
MsgBox ThisDate
ThisMonth = Month(ThisDate)

But as I said, your original code shouldn't raise that error. Have you
stepped through the code line by line and verfied exactly where the
error occurs?
 
Thank you, sir. Much appreciated. Just to show you how easy it is to
overlook the obvious, taking your response together with Dirk's and proving
that both Day and Year worked just fine, It noticed that I had "somewhow?"
named the Sub "Month". Don't I feel stupid now!

Thanks again!
 
Back
Top