Why did this work?

[stevekos07]

Not often I fix something but don't know why it fixed the problem. I have a table with 320,000 records. When I tried to construct a query with criteria to filter by BETWEEN dates, I only got records showing with the first date in the range. I was going nuts, when I finally decided to index the dates in the design view. This fixed the problem. I can now construct queries that behave correctly but would not before the indexing.

Can anyone explain to me why? Just for future reference.

 

[Frothingslosh]

Please post both the before and after SQL.

You absolutely can filter BETWEEN dates without them being indexed - it's just a bit slower without the index, and even then should only make a noticeable difference if you have a LOT of records to dig through.

 

[arnelgp]

If there is Time element on the field and your filtering only by Date part, it will not return the result you expect. Either add Time to the filter or just input date to the field without the Time part.

 

[The_Doc_Man]

Before I open mount and insert foot, I need to ask: What data types are the fields in question? Actual dates or strings that contain dates in text format?