Access date validation driving me nuts

[carriolan]

Hi
I have a very simple problem indeed, but I am unable to solve it. I
am inputing a short date into an unbound text box with a short date
input mask. I am in the UK so my date format is dd/mm/yyyy. Everything
works superbly well with one exception.
Example: Suppose I accidentally keyed in 04/13/2005 the program
assumes that I meant 13/04/2005 and swaps the day and the month field
around without displaying an error, so the date is incorrectly
processed as 13/04/2005. How do I stop this please?

Regards
Carriolan

[Arno R]

<carriolan@> schreef in bericht news:n9********************************@4ax.com...

Hi
I have a very simple problem indeed, but I am unable to solve it. I
am inputing a short date into an unbound text box with a short date
input mask. I am in the UK so my date format is dd/mm/yyyy. Everything
works superbly well with one exception.
Example: Suppose I accidentally keyed in 04/13/2005 the program
assumes that I meant 13/04/2005 and swaps the day and the month field
around without displaying an error, so the date is incorrectly
processed as 13/04/2005. How do I stop this please?

Regards
Carriolan

Hi Carriolan,
I don't think you can stop Access from trying to be a intelligent program. ;-)
Since there is NO month 13 Access guesses you made a mistake.
AFAIK you can not stop this behaviour.
You can however check the date yourself (before update event) and present a nice messagebox to the user.
Let us know if you need more help on this.

Arno R

[Ken Post]

How about wring some vba to check each portion of the date?

Split the string based on location of / or - into an array then process
the array, making sure that month is between 1 and 12. If it isn't
give error msg.

[carriolan]

Hi
I found auto correct was set to 'yes'. Now this gets me part way
there. It prevents the date from being switched around, but it still
does not trap the error 04/15/2005. This is really strange as I am
using a default date of today which comes up in dd/mm/yyyy format. It
appears to allow both dd/mm/yyyy and mm/dd/yyyy formats - how do I
stop this please?

Regards
Carriolan

[carriolan]

Hi
I found auto correct was set to 'yes'. Now this gets me part way
there. It prevents the date from being switched around, but it still
does not trap the error 04/15/2005. This is really strange as I am
using a default date of today which comes up in dd/mm/yyyy format. It
appears to allow both dd/mm/yyyy and mm/dd/yyyy formats - how do I
stop this please?

Regards
CarriolanOn Sun, 4 Dec 2005 23:58:37 +0100, "Arno R"
<ar***********@tiscali.nl> wrote:

<carriolan@> schreef in bericht news:n9********************************@4ax.com...

Hi
I have a very simple problem indeed, but I am unable to solve it. I
am inputing a short date into an unbound text box with a short date
input mask. I am in the UK so my date format is dd/mm/yyyy. Everything
works superbly well with one exception.
Example: Suppose I accidentally keyed in 04/13/2005 the program
assumes that I meant 13/04/2005 and swaps the day and the month field
around without displaying an error, so the date is incorrectly
processed as 13/04/2005. How do I stop this please?

Regards
Carriolan

Hi Carriolan,
I don't think you can stop Access from trying to be a intelligent program. ;-)
Since there is NO month 13 Access guesses you made a mistake.
AFAIK you can not stop this behaviour.
You can however check the date yourself (before update event) and present a nice messagebox to the user.
Let us know if you need more help on this.

Arno R

[Lyle Fairfield]

Many developers do not permit the direct entry of a date because of the
difficilties you describe. They use some form of a pop-up calendar and
a click on date method which populates the text field. There are many
pop-up calendars and a google search of this newsgroup is likely to
locate some good ones.
You can format the text box as something like d-mmm-yyyy which may give
you something like 4-dec-2005, but when you set the focus to the
control, it's likely the date will show in your regional settings
format, in my case, 2005-12-04.
On the net, one rarely sees the direct entry of a date being allowed. A
common solution seems to be three combo boxes, one for year, one for
month (names rather than numbers) and day. This of course, requires
some coding to get the date from the three combo boxes.

[Larry Linson]

"Lyle Fairfield" <ly***********@aim.com> wrote

Many developers do not permit the direct entry of a date because of the
difficilties you describe. They use some form of a pop-up calendar and
a click on date method which populates the text field. There are many
pop-up calendars and a google search of this newsgroup is likely to
locate some good ones.
Two calendar-style date pickers, from knowledgeable sources, may be found at
Stephen Lebans' site, http://www.lebans.com and Drew Wutka's site,
http://www.wolfwares.com. There are many others that can be found, as Lyle
states, by Googling. Both of these, IIRC, are done entirely with native
Access objects, so do not require distributing an ActiveX control with your
database.
On the net, one rarely sees the direct entry of a date being allowed. A
common solution seems to be three combo boxes, one for year, one for
month (names rather than numbers) and day. This of course, requires
some coding to get the date from the three combo boxes.

And, it seems hardly worth the trouble, considering the wide availability of
easy-to-use date pickers where experienced developers have already done the
"heavy lifting".

Larry Linson
Microsoft Access MVP

[carriolan]

Hi Larry
I think you have it. Custom and practice dictate that I should not
allow direct date entry into a date field. I have one question
however. I use Access 2003, which has a calendar control. Is there a
catch to using this, if I am going to ditribute the app to 3rd
parties?

Regards
Carriolan

On Mon, 05 Dec 2005 04:17:24 GMT, "Larry Linson"
<bo*****@localhost.not> wrote:

"Lyle Fairfield" <ly***********@aim.com> wrote

Many developers do not permit the direct entry of a date because of the
difficilties you describe. They use some form of a pop-up calendar and
a click on date method which populates the text field. There are many
pop-up calendars and a google search of this newsgroup is likely to
locate some good ones.

Two calendar-style date pickers, from knowledgeable sources, may be found at
Stephen Lebans' site, http://www.lebans.com and Drew Wutka's site,
http://www.wolfwares.com. There are many others that can be found, as Lyle
states, by Googling. Both of these, IIRC, are done entirely with native
Access objects, so do not require distributing an ActiveX control with your
database.

On the net, one rarely sees the direct entry of a date being allowed. A
common solution seems to be three combo boxes, one for year, one for
month (names rather than numbers) and day. This of course, requires
some coding to get the date from the three combo boxes.

And, it seems hardly worth the trouble, considering the wide availability of
easy-to-use date pickers where experienced developers have already done the
"heavy lifting".

Larry Linson
Microsoft Access MVP

[carriolan]

Hi Lyle
This is v. good advice - thanks.
Regards
Carriolan
On 4 Dec 2005 15:48:37 -0800, "Lyle Fairfield" <ly***********@aim.com>
wrote:

Many developers do not permit the direct entry of a date because of the
difficilties you describe. They use some form of a pop-up calendar and
a click on date method which populates the text field. There are many
pop-up calendars and a google search of this newsgroup is likely to
locate some good ones.
You can format the text box as something like d-mmm-yyyy which may give
you something like 4-dec-2005, but when you set the focus to the
control, it's likely the date will show in your regional settings
format, in my case, 2005-12-04.
On the net, one rarely sees the direct entry of a date being allowed. A
common solution seems to be three combo boxes, one for year, one for
month (names rather than numbers) and day. This of course, requires
some coding to get the date from the three combo boxes.

[Arno R]

<carriolan@> schreef in bericht news:4i********************************@4ax.com...

Hi Larry
I think you have it. Custom and practice dictate that I should not
allow direct date entry into a date field. I have one question
however. I use Access 2003, which has a calendar control. Is there a
catch to using this, if I am going to ditribute the app to 3rd
parties?

IMO it's better to use this on or another not-activeX one:
http://allenbrowne.com/ser-51.html

Arno R

[Lyle Fairfield]

Arno R wrote:

<carriolan@> schreef in bericht news:4i********************************@4ax.com...

Hi Larry
I think you have it. Custom and practice dictate that I should not
allow direct date entry into a date field. I have one question
however. I use Access 2003, which has a calendar control. Is there a
catch to using this, if I am going to ditribute the app to 3rd
parties?

IMO it's better to use this on or another not-activeX one:
http://allenbrowne.com/ser-51.html

Arno R

Thirty-three of us have written the best popup calendar and each of them
is better than the other thirty-two.

--
Lyle Fairfield

[David W. Fenton]

"ca*******@blootoo.com" <carriolan@> wrote in
news:vv********************************@4ax.com:

I found auto correct was set to 'yes'. Now this gets me part way
there. It prevents the date from being switched around, but it
still does not trap the error 04/15/2005. This is really strange
as I am using a default date of today which comes up in dd/mm/yyyy
format. It appears to allow both dd/mm/yyyy and mm/dd/yyyy formats
- how do I stop this please?

Are you sure your Windows location settings are correct? I would
have thought that if the Windows regional settings are correct, that
date formats would get handled according to UK standards.

I once had to deal with this for an application that was used in NYC
and London, and it worked out fine. But I did *not* use input masks.
Perhaps it is only the input masks that are causing the problem?

The one place where I had to take action was in SQL strings written
in code, where I had to use DateSerial(), since Jet SQL works only
with US date order.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc