Trying to Filter Based on Date Ranges

[JHNielson]

I have a query where I want to find records where they are between two dates.
I am currently using the criteria of :

>[FORMS]![Report - Data Owner Record Status].[MIN_DATE]
<[FORMS]![Report - Data Owner Record Status].[MAX_DATE]

But it doesn't work. The query filters out all records.

What do I have to do to fix this?


Thanks for any help I can get.

[JimInAJam]

Is the data on the form a date type?

Have you tested to see if half of the expression works?

Have you tried to put constant form the form in the select list so that you can check its picking up and evaluating.

[CrostonScottish]

In your query you could set the criteria for the date field you want to filter by to:
Between [Enter begining date] And [Enter Finish Date ]

This will prompt the user when they run the query to input a start date then an end date.

Hope this is what you are looking for.

[JHNielson]

Thanks for everyone's help.

Yes, the form fields are formatted as dates. I don't want to make them enter a date in the query prompt, because they are already entering a date in the form.

Any other ideas?

[Rabbit]

Shouldn't you use BETWEEN?
The way your criteria is set up right now it'll return all dates.

As long as the field in your table is a date format and the control on the form is a date format it should work.

[JHNielson]

Shouldn't you use BETWEEN?
The way your criteria is set up right now it'll return all dates.

As long as the field in your table is a date format and the control on the form is a date format it should work.

I've never used the BETWEEN syntax. What is the syntax for BETWEEN? That might be what I need to do.


Thanks

[Rabbit]

FieldName BETWEEN Value1 AND Value2

[JHNielson]

FieldName BETWEEN Value1 AND Value2

Thanks again - Works great!

[Rabbit]

Good Luck.

[NeoPa]

FieldName BETWEEN Value1 AND Value2

That's the most usual form, but it can be used more flexibly. For instance, to determine if a record (which has [StartDate] & [EndDate] fields) is currently active, you could say (in SQL mode) :

WHERE Date() Between [StartDate] And [EndDate]