Include Date Range Parameter in results

[jjb373]

I have a query which returns the results between an appointment date and a results date. This query eliminates any results that are less than 24 hours by making them null. Thus, only showing results > 24 hours (or not null)

The query prompts the user to enter a start date (mm/dd/yyyy) and end date (mm/dd/yyyy).

This formula below is in the Report Footer:
=DCount("[Documents]![HX Documents]","Documents")

It returns the count of "HX Documents" from the table. This is what I want it to do, because it includes the Null values which are not included in the query because of my formula I described above: by assigning results < 24 hours as Null.

All I want to do is take that number I am getting already (from the formula), and eliminate all results which are not in the date range the user enters in the parameter when the report(query) opens.

 

[dhookom]

First, kick you development up a notch and don't use parameter prompts. Use controls on forms for users to enter criteria. There is generally no substitute for this.

I would probably include all the records with in the date range in your report but cancel the printing of the records where the results is <24 hours. You should be able to create your average without using DCount().

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jjb373]

Out of curiosity why would I put controls (which you mean textboxes??) on my form. I am not using a form to look at any of the data because all they want is to view the data in a report which they pass along. The only form I have is one to enter the data and one with buttons or shortcuts to open a report or open the form to add the data, or close the program.

What do you mean by cancel the printing of the records where the results are <24 hours.

I really am just wanting to know how I can eliminate the records outside the desired date range from this total count formula.
=DCount("[Documents]![HX Documents]","Documents")

 

[dhookom]

I answered your "cancel the printing" in another thread you started.

Parameter prompt queries are not for user consumption. Consider Martin Green's tutorial at

http://www.fontstuff.com/access/acctut08.htm.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]