open report based on Crosstab query from a form

[THWatson]

Using Access 2000

The SQL for the crosstab query is

TRANSFORM Count(tblAttendance.Present) AS CountOfPresent
SELECT tblMembers.MemberID, [LastName] & ", " & [FirstName] AS FullName, Count(tblAttendance.MeetingDate) AS [Meetings Attended]
FROM tblMembers LEFT JOIN tblAttendance ON tblMembers.MemberID = tblAttendance.MemberID
WHERE (((tblMembers.Status)<>"Deceased" And (tblMembers.Status)<>"Transferred Out") AND ((tblAttendance.Present)=Yes) AND ((tblAttendance.MeetingDate) [b]Between #11/13/2008# And #11/20/2008#))[/b]
GROUP BY tblMembers.MemberID, [LastName] & ", " & [FirstName], tblMembers.Status
ORDER BY [LastName] & ", " & [FirstName], Format([MeetingDate],"mmm dd, yyyy")
PIVOT Format([MeetingDate],"mmm dd, yyyy");

I am trying to open a report based on a Crosstab query, from a form.

The form is an unbound form called frmDateSelector. It has a txtStartDate in which to enter the Start Date for the period, and txtEndDate in which to enter the End Date for the period.

Behind a command button to preview the report is the following code...

DoCmd.OpenReport "rptAttendanceTotalsForPeriod", acViewPreview, , "[MeetingDate] Between #" & Forms!frmDateSelector!txtStartDate & "# AND #" & Forms!frmDateSelector!txtEndDate & "#"

This results in "Forms!frmDateSelector!txtStartDate is not recognized as a valid field name or expression."

When I try to change the dates in bold in the SQL above, to "Between [Forms]![frmDateSelector]![txtStartDate] And [Forms]![frmDateSelector]![txtEndDate]" the same error results.

A push in the right direction would be appreciated.

Thanks.

Tom

 

[THWatson]

I was able to get the Between [Forms]![frmDateSelector]![txtStartDate] AND [Forms]![frmDateSelector]![txtEndDate] to work in the query, by filling in the Parameters boxes.

However now, in the form, the error I get is Microsoft Jet database engine does not recognize "" as a valid field name or expression.

Tom

 

[THWatson]

I finally figured it out.

I had created a circular reference.

Things are working fine now.

Tom

 

[dhookom]

I'm not sure how you are creating your report with bound controls and column headings but you might want to take a look at faq703-5466. A few modifications to change the months to days might meet your needs.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Thanks for the suggestion.

Actually, all I want for this particular purpose is to be able to get the Count of the number of meetings individual members attended during a selected period of time. So I can do that just as well with a much simpler totals query and not use a crosstab query for this situation.

I will keep your suggested approach in case I need it down the line in this database.

All the best of 2009!!

Tom

 

[dhookom]

Tom,
Can I ask how you get date columns across the page with a totals query?

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Thanks for the question.

After checking with my friend for whom I am building the database (to keep track of Members in his Kiwanis Club and their attendance, I find that I don't really need to get the date columns to display.

For this particular report, I only need to get the total # of members who attended on a specific date or range of dates.

I also need to get a list of members who attended on a specific date or dates. That's done separately.

Earlier, I was assuming that I needed the date columns, but learned that I don't.

Thanks again for taking the time to think about this.

Tom

 

[dhookom]

Tom,
Thanks for the update. I was wondering if there was a simple crosstab reporting solution that I wasn't aware of.


Duane
Hook'D on Access
MS Access MVP