Month() in VBA

[gnt]

Hello-
Does the Month() function not work in VBA? Year() and Day() work just fine for me, but Month() gives me an error message...?
Thanks-

 

[Zathras]

What's the message?

MsgBox Month(Now)

works for me (answer = 3)

 

[gnt]

It's a really simple procedure....

Dim intMonth As Integer

intMonth = Month(Me!txtDate.Value)
Debug.Print intMonth

When I substitute Day() or Year() it returns the correct value, but with Month() it gives me a type mismatch error.

 

[Zathras]

There's something you're not telling us. This works fine in Excel 97 as long as there is something that looks like a date in the TxtDate text box:

Option Explicit

Private Sub CommandButton1_Click()
Dim intMonth As Integer

  intMonth = Month(Me!TxtDate.Value)
  MsgBox intMonth

End Sub

What are the contents of TxtDate in your situation?
What application are you using?
Which version?

 

[gnt]

I think I figured it out...
I used Debug.Print Month and it returned Null. So whoever started this project probably has Month defined as a Public Variable somewhere, which would explain why the Month() wasn't working. Could this be it?

Is there a quick way to figure out where this Declaration is so I can get rid of it?
Thanks-

 

[Zathras]

Add

  Option Explicit

as the first line of code in the module (above all of the Dims, Subs and Functions) and see if Debug.Print Month still works.

If there is a public Dim Month somewhere you should see an error message that says "Expected Array"

If there is a public Dim Month(12) (for example) you should see an error message that says "Type Mismatch"

But, why are you ignoring my questions:
What are the contents of TxtDate in your situation?
What application are you using?
Which version?

(I've been known to become quite sarcastic when my questions are ignored, and it's not a pretty sight.)

 

[gnt]

First, to answer your questions - don't want you to get sarcastic on me:
I'm using Access2000 on Win2000
txtDate is a field on my form with format set to ShortDate. So, for example, 12/04/02 is the date I'm currently working with. Again, Day(Me!txtDate.Value) returns 4, and Year(Me!txtDate.Value) returns 2002 - so Month() should work.

Option Explicit is already in my Gen Declarations - but still getting Type Mismatch error. And Debug.Print Month still returning Null.

Funny thing though-
If I create a new form, Month() works just fine. Just not with any of my existing forms....?

 

[Zathras]

You need Option Explicit in each module.

I don't have Access 2000 here, unfortunately. It works ok on Access 97 under Win 98, so I still can't reproduce your symptoms.

I can try it on Access 2000, Win 2K tonight when I get home, but I don't really think it will make any difference.

Can you search all of the code for the string "Month" to see where it is tripping you up?