Data validation for input of date in the form of dd/mm/yyyy

Hi,
I am trying to create a validation for a date field in Tanzania. They input
the day first. In my Input mask I have:
!00\/00\/0000;0;#

In my validation rule I have:
Like "[0-3][0-9][0-1][0-9][1-2][0-9][0-9][0-9]"

this should keep most of the bad data out, but access doesn't accept the
first number as zero. It will validate if it is over 4 and all the other
numbers are validated correctly.

Help,
Lisa

Lisa:

We use the same short date format in the UK, but I've never felt the need for
a validation rule on a column of date time data type to ensure a valid date
is entered as a value which is not a valid date will be rejected
automatically. Even if a user enters a date in mm/dd/yyyy format, if it is
not a valid date in dd/mm/yyyy format it will be automatically converted
provided it is a valid date in mm/dd/yyyy format.

If you really feel there is a need to force entry unambiguously in dd/mm/yyyy
format then I'd suggest using a combo box as the bound control for the date
field. If the valid dates are within a fairly limited range, then the combo
box's list can be filled when the form opens using some simple code. One
thing this allows you to do, which can sometimes be appropriate, is present a
limited date range for entering a new record or updating a date in an
existing record, e.g. n days before and after the current date, but will show
dates from outside this range for earlier existing records.

Alternatively the combo box can draw upon a calendar table for a much longer
range. By setting the combo box's LimitToList property to True (Yes) only
dates in the list can be entered, and any error can be easily handled in the
control's NotInList event procedure. By setting its AutoExpand property to
True the nearest matching date will be progressively selected as the user
enters the characters. You cannot combine the AutoExpand mechanism with an
InputMask however, its one or the other.

If you want to pursue that route I can post code here either to fill the list
when the form opens, or to create a calendar table within your database.

Ken Sheridan
Stafford, England

Crymm9 wrote:
Hi,
I am trying to create a validation for a date field in Tanzania. They input
the day first. In my Input mask I have:
!00\/00\/0000;0;#

In my validation rule I have:
Like "[0-3][0-9][0-1][0-9][1-2][0-9][0-9][0-9]"

this should keep most of the bad data out, but access doesn't accept the
first number as zero. It will validate if it is over 4 and all the other
numbers are validated correctly.

Help,
Lisa

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201001/1