DATE VALIDATION - UK DATE FORMAT

[JMCS]

This is possibly a new twist in a recurring theme. I have a portable Access
application which is used by a number of business which on the whole are
fixed-term contracts, ie there will be no recordable date data earlier than
the contract start date. To prevent user error, I would like to change the
field validation for selected tables accordingly. My code works fine for any
date where the day is 13+, but reverts to US when it is 12-. I have used
Format as recommended by Douglas Steele etc, and in the Watches Window my
string is fine, but flunks in the table. I have even tried contructing a
string made up by selecting the day/month/year componenets separately, but
Access insists on over-riding it.
Can anyone advise, please?

JMCS

 

[Jack Leach]

I'm not exactly sure what you are looking to get at here, but as a general
rule you should not have to worry about how the date "format" is stored in a
table. Tables store a number to derive the date from and have no specific
format aside from the way it shows it to you. And, based on the fact that
your clients/users should never be in contact with a table directly, this
means the that format of the date displayed in a table is irrelevant. You
should be able to set this format in all of your forms and reports and not
have to worry about it elsewhere.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Linq Adams via AccessMonster.com]

The problems here are caused by the why Access Queries treat the dates.

Being from the British Commonwealth (Australia) and one of the most
experienced Access developers around, Allen Browne's short paper on
International Dates probably the best explanation to be found:

http://www.allenbrowne.com/ser-36.html

 

[JMCS]

Thanks to both contributers, but I worked this one out for myself. I hope my
solution works for others. What I have done is this - if the start day is 12
or less, I change it to 13, and store the difference in a variable which is
zero for 13 or over. The field validation statement includes both the date
AND the variable, eg ">=#13/11/2009#-1" for 12th November and ">=#13/11/2009#
- 0" for 13th November. It works!

Thanks again

JMCS