Date format dd/mm/yy

[Guest]

I am frustrated as since a few hours I am trying to display a form that
executes a query with date. The date filter criteria are provided via the
form. I want to display all records with date >= a value supplied on the
form.

During execution, access treats this date as "mm/dd/yyyy" format despite all
my efforts to get it to treat it as "dd/mm/yyyy". thus if I am type in the
date as 1/8/2006, the form displays all records from 8/1/2006.

Extremely frustrating, can somebody help?

 

[Guest]

Hi,
check this:
http://www.mvps.org/access/datetime/date0005.htm
HTH
Good luck

 

[Allen Browne]

See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The article explains how literal dates in code and SQL clauses must be
handled differently than dates entered through the interface, and identifies
the 3 cases where Access can misunderstand our d/m/y dates.

 

[Guest]

Hi DKS,
Open Control Panel >> Regional and Language Options -> Click Customize ->
Choose tab Date, Change Short date format to: dd/MM/yyyy +> Apply/OK. Now you
should enter a date as dd/mm/yyyy.
Good luck.

 

[Guest]

Khoa,

This has already been done. All my spreadsheets and databases display dates
in dd/mm/yyyy by default. It is only in this form/query combination that I
get this behaviour.

 

[Guest]

Thanks, I think your url point 2 explains the problem. I am creating my
filter criteria based on dd/mm/yyyy format, whereas as you mention in your
point 2, regardless of your setting such dates must always be given in
mm/dd/yyyy setting. I believe that is the reason for the problem.

I will try changing my criteria format from dd/mm/yyyy to mm/dd/yyyy and see
whether that delivers the goods.

 

[Guest]

Yes, that did the trick. thanks for the help from all of you.

Lesson learnt: whatever format the date is in the user-interfaces, if a date
is supplied delimited by # then it must be in mm/dd/yyyy format.

 

[Guest]

Yes, this is the solution. Thanks.

Hi,
check this:
http://www.mvps.org/access/datetime/date0005.htm
HTH
Good luck