formatting a text box

[Darla123]

Hi Everyone, I have a text box that is bound to a table. There can either be a date or NA or TBD in this textbox. I have achieved this by using a validation rule:
IsDate([textbox])=True Or "NA" Or "TBD"

However they are requesting that the date format be in m/dd/yy format. I tried entering that under the format field of the table, and I also tried using an input mask, but neither work, I think it's because there can be other text in this textbox, but I'm not really sure what's going on. Is there a better way to get this value formatted only if it's a date that was entered?

 

[arnelgp]

Private Sub TextBox_BeforeUpdate(Cancel As Integer)
If "" & Me.TextBox = "NA" Then
ElseIf "" & Me.TextBox = "TBD" Then
ElseIf "" & Me.TextBox = "" Then
    MsgBox "Valid entry for this fields are NA, TBD or date (m/dd/yy)"
Else
    If (ValidateTextAsDate(Me.TextBox) = False) Then
        MsgBox "Valid entry for this fields are NA, TBD or date (m/dd/yy)"
        Cancel = True
    End If
End If
    
End Sub

Private Function ValidateTextAsDate(strSource As String) As Boolean

    Dim lngCount As Long
    Dim strMonth As String
    Dim strDay As String
    Dim strYear As String
    Dim arr() As String
    Dim dte As Date
    Dim i As Long
    
    ValidateTextAsDate = True
    If Len(strSource) > 8 Then
        ValidateTextAsDate = False
        Exit Function
    End If
    arr = Split(strSource, "/")
    If Not IsArray(arr) Then
        ValidateTextAsDate = False
        Exit Function
    End If
    If UBound(arr) <> 2 Then
        ValidateTextAsDate = False
        Exit Function
    End If
    strMonth = arr(0)
    strDay = arr(1)
    strYear = arr(2)
    If Not (Len(strMonth) <= 2 And Len(strDay) <= 2 And Len(strYear) = 2) Then
        ValidateTextAsDate = False
        Exit Function
    End If
    ValidateTextAsDate = IsDate(strMonth & "/" & strDay & "/" & strYear)
    
End Function

 

[Darla123]

I never could have gotten this on my own, and it works great. Thank you!

 

[arnelgp]

thank you also for using the code!

 

[Darla123]

And now that this is working perfectly, they've decided that they want to change it to m/d/yy, like it strips the zeroes off of the month or day if they are entered. Can that be done?