Date to be inputted in format "yyyy-mm"

G

Guest

Hi,
I have a table with two date fields: StartDate and EndDate declared as
Date/Time fields. On the corresponding form, the user would like to input
only the year and month part in the format "yyyy-mm" but I can't get it to
work. I always get an error message.
I don't mind if the date are stored as "yyyy-mm-dd" or some other format as
long as the are displayed in the "yyyy-mm" format in the form.
What can I do?
 
B

Brendan Reynolds

In this example, txtYear and txtMonth are unbound text boxes. TestDate is a
date/time field in the form's record source.

Private Sub Form_Current()

If Me.NewRecord Or IsNull(Me.TestDate) Then
Me.txtYear = Null
Me.txtMonth = Null
Else
Me.txtYear = Year(Me.TestDate)
Me.txtMonth = Month(Me.TestDate)
End If

End Sub

Private Sub txtMonth_AfterUpdate()

If Not IsNull(Me.txtYear) And Not IsNull(Me.txtMonth) Then
Me.TestDate = DateSerial(Me.txtYear, Me.txtMonth, 1)
End If

End Sub

Private Sub txtYear_AfterUpdate()

If Not IsNull(Me.txtYear) And Not IsNull(Me.txtMonth) Then
Me.TestDate = DateSerial(Me.txtYear, Me.txtMonth, 1)
End If

End Sub
 
G

Guest

Hi Brendan,
Your comment is very helpful but I found a better (and easier) way to solve
my problem: use the format yyyy-mm on the form's control. The date needs no
special setting in the database. And it works. The day (01) is automatically
added to the field.
I hope this helps some people in the community.
Thank you for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top