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?

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 wrote:
> 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?
>

That's perfect, thanks!

On Nov 10, 12:39=A0pm, John Spencer <spen...@chpdm.edu> wrote:
> 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" =A0or even "2001-02-29"
>
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
>
> eda wrote:
> > Hello,
>
> > I'm adding a new field to a table to hold a date. =A0The field must be
> > able to except input in all three of the following ways:
>
> > yyyy =A0 (just the year, without month or day)
> > yyyy-mm =A0 (just the year and month, without the day) =A0or
> > yyyy-mm-dd =A0 (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. =A0However, I don't know how to create =
a
> > format or input mask where everything past the yyyy is optional. =A0I
> > 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?

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

eda wrote:
>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?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200911/1

Another wonderful idea! Thanks!

On Nov 10, 2:34=A0pm, "KenSheridan via AccessMonster.com" <u51882@uwe>
wrote:
> Have you considered using three separate fields for the year, month a day=
-of-
> month? =A0Its always easier to combine values from separate fields than t=
o
> parse a single value, and it would make the formatting and input masks ea=
sier
> to set up. =A0On a form you can use three contiguous text boxes with a hy=
phen
> character between each as labels. =A0You =A0could include some validation=
code in
> the day-of-month control's BeforeUpdate EventProcedure to test for an inv=
alid
> date, e.g.
>
> Private Sub txtDay_BeforeUpdate(Cancel As Integer)
>
> =A0 =A0 Const BAD_DATE =3D 13
> =A0 =A0 Const MESSAGETEXT =3D "Invalid date"
> =A0 =A0 Dim dtmDate As Date
>
> =A0 =A0 If Not IsNull(Me.txtDay) Then
> =A0 =A0 =A0 =A0 On Error Resume Next
> =A0 =A0 =A0 =A0 dtmDate =3D CDate(Me.txtYear & "-" & Me.txtMonth & "-" & =
Me.txtDay)
> =A0 =A0 =A0 =A0 Select Case Err.Number
> =A0 =A0 =A0 =A0 =A0 =A0 Case 0
> =A0 =A0 =A0 =A0 =A0 =A0 ' no error
> =A0 =A0 =A0 =A0 =A0 =A0 Case BAD_DATE
> =A0 =A0 =A0 =A0 =A0 =A0 MsgBox MESSAGETEXT, vbExclamation, "Error"
> =A0 =A0 =A0 =A0 =A0 =A0 Cancel =3D True
> =A0 =A0 =A0 =A0 =A0 =A0 Case Else
> =A0 =A0 =A0 =A0 =A0 =A0 ' unknown error
> =A0 =A0 =A0 =A0 =A0 =A0 MsgBox Err.Description, vbExclamation, "Error"
> =A0 =A0 =A0 =A0 =A0 =A0 Cancel =3D True
> =A0 =A0 =A0 =A0 End Select
> =A0 =A0 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 a=
nd
> 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
>
>
>
> eda wrote:
> >Hello,
>
> >I'm adding a new field to a table to hold a date. =A0The field must be
> >able to except input in all three of the following ways:
>
> >yyyy =A0 (just the year, without month or day)
> >yyyy-mm =A0 (just the year and month, without the day) =A0or
> >yyyy-mm-dd =A0 (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. =A0However, I don't know how to create a
> >format or input mask where everything past the yyyy is optional. =A0I
> >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?
>
> --
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Foru=
ms.aspx/access/200911/1