Searching between two dates

[desk4tbc]

Hello,

I'm having troubles creating a query to return records between a certain range of dates. Below is my current query.

SELECT DISTINCT CMS.CMSRID, tmpCMS.FDOS
FROM CMS LEFT JOIN tmpCMS1500 ON CMS.CMSRID = tmpCMS.CMSRID
WHERE (((tmpCMS.FDOS) Between #1/1/2011# And #10/13/2011#));

But it returns dates from 2010. What gives?

[RuralGuy]

Is the FDOS field a DateTime field or a text field?

[desk4tbc]

It's a DateTime field [Edit: I was wrong.]. I've played around with the search parameters (i.e. 04/15/2011-10/13/2011) and that still gives me options outside of the range (i.e. 02/10/2010).

EDIT: Figured it out. My make table query for the temp table overwrites the field settings and puts it as a standard text field. Can't believe I missed that. Thanks for the direction RG. Btw, is it possible to have a make table query set field settings or would it be better to use a delete and then an append query for a temporary table?

[RuralGuy]

I personally do not use MakeTable queries but just delete all and append if that helps. Glad to hear you got it sorted.