Date Comparison

[Me]

In Access database I have a table linked to sql server database. I would like
to do the date comparison like

select ... from <tablename> where podate > 'fromdate' and podate < 'todate'

in this query fromdate could be like '1/1/1997' and todate like '31/12/2001'

when I run the query, I get error data type mismatch.

Can someone help me in writing the correct query?

Thank you,
-Me

 

[Tom Ellison]

Dear You:

Please try something once, very simple.

Enter all dates strictly MM/DD/YYYY. Do not enter 31/12/2001 but
12/31/2001.

Does that make a difference?

Next, if that isn't enough, use "CDate() around the entered dates.

A date like 1/3/2006 would be ambiguous. Is it January 3 or March 1? To
avoid ambiguity like this, it will use strictly MM/DD/YYYY to interpret
entered dates. 31/12/2001 is therefore not a date at all.

Tom Ellison

 

[John Spencer]

IF Tom Ellison's solution doesn't work for you, you might try delimiting the
dates with # marks versus ' marks.

See International Dates in Access at:
http://allenbrowne.com/ser-36.html

 

[Tom Ellison]

Dear John:

Yes, probably so. Not if it's for MSDE (where I uusually work, and why I
didn't catch that, especially as Me used single quotes, which looks all the
more like MSDE), but then most questions here are for Jet.

My mistake!

Tom Ellison