[AccessD] CheckDate?

Hi Borge

First, have in mind that what to one looks like a crazy date may be
perfectly valid for others.
mm/dd/yyyy is crazy for most people outside the US; yyyy-mm-dd is an
ISO standard, is the preferred format in Sweden and (I think) Japan and
an accepted format in Denmark, and in Java and SAP DB (Max DB) the
preferred date format. Also, it is by no doubt the most logical:
yyyymmddhhnnss ... 

Second, one should apply any validation rule needed or useful as you
do. For most accounting systems, for example, you normally are allowed
to type in dates within the current fiscal year only except for
statistics, of course.

Third, my suggestion will only catch those inputs which are in direct
contrast with what might be intended. If you format your textbox to "dd
mmm yy" it _will_ catch the example you mention, 34 dec 04. Regard it as
a "silent inputmask".

Finally, one method to get rid of all this completely, is to use three
juxtaposed textboxes, one for day, one for month and one for the year.
Then you are in complete control. It even adds the possibility to let
the user type in the day only using default values for month and year,
or type digits only; you can't do that when using one textbox only. Many
accounting systems accept the 6-digit compressed date format ddmmyy (and
variations hereof) for minimal typing. Unfortunately Access does not.

/gustav

>>> pcs at azizaz.com 04-12-2004 07:13:05 >>>
Hi Gustav,

Thanks man, also for going the extra step and supplying a more general
approach.

I've been playing around with this and I've got to stop.....

I've found - in line with what you said -, that when you are using a
two
digit year entry, as long as MS Access can interpret this as a possible
date
it will, and there's no general way to stop it from doing that.

At least, I have found no way to catch the following scenario in a
general
way:

Two digit year format entry of the following format : d mmm yy - where
you
have input like 32 Dec 04 or 35 Dec 04 or other in this context
'mistyped
entries'

Only by limiting valid date entries like : >=#1/1/1950# for example

When User enters an (obvious?) invalid(?) date entry like 32 Dec 04 
MA
Access appears to be thinking

"..Ok, let' see the first part can be neither a date nor a month so
.... now
lets have a look at the last part of this variant type of thing just
to
check if the poor bugger has been using the four digit format... oh no!
-
well, good! we use the first part as year and we'll disregard the fact
that
also the last part could be interpreted as the year with an
consequently
incorrect date entry .... we'll just frustrate the fellow and give him
4/12/1932  .... and alright, if he insists on displaying month as ddd,
we'll
return him with a 4 Dec 1932 .... no problerma..... Let's just hope the
poor
fellow is going to use this unbound text box as a parameter in a sql
string,
and we can mess with him a bit further..... wish we could see his face
when
he discovers that such an sql string - since he obviously is not a "mm
dd
yy" type of person - will turn into 12 March 1932 .... ha, ha ...
that'll
teach the hominid a lesson!"

So, Gustav I'm back with testing for >=#1/01/2001#

Regards
Borge

PS

Hmmm, also come to think of it MS Access interprets 32 Dec 04 as
4/12/1932
(using format, year or cdate function) - why not 4/12/2032  ??


----- Original Message ----- 
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Saturday, December 04, 2004 12:33 AM
Subject: Re: [AccessD] CheckDate?


> Here is a more general approach:
>
>   With Me!txtDate
>     Cancel = Val(.Text) <> Val(Format(.Text, Nz(.Format, "Short
> Date")))
>   End With
>
> /gustav
>
> >>> Gustav at cactus.dk 03-12-2004 12:37:50 >>>
> Hi Borge
>
> Well, the problem is that IsDate() and CDate() don't care for
"likely
> dates" only possible dates which date strings formatted as yy/mm/dd
> certainly are.
> If you need dates within a certain time frame, you'll have to check
> that on your own as you already have done.
>
> You could also, in the BeforeUpdate event, check for
>
>   Day(strDate) = Val(strDate)
>
> or, for our US formatted friends:
>
>   Month(strDate) = Val(strDate)
>
> /gustav
>
> >>> pcs at azizaz.com 03-12-2004 03:01:01 >>>
> Hi, amazing how inexhaustible this subject is....
>
> I have an unbound textcontrol formatted dd mmm yyyy or dd mmm yy
>
> If I enter 32 Dec 2004 Access will cut in and tell me: Not a valid
entry
> etc....
>
> If I enter 32 Dec 04 - Access interprets as 4 Dec 1932
>
> When I test for a valid date in the beforeupdate event using IsDate()
it
> tests on the 4 Dec 1932 value ....
>
> What am I doing wrong here?
>
> Or rather : how do I catch obvious incorrect entries like 32 Dec 04
using
> IsDate() before Access let them slip through?
>
> Currently I test entry in text control box on the validation rule
> property:
> >=#1/01/2001#
> Hmmmm....
>
> Regards
> Borge
>
> ----- Original Message ----- 
> From: "S D" <accessd667 at yahoo.com>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Sent: Wednesday, November 03, 2004 5:39 PM
> Subject: Re: [AccessD] CheckDate?
>
> > Gustav Brock <gustav at cactus.dk> wrote:
> > Hi Sander
> >
> > > However, I added some extra checks to rule out 'impossible
dates'
> > > like 29,30 or 31 februari, 31 november, etc.
> >
> > Well, that's the point - IsDate() checks that as well!
> > Both IsDate("31-11-xxxx") and IsDate("11-31-xxxx") will fail.
> >
> > /gustav