Date Input Mask Question

[Kaylen]

Is it possible to have a short date input mask for a text field in the form
but then also allow a text to be entered if there is no date? For my case,
most of the time, the users must enter a date, therefore I want to put an
input mask for date for this field on the form, however, there is a small
chance that there is no date and I would like the users have to enter "none"
in that case. So my question is, if I put a date input mask for a text field,
how can I also allow the text "none" to be entered in the same field? Or is
there better way to satisfy both conditons? Any suggestions would help.
Thanks!

 

[Linq Adams via AccessMonster.com]

If your field is supposed to hold a date, its Control Source needs to be
defined as Datatype Date/Time, and simply entering text won't work.

If you define the Control Source Datatype as Text, you could enter a "date"
or "none," but you couldn't use a short date Input Mask, and you'd have to
(maybe) convert your "date' to a real date, using CDate(DateField), before
using it in calculations. I say "maybe" because Access is tolerant here, and
often if it looks like a date and walks like a date and quacks like a date,
Access will treat it like a date!

I'd use a Date/Time Datatype, the short date Input Mask, and simply have it
left blank, if no date is needed. Then, if you need to do something with the
empty field, such as in a report, use some thing like

=IIF(IsNull([DateField]), "None", [DateField])

as the Control Source for a field in the report.

 

[Bill]

One approach would be to leave the control as text
and format the date in the OnUpdate Event after the
field has changed. If the user enters "none" then
there's nothing else to do. Otherwise, you can
attempt to "Format" the text and trap any errors
informing the user to comply with a format you
want, for example, like "mm/dd/yy".

Bill

 

[Kaylen]

I like your idea, Bill. Can you help me create an OnUpdate Event so that if a
date is entered, it will use a short date input mask and if a text is
entered, nothing change? Thank you!

 

[Bill]

Kaylen,
Are you still working on this problem? I've been gone for
a couple of days and I'm just now seeing your post,
Friday morning.
Bill

 

[Kaylen]

Yes Bill, I still want to solve this problem. I would like to have the text
field to both allow a short date input mask _ _/_ _/_ _ and also allow the
users to enter "no ltr" if there is no date available. Can you help me with a
code?

 

[Bill]

Hi Kaylen,
If you can wait until tomorrow, I'll write you some code
that will do what you want.
Bill

 

[Kaylen]

Thank you, Bill. I don't work on the weekends, so I have to check back Monday.

 

[Bill]

Okay

Thank you, Bill. I don't work on the weekends, so I have to check back
Monday.

 

[Bill]

Thread cleared here for clarity.

Kaylen,
In using the code below, either change the name
of your text box to "tbMyDate" or use a Replace
All via Edit to whatever name you've given to the
text box on your form.

Note that you can change the value of the constant
at the head of your form's module to whatever
default value you'd like; I used "No Ltr" as you
previously posted in your example.

Code posting in NG's usually have their formatting
distorted from what's entered, so let me know what
your EMA is and I'll send it to you directly if you'd
like.

Option Compare Database
Const strNoDate = "No Ltr"
Const strValid = "Expression must be ""at least"" of the form m/d"

Private Sub tbMyDate_AfterUpdate()
Dim strErrMsg As String
Dim arMMDD() As String
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Leave date field blank if made so.
'
' If defaulted, just fold to proper case.
'
' Demand at least one of the two field
' delimiters "-" or "/". Error handler
' will take care of anything else.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

On Error GoTo DateFmtError

If Len(tbMyDate & "") > 0 Then
If LCase(Me.tbMyDate) = LCase(strNoDate) Then
Me.tbMyDate = strNoDate
Else 'Check format of date
'Demand at least one delimiter
Me.tbMyDate = Replace(tbMyDate, "-", "/") 'Allow use of "-"
If InStr(Me.tbMyDate, "/") = 0 Then
strErrMsg = "Date expression """ & tbMyDate & """ is invalid."
strErrMsg = strErrMsg & vbNewLine & strValid
MsgBox strErrMsg
Me.tbMyDate = "" 'Clear invalid field
Else 'Attempt to format as entered.
Me.tbMyDate = FormatDateTime(Me.tbMyDate, vbShortDate)
End If
End If
End If

Exit Sub

DateFmtError:
strErrMsg = "Date expression """ & tbMyDate & """ is invalid."
strErrMsg = strErrMsg & vbNewLine & "Check values entered."
MsgBox strErrMsg
Me.tbMyDate = "" 'Clear invalid field
Exit Sub

End Sub

 

[Kaylen]

Bill, before using the code, I just want to let you know that I have more
than 1 text box field that need to have date input mask or "no ltr" input.
Therefore I dont think I can change the name of all those text boxes to
"tbMyDate" becuase then they will have all the same name. How would I go
about using the Replace All Via Edit thing? I'm not famliar with that
function.

Thank you for your help!

 

[Bill]

I'll make the code into a function that each
text box can pass its name. That way, you
only have one function that will handle how-
ever many text boxes you have.

E.g., in each of the text boxe's Update event,
you would code "=MyDateFunction(tbname)"
where tbname is the name of the text box.

If that's acceptable to you, I'll take care of that
later today or tomorrow.

Bill

 

[Bill]

Kaylen,

For each text box you have pertaining to
dates, enter the expression:

=fnctMyDate("tbMyDate")

Where, "tbMyDate" is the name you've
given to the text box.

Then, copy the following public function into
the code-sheet of your form.

Note: As you look at the function code, notice
the notation Me.Controls(ctlName). That
notation or code syntax is referring to the content
of the text box whose name is currently passed to
the function. (In VBA vocabulary, it references
one of the controls in the Controls Collection.)



==========begin code=============
Option Compare Database
Const strNoDate = "No Ltr"
Const strValid = "Expression must be ""at least"" of the form m/d"

Public Function fnctMyDate(ctlName As String) As String
Dim strErrMsg As String
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Leave date field blank if made so.
'
' If no date indicator, just fold to
' proper case.
'
' Demand at least one of the two field
' delimiters "-" or "/". Error handler
' will take care of anything else.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

On Error GoTo DateFmtError

If Len(Me.Controls(ctlName) & "") > 0 Then
If Trim(LCase(Me.Controls(ctlName))) = LCase(strNoDate) Then
Me.Controls(ctlName) = strNoDate
Else 'Check format of date
'Demand at least one delimiter
Me.Controls(ctlName) = Replace(Me.Controls(ctlName), "-", "/")
'Allow use of "-"
If InStr(Me.Controls(ctlName), "/") = 0 Then
strErrMsg = "Date expression """ & Me.Controls(ctlName) & """ is
invalid."
strErrMsg = strErrMsg & vbNewLine & strValid
MsgBox strErrMsg
Me.Controls(ctlName) = "" 'Clear invalid field
Else 'Attempt to format as entered.
Me.Controls(ctlName) = FormatDateTime(Me.Controls(ctlName),
vbShortDate)
End If
End If
End If

Exit Function

DateFmtError:
strErrMsg = "Date expression """ & Me.Controls(ctlName) & """ is
invalid."
strErrMsg = strErrMsg & vbNewLine & "Check values entered."
MsgBox strErrMsg
Me.Controls(ctlName) = "" 'Clear invalid field
Exit Function

End Function
==========end code==============

 

[Bill]

(PS) There is another method that can be used
here if you have several forms with date fields
that need checking. I could have used that
method here (passing values instead of
control names) but what I've given you is
something born out of a simple Event
procedure.
Bill