Access 2007 reports to display rows based on date range given by user (1 Viewer)

[Frankat]

Hi All, I am stuck for many days now.

What is easiest way where I can run the report showing three variables (Total Count, Total Count of Males, and Total Count of Females) based on user input for date range provided.

EntryDate is date column in table.
Sex is the column where Male or Female is stored as text.

Thanks,

 

[Beetle]

1) Create a form, let's call it frmSearchByDate, with two unbound text boxes named, for example, txtStartDate and txtEndDate where the users will enter the desired date values.

2) Create a query like the following that references the text boxes on the SearchByDate form;

SELECT Count(EntryDate) AS TotalCount, Sum(IIf([Sex]="m",1,0)) AS Males, Sum(IIf([Sex]="f",1,0)) AS Females
FROM YourTable
WHERE YourTable.EntryDate Between [forms]![frmSearchByDate]![txtStartDate] And [forms]![frmSearchByDate]![txtEndDate];

3) Use this query as the Record Source of your report.

Note: Keep in mind that the SearchByDate form needs to remain open (you can make it hidden if you want) while the report is run.

 

[Frankat]

Hi Beetle,

Your qry works perfect. I set it to the source of report as you instructed. The issue I having is that two text boxes comes up where I put start and end date. That is fine But I see so many other text boxes shows up as well, like EntryDate, Lastname, etc,etc.
that is confusing me.
Also on my report,I have three text boxes that I want the results to be shown in. Like TotalCount, TotalMaleCount and TotalFemale Count.

What should be code in VBA for button click event?
I appreciate your help. Looks like I am getting somewhere.

 

[Frankat]

I got it. Thanks BEETLE. You are greatttttttttttttttttttttttt.