User enter date like "12/05/345

[angela]

Hi all,
Is there any way to stop the user from entering the date in the above
format? i have tried using the validation rule, default, and inputmask but
the user is still able to enter the date "12/05/345" (where 345 is any random
number) and the validation is not checking it.

thanks in advace

 

[Tony Toews [MVP]]

Is there any way to stop the user from entering the date in the above
format? i have tried using the validation rule, default, and inputmask but
the user is still able to enter the date "12/05/345" (where 345 is any random
number) and the validation is not checking it.

One way might be to put a format in the control such that a four digit
year is display. Assuming that all your users are all running the
same date format, in your case either mdy or dmy.

Personally my systems always have the date formatted as yyyy-mm-dd
which is the ISO standard and is not ambiguous.

Another way would be to put some vba code in behind the Before Update
event of the control looking to see if the century and year is
reasonable. Unless you're running a genealogy or other database you
can likely assume that this year or last year are reasonable.

Also one trick is that if you enter just the day and the month, with
either spaces, dashes or slashes that Access will default the year to
this year.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Karl E. Peterson]

Is there any way to stop the user from entering the date in the above
format? i have tried using the validation rule, default, and inputmask but
the user is still able to enter the date "12/05/345" (where 345 is any random
number) and the validation is not checking it.

"As soon as you idiot-proof something, the world invents a better idiot!"

Dates are probably one of the worst possible, from a raw data input perspective.

A lot of folks simply give up, and use a calendar-type control instead.

Only other choice is to errortrap after an attempted CDate, then check whether a
valid result falls within the acceptable range, and finally hope the user has the
same conception of what order the numbers need be entered in. (ie, what's
02-03-04?)

 

[Tony Toews [MVP]]

A lot of folks simply give up, and use a calendar-type control instead.

If the environment is Access we prefer using a form given the
distribution and versioning problems of ActiveX controls.

See the Calendar Tips page at my website
http://www.granite.ab.ca/access/calendars.htm

There could, likely will, be lots of version problems when you go to
distribute the MSCal.OCX..

One alternative is MonthCalendar is a completely API generated Month
Calendar derived directly from the Common Control DLL. There are
links to several downloadable calendar forms at my website. As these
are forms you can also do anything with them you want.

You can also use the calendar form which comes in the Access <insert
your version here> Developers Handbook by Litwin/Getz/Gilbert,
publisher Sybex www.developershandbook.com. These books are well
worth spending money. Every time I open one I save the price of the
book.

Duane Hookum has an awesome and simple calendar in a report. See the
Calendar Reports sample section at
http://www.access.hookom.net/Samples.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Karl E. Peterson]

Hi Tony --

Yep, it's a very painful area. I wrote my own common control based date/time picker
control once, too, simply out of frustration with the available options. Nasty
stuff, dates!

Later... Karl