Validate dates in date/time field

[Kayleigh]

Hi
Just come across a really weird issue. I have several date/time fields which appear in a form. When entering the dates it will allow invalid dates e.g. 1/1/111
I always thought a date field would check that a valid date has been input before updating data??
Any way round this?

 

[theDBguy]

Actually, 1/1/111 is a valid date. If you want to keep the dates within a certain range, you can use a Validation Rule.

 

[Kayleigh]

What type of validation rule will restrict dates to be within last 50 years or future 50 years?

 

[cheekybuddha]

The first of January in the year 111 seems a perfectly valid date to me.

If you want to ensure dates entered are within sane limits you will need to check yourself.

You can use the control's BeforeUpdate event:

Private Sub txtDate_BeforeUpdate(Cancel As Integer)

  If Len(Me.txtDate & vbNullString) > 0 _
  And (Not IsDate(Me.txtDate) Or Me.txtDate < #1900-01-01# Or Me.txtDate > #2100-01-01#) Then
    MsgBox "Invalid date!"
    Me.txtDate.Undo
    Cancel = True
  End If
 
End Sub

 

[theDBguy]

What type of validation rule will restrict dates to be within last 50 years or future 50 years?

Untested, but try: Between DateAdd("yyyy",-50,Date()) And DateAdd("yyyy",50,Date())

Hope that helps...

 

[cheekybuddha]

Oops! Too slow again!

 

[The_Doc_Man]

Just a comment in regard to Microsoft date routines and the apparent legality of 1/1/111.

Just for the record, the MS Date translation routines work from 1/1/100 through 12/31/9999 - which makes for two perfectly good non-null dates that you can use for "earliest legal date" and "latest legal date." I used them most recently in my genealogy code. Physically, the 1/1/100 date is a negative number as demonstrated in CDbl( #1/1/100# ) - and so is CDbl( #1/1/1776# ). You get negative numbers for people born before 12/30/1899. I got a LOT of those when dealing with ancestry research. So I use 1/1/100 for the birthdate of folks whose birthdate was not on record in my Ancestry.COM research. Makes it easy to say, OK, person is most likely deceased. And for people still alive, 12/31/9999 makes a perfect stand-in for their death date. Two VERY useful numbers. In fact, so useful that I declared them as date constants in my genealogy database code.

In more practical terms, when I wanted a date that was not zero but also not possible (when dealing with a computer security database), I used 1/1/1900 - which was clearly before any modern computers - but it wasn't zero either.

 

[LarryE]

Hi
Just come across a really weird issue. I have several date/time fields which appear in a form. When entering the dates it will allow invalid dates e.g. 1/1/111
I always thought a date field would check that a valid date has been input before updating data??
Any way round this?

Use a Short Date Input Mask on the field if you want a 4-digit year. Input masks are in the properties window for the form field.

 

[Kayleigh]

The first of January in the year 111 seems a perfectly valid date to me.

Thanks. Very informative.
I think validation is the way forward here.

 

[Pat Hartman]

The problem with using a date mask is that it removes the date picker as an option. So, if you are entering old dates as the Doc is in his ancestry database, the date picker isn't useful anyway but if most of your dates are within a month or two of today, the date picker is too important to loose.

 

[cheekybuddha]

I think validation is the way forward here.

@theDBguy's suggestion might be simpler than using the control's event

 

[Pat Hartman]

Validation rules are very simplistic and so not useful for most situations but this is an exception. Things like birth dates should always be <= Date(). If you are doing most types of data entry, may decide that data will always be entered within a week so the date has to be > Date() - 7 and <= Date()

The nice thing about table level validation rules is that they are enforced by the database engine so it makes absolutely NO difference what cause the update, the rule is enforced.

The downside is that it is a separate place to look for validation rules.