Comparing Dates in Access Query

[meinhunna]

I have a column (JoinDate) which stores data in DateTime data type. How can I compare values in the column which range of dates? I tried the following, but didn’t return me any records


SELECT FistName, LastName
FROM TableName
WHERE JoinDate BETWEEN #1/1/2002# AND #01/10/2001#

What am I doing wrong

 

[PHV]

And this ?
BETWEEN #01/10/2001# AND #1/1/2002#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GingerR]

Do you have a field FistName (or is it FirstName?), and your table is called TableName, and you have data where the JoinDate is indeed between those two dates?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[meinhunna]

nops still no result

 

[meinhunna]

GingerR i do have data in the table where JoinDate is b/w those 2 dates and column name was just a typo

 

[meinhunna]

okay i tried this query on a different table and still no results

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

 

[PHV]

And this ?
AND Period BETWEEN #9/01/2004# AND #12/01/2004#;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[meinhunna]

PHV this did work???
Is this to do something with formats???

 

[PHV]

When a date is ambigue access always choose US format, ie m/dd/yyyy.
Finally I suggest this:
AND Period BETWEEN #2004-09-01# AND #2004-12-01#;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[meinhunna]

#2004-09-01# AND #2004-12-01# does this always ensures yyyy-MM-dd

 

[PHV]

Yes, without any ambiguity.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[meinhunna]

Thanks