Problem using Month() function

[Guest]

I'm trying to use the Month() function to extract the month of the date input
by the user. The user selects their date using a calendar and the calendar
value is placed in a text box on the form. I'm trying to extract the month
from the date in the text box on the form. Here's the code I have:

Private Sub goscalendar_Click()
Dim gosmonth
txtgosdate = goscalendar.Value
txtgosdate.SetFocus
gosmonth = Month(txtgosdate.Value)
goscalendar.Visible = False
End Sub

When I use this code I get the following error:

Run-time error '13':
Type mismatch

Any help appreciated. Thanks.

 

[Guest]

first, you don't need to use the Value property of the control, it is the
default. Next, you should always qualify your control names with either the
form name or the Me shortcut. Any of these would be correct:

Forms!MyFormName!txtgosdate
Me.txtgosdate
Me!txtgosdate

The latter two are only valid in the form's module.

That, however, is probably not causin the error. It is most likely a
formatting error. That is, the Month function is not recognizing the value
in txtgosdate as a date. I don't know what your regional date format is or
what the user is entering in the control, but I would start with the Date
Type Conversion function. If that fails, work on the formatting so it
appears to VBA to be a date.

gosmonth = Month(CDate(txtgosdate.Value))

Also, your Dim statement make gosmonth a Variant. Is that what you really
want?

txtgosdate

 

[Guest]

Thanks for your reply. The short date format on the PC I'm working from is
YYYY/MM/DD. Using the CDate() function to convert the date to a date format
works no problem but when I incoporate that with the Month() function, I get
the same error as below. Here's what I tried:

Private Sub goscalendar_Click()
Dim gosmonth As Integer
txtgosdate = goscalendar.Value
txtgosdate.SetFocus
gosmonth = Month(CDate(Forms!GradeOfService!txtgosdate))
goscalendar.Visible = False
End Sub

 

[Guest]

Thanks for your reply. The short date format on the PC I'm working from is
YYYY/MM/DD. Using the CDate() function to convert the date to a date format
works no problem but when I incoporate that with the Month() function, I get
the same error as below. Here's what I tried:

Private Sub goscalendar_Click()
Dim gosmonth As Integer
txtgosdate = goscalendar.Value
txtgosdate.SetFocus
gosmonth = Month(CDate(Forms!GradeOfService!txtgosdate))
goscalendar.Visible = False
End Sub

 

[Guest]

Just to see if perhaps there is a reference problem causing this, try this in
the immediate window of the VBA editor:

?Month(Date)

 

[Guest]

that works no problem at all in the immediate window.

 

[Guest]

This is very interesting. Other than your code not being very well
qualified, I don't see the problem. You many need to experiment with the
format of the date until it works correctly. In the mean time, I would
suggest the following changes to your existing code:

Private Sub goscalendar_Click()
Dim gosmonth As Integer

Me.txtgosdate = Me.goscalendar
Me.txtgosdate.SetFocus
gosmonth = Month(CDate(Me.txtgosdate))
Me.goscalendar.Visible = False

End Sub

Now the question is - what is the purpose of the above code? You initialize
a variable (gosmonth) and populate it with the month, but never do anything
with it.

Once the sub is complete, it disappears. What do you want to do with it?

 

[Guest]

Thanks for your help. i tried the below code and still the same problem.
The reason why I'm trying to determine the month of the date is because I
want to store it in a table.

 

[Guest]

If the date is stored in the table, then it is not necessary, in fact
violated good database normalization rules, to store the date. It can be
calculated whenever you need it.