Input Mask for Dates and field's end result

[dvlander]

Is there an easy way to have a Text Box's data entry format be MM/YY and have
the actual underlying field's date be converted to the last day of that
month? I always appreciate the help I receive on this forum.

Thx, Dale

 

[Damon Heron]

Yes, first put an input mask on an unbound textbox set to:
99/00;0; This allows the user to type mm/yy.
Your textbox bound to the table should be invisible, since it will always be
the mm/lastday/yy.
I named the bound textbox Text1, and the unbound, Text2.

Next,on the unbound textbox event:

Private Sub Text2_BeforeUpdate(Cancel As Integer)
Dim mydate As Date
mydate = Left(Text2, 2) & "/01/" & right(Text2, 2)
mydate = DateSerial(Year(mydate), Month(mydate) + 1, 0)
Text1 = mydate
'the above could all be squeezed into one line
'if you're not big on clarity
End Sub

To make the current records format properly, add this to
the current event of the form:

Private Sub Form_Current()
Dim myPos As Integer
If Not Me.NewRecord Then
myPos = InStr(1, Text1, "/", 1) - 1
End If
If myPos = 1 Then
Me.Text2 = "0" & Left(Me.Text1, myPos) & right(Text1, 2)
Else
Me.Text2 = Left(Me.Text1, myPos) & right(Text1, 2)
End If

End Sub

HTH
Damon