Compare Dates in MS Access

coderzone's Avatar, Join Date: Jul 2004
Team Leader
I would like to compare dates in MS ACCESS

SELECT *
FROM Ledger
WHERE TranDate>1/1/2008 TranDate<1/1/2009
ORDER BY TranDate;

But when I use the above it gives no results where as there are lots of records for 2008 and when I use quotes it gives error saying datatype mismatch
0
SaswatPadhi's Avatar, Join Date: May 2009
~ Б0ЯИ Τ0 С0δЭ ~
I think this should work :

Code: SQL
SELECT *
FROM Ledger
WHERE TranDate BETWEEN #1/1/2008AND #1/1/2009#
ORDER BY TranDate;
I think you have used Oracle or SQLServer earlier, so you used quotes. But the delimiter in MS Access is # not '

Last edited by SaswatPadhi; 2Jun2009 at 17:15..
0
sriram225's Avatar, Join Date: May 2009
Go4Expert Member
U Haven't used "and" keyword to join both conditions..use it like this..
SELECT *
FROM Ledger
WHERE TranDate>1/1/2008 andTranDate<1/1/2009
ORDER BY TranDate;


And Also be Sure that TranDate is Text dataType ......
0
sriram225's Avatar, Join Date: May 2009
Go4Expert Member
U Haven't used "and" keyword to join both conditions..use it like this..
SELECT *
FROM Ledger
WHERE TranDate>#1/1/2008# andTranDate<#1/1/2009#
ORDER BY TranDate;

# delimitter must used in ms access
0
SaswatPadhi's Avatar, Join Date: May 2009
~ Б0ЯИ Τ0 С0δЭ ~
Quote:
Originally Posted by sriram225 View Post
U Haven't used "and" keyword to join both conditions..use it like this..
WHERE TranDate>1/1/2008 andTranDate<1/1/2009
That won't work without # delimiter.

Quote:
Originally Posted by sriram225 View Post
And Also be Sure that TranDate is Text dataType ......
TranDate is obviously Date data-type.

Sriram, don't be hurried to post.
Take your time and include all you want to say in one post.

Last edited by SaswatPadhi; 2Jun2009 at 17:32..
0
sriram225's Avatar, Join Date: May 2009
Go4Expert Member
Dude Here also same problem............cool KID
0
sriram225's Avatar, Join Date: May 2009
Go4Expert Member
Quote:
TranDate is obviously Date data-type.
Of course it is date datatype if we don't use # then trandate should be text datatype else if it is date datatype then we have to use #....Cool KID.