Properties - Format, Input Mask, Validation Rule

[eda]

Hello,

I'm adding a new field to a table to hold a date. The field must be
able to except input in all three of the following ways:

yyyy (just the year, without month or day)
yyyy-mm (just the year and month, without the day) or
yyyy-mm-dd (year, month, and date)

Since usually it would just hold the year, it doesn't have to be a
DATE data type, it can be TEXT. However, I don't know how to create a
format or input mask where everything past the yyyy is optional. I
guess I could leave it without any format or input mask and just use a
validation rule, but I can't figure out how to make that work either.
Can anyone help?

 

[John Spencer]

Your validation rule might be something like

Like "####" OR Like "####-[0-1]#" OR Is Null OR Like "####-[0-1]#-[0-3]#"

Of course that will allow bad date values to be entered even if they meet the
pattern. For instance "9999-14-32" or even "2001-02-29"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[eda]

That's perfect, thanks!

Your validation rule might be something like

Like "####" OR Like "####-[0-1]#" OR Is Null OR Like "####-[0-1]#-[0-3]#"

Of course that will allow bad date values to be entered even if they meetthe
pattern. For instance "9999-14-32"  or even "2001-02-29"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello,

I'm adding a new field to a table to hold a date.  The field must be
able to except input in all three of the following ways:

yyyy   (just the year, without month or day)
yyyy-mm   (just the year and month, without the day)  or
yyyy-mm-dd   (year, month, and date)

Since usually it would just hold the year, it doesn't have to be a
DATE data type, it can be TEXT.  However, I don't know how to create a
format or input mask where everything past the yyyy is optional.  I
guess I could leave it without any format or input mask and just use a
validation rule, but I can't figure out how to make that work either.
Can anyone help?

 

[eda]

Another wonderful idea! Thanks!

Have you considered using three separate fields for the year, month a day-of-
month?  Its always easier to combine values from separate fields than to
parse a single value, and it would make the formatting and input masks easier
to set up.  On a form you can use three contiguous text boxes with a hyphen
character between each as labels.  You  could include some validationcode in
the day-of-month control's BeforeUpdate EventProcedure to test for an invalid
date, e.g.

Private Sub txtDay_BeforeUpdate(Cancel As Integer)

    Const BAD_DATE = 13
    Const MESSAGETEXT = "Invalid date"
    Dim dtmDate As Date

    If Not IsNull(Me.txtDay) Then
        On Error Resume Next
        dtmDate = CDate(Me.txtYear & "-" & Me.txtMonth & "-" & Me.txtDay)
        Select Case Err.Number
            Case 0
            ' no error
            Case BAD_DATE
            MsgBox MESSAGETEXT, vbExclamation, "Error"
            Cancel = True
            Case Else
            ' unknown error
            MsgBox Err.Description, vbExclamation, "Error"
            Cancel = True
        End Select
    End If

End Sub

Put the same code in the year and months controls' BeforeUpdate event
procedures also, as a user could enter a valid month and day such as 03 and
31, but then change the month to 02, or enter a valid leap year date suchas
200-02-29 and then change the year to 2001.

Ken Sheridan
Stafford, England

 

[James A. Fortune]

Have you considered using three separate fields for the year, month a day-of-
month? Its always easier to combine values from separate fields than to
parse a single value, and it would make the formatting and input masks easier
to set up. On a form you can use three contiguous text boxes with a hyphen
character between each as labels. You could include some validation code in
the day-of-month control's BeforeUpdate EventProcedure to test for an invalid
date, e.g.

Private Sub txtDay_BeforeUpdate(Cancel As Integer)

Const BAD_DATE = 13
Const MESSAGETEXT = "Invalid date"
Dim dtmDate As Date

If Not IsNull(Me.txtDay) Then
On Error Resume Next
dtmDate = CDate(Me.txtYear & "-" & Me.txtMonth & "-" & Me.txtDay)
Select Case Err.Number
Case 0
' no error
Case BAD_DATE
MsgBox MESSAGETEXT, vbExclamation, "Error"
Cancel = True
Case Else
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
Cancel = True
End Select
End If

End Sub

Put the same code in the year and months controls' BeforeUpdate event
procedures also, as a user could enter a valid month and day such as 03 and
31, but then change the month to 02, or enter a valid leap year date such as
200-02-29 and then change the year to 2001.

Ken Sheridan
Stafford, England

That's positively brilliant. I mean that. Should the date format
ever have been created? Isn't the date really a calculated value
based on three separate pieces of information that should never have
been combined into one field? Even handy functions for combining and
splitting the three pieces of information doesn't atone for the
underlying transmogrification. Maybe other functions could have been
created to validate combinations of the three without stuffing them
all into a single field. That's food for thought anyway.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Praise from someone for whom I have as much respect as I do for you is always
welcome James, but while the code is mine, the use of separate columns for
the year, month and day-of-month is something a former colleague of mine back
in the old CompuServe MS Access Forum days always recommended to ensure
consistent user input; to try and prevent me inadvertently changing 4 July to
7 April if I were to use your machine for instance.

As regards the date format it would be better if we all stuck to the ISO
standard of YYYY-MM-DD, but we are probably too much creatures of habit to
change our normal use. I always use it for date literals in code, even
though the US short date format or any other internationally unambiguous
format will do.

I don't think I'd see dates as three separate pieces of information, though.
Its more an encoding system which encodes any point in time to a precision of
1 day as an offset from 1 January, year 1 (nominally at least, we have to
ignore the days removed when Gregory reformed the Julian calendar, but as the
reform was adopted at different times in different places over a long period
of time its not really practical, or indeed necessary, to take it into
account on a day to day basis). Omitting the month or day is really just a
reduction in the precision of the encoded value from 1 day to 1 month or 1
year. Conversely, adding time values is an increase in the precision of
course.

I don't imagine it would be too difficult to validate the three separate
values. I took the easy route of simply seeing if, taken together, they
converted to a valid value of date/time data type, but theoretically a year
can be any integer value (it doesn't even have to be limited to 4 digits, as
I can testify, having worked with archaeological data). The month value
merely has to be an integer between 1 and 12. The day can be up to 28 for
any year/month, up to 30 for some, 31 for others in any year. The algorithm
for determining leap years is well known so whether a February date can go up
to 28 or 29 would not be difficult to cater for. So the code to validate
each of the 3 values should be quite straightforward without having to make
any use of Access's date/time data type. I'll bet somebody, somewhere has
done so already.

Ken Sheridan
Stafford, England

I suppose a point time is a reasonable entity. The encoding idea
(with precision) seems to draw an analogy I've mentioned before
between the decimal places of numbers and the number of date pieces
used to represent the time value. Except that numbers don't change
their base at nearly every decimal place! I might add some more
musings about the philosophy and origin of time encodings later.

James A. Fortune
(e-mail address removed)