Multiple Date Criteria

[Dwight]

Hello:

I need a query to find the total (sum of) active client reports on a specified date – StartDate. My table has a ReportOpen date field and a ReportClose date field. I plan to use a parameter query that uses criteria supplied by a data entry form to allow the user to enter a date range i.e. Start date and End date to search.

I believe it is correct to say that an active report would have the following conditions:

1) A ReportOpen date less than the Start Date
2) AND the ReportClose date is either Empty (because it is open) OR greater than the Date.

I’m a little rusty on my query syntax and would appreciate help/advice on how to structure the SQL.

The report will also require the total change in client reports for the date range (number opened and number closed), the net increase for the period (which will be a calendar year), and then the number of active reports at the end of the year.

I don’t expect anyone to write all this but I would be interested if it is recommended to calculate these figures in the query the report is based on or in the report itself.

Any help is appreciated. Thank you in advance,

Dwight

 

[Mile-O]

1) A ReportOpen date less than the Start Date
2) AND the ReportClose date is either Empty (because it is open) OR greater than the Date.

In the query grid, use a reference to the open date field:

i.e.

Forms!YourForm!StartDate

And for the closed field:

(Is Null or Forms!YourForm!CloseDate)

 

[Dwight]

Thank you. It was the Is Null that I was missing. I was using """" which caused an error.

 

[Mile-O]

There is a difference between Is Null and a Null String.

Is Null means that there is absolutely no value.
A Null String has a value despite seemingly being a zero length string of text.