Comparing Dates in Access Query

[PankajBanga]

I tried this query and didn't get any result. What cud be wrong??? I do have data b/w those two dates in my table.

SELECT DPS
FROM tblDividend
WHERE CompanyKey =1582
AND Period BETWEEN #1/09/2004# AND #1/12/2004#;

Following is the data in tblDividend

CompanyKey DividendType PeriodEndDate DPS PaymentDate ExDividendDate Period
1582 Interim 30/09/2004 0.01875 15/11/2004 26/10/2004 1/09/2004
1582 Interim 30/12/2004 0.01875 15/02/2005 25/01/2005 1/12/2004

 

[Jon K]

Access treats any dates surrounded by the # signs as in US date format, so Period BETWEEN #1/09/2004# AND #1/12/2004# is Period between Jan 9 and Jan 12, 2004.

You can put the dates as BETWEEN #9/1/2004# AND #12/1/2004#.

Or avoid using the # signs, e.g.

SELECT DPS
FROM tblDividend
WHERE CompanyKey =1582
AND Period BETWEEN DateValue("1/09/2004") AND DateValue("1/12/2004");


See examples in this thread: How to make the code work in both US and UK date formats?
http://www.access-programmers.co.uk/forums/showthread.php?t=77354
.