Where clause doesn't work on date field

Dear all experts,  I've a table which contains some a date field, when I run a query it cannot returns the value that I want, is there any thing wrong in my query?

Select * from Q_Analystz_Report where Type = 'C' contract_date > #01/03/2012#

I expect the above query can return the records which is larger than 1 Mar 2012 (record should up to 17 Mar 2012) but only one record returns.

Please note, the field is in DD/MM/YYYY format
LVL 1
towo2002Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AshokSr. Software EngineerCommented:
According to Microsoft

http://office.microsoft.com/en-us/access-help/where-clause-HA001231526.aspx

When you specify the criteria argument, date literals must be in U.S. format, even if you are not using the U.S. version of the Microsoft Access database engine. For example, May 10, 1996, is written 10/5/96 in the United Kingdom and 5/10/96 in the United States.

So try.....

Select * from Q_Analystz_Report where Type = 'C' contract_date > #03/01/2012#

HTH
Ashok
0
towo2002Author Commented:
Dear Ashok,

Thanks for your quick reply.  Is there anyway to use UK / Australia format?
0
AshokSr. Software EngineerCommented:
By the way.....

To find records dated May 10, 1996 in a United Kingdom database, you must use the following SQL statement:
SELECT * FROM Orders WHERE ShippedDate = #5/10/96#;

You can also use the DateValue function which is aware of the international settings established by Microsoft Windows®. For example, use this code for the United States:
SELECT * FROM Orders WHERE ShippedDate = DateValue('5/10/96');

And use this code for the United Kingdom:
SELECT * FROM Orders WHERE ShippedDate = DateValue('10/5/96');

HTH
Ashok
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

AshokSr. Software EngineerCommented:
Is there anyway to use UK / Australia format?

See my post above.

Ashok
0
towo2002Author Commented:
Dear Ashok,

Another question, how to identify the date format (or regional settings) for my access database?  I'm using English Windows 7 and regional settings is English Australia (date format is DD/MM/YYYY)

I'm using Access 2010.
0
AshokSr. Software EngineerCommented:
If regional setting is DD/MM/YYYY, DateValue function should work.

Try.....(copy & paste from below)

Select * from Q_Analystz_Report where Type = 'C' contract_date > DateValue('01/03/2012')

HTH
Ashok
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
towo2002Author Commented:
Sorry Ashok, one more question.  If I pass this query from VBA / VBScript, I still have to have to use US format?
0
AshokSr. Software EngineerCommented:
I think you will have to use US format if you use SQL from VBA.
Query to Access is handled by Access Engine no matter how you pass it.

I cannot say it for sure.  Only way to verify how it will behave is to try it from VBA.

HTH
Ashok
0
towo2002Author Commented:
Just did try, have to use US format ;-(
0
AshokSr. Software EngineerCommented:
US format with # signs.

You can use DateValue function in SELECT statement and you do not have to use US format (you know it by now).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.