Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MS Access (http://www.go4expert.com/forums/ms-access-forum/)
-   -   Compare Dates in MS Access (http://www.go4expert.com/forums/compare-dates-ms-access-t17834/)

coderzone 2Jun2009 15:34

Compare Dates in MS Access
 
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 2Jun2009 17:12

Re: Compare Dates in MS Access
 
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 ' ;)

sriram225 2Jun2009 17:22

Re: Compare Dates in MS Access
 
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 2Jun2009 17:24

Re: Compare Dates in MS Access
 
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 2Jun2009 17:27

Re: Compare Dates in MS Access
 
Quote:

Originally Posted by sriram225 (Post 48849)
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 (Post 48849)
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 2Jun2009 21:08

Re: Compare Dates in MS Access
 
Dude Here also same problem............cool KID

sriram225 2Jun2009 21:12

Re: Compare Dates in MS Access
 
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.


All times are GMT +5.5. The time now is 14:55.