[AccessD] CheckDate?

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
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


---
Outgoing mail is certified Virus Free by AVG Anti Virus System.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.799 / Virus Database: 543 - Release Date: 19/11/2004