single date or a date range entered 1

[srenner]

I am trying to write a query that would enable me to search on a single date or a date range entered on a "Report Criteria Form".

The criteria specified in the query is:

Between [Forms]![Report Criteria Form]![Start Date] And [Forms]![Report Criteria Form]![End Date]

It seems to work okay for a range of dates.

However it does not pull up records when the SAME date is entered in BOTH Start and End Date fields on the Report Criteria Form. I would like to add functionality to search for a single date OR range of dates.

What's the best way to modify this query to use only [Start Date] IF [End Date] is null or equal to the [Start Date], OTHERWISE use the full range between [Start Date] and [End Date]?

Many thanks for your assistance

Sylvester

 

[GoDawgs]

If you make sure that the user enters a date for both start date and end date then you could use something like this:

>=[Forms]![Report Criteria Form]![Start Date] And <=[Forms]![Report Criteria Form]![End Date]

I think the problem you are getting with your between statement is that nothing is between the same number, I believe it's like using > and < instead of >= and <= if that makes sense. Hope that helps.

 

[srenner]

Thanks godawgs

I modified query to finally look like this...

>=[Forms]![Report Criteria Form]![Start Date] And <[Forms]![Report Criteria Form]![End Date]+1

* removed = sign
* added +1

Works great