[coderzone]

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

[SaswatPadhi]

I think this should work :

SELECT *
FROM Ledger
WHERE TranDate BETWEEN #1/1/2008#  AND #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 '

[sriram225]

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 ......

[sriram225]

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

[SaswatPadhi]

Quote:

Originally Posted by sriram225

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

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.

[sriram225]

Dude Here also same problem............cool KID

[sriram225]

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.