Calculated Field Problem (DCount) 1

[Ally72]

This should be so simple, but it’s completely baffled me. Hope this doesn't confuse you - it's a bit detailed.

On my report I have a list of Staff, Courses etc and whether they Attended, Cancelled or DNAd (Did Not Attend) –fieldname = “AttendanceID”. At the end of each section, (broken down by Organization in Organization Footer), I’d like to breakdown the number of Attended (1), Cancelled (2) and DNAd (3).

Eg:
Staff1 MS Word Beg Attended
Staff2 MS Word Adv DNA
Staff3 MS Access Attended
Staff4 MS Excel Cancelled
- - - - - - - - - - - - - - - - - - - -
Attended: 2
Cancelled: 1
DNA: 1

To obtain Attended I tried the following in a text box:

=DCount("[attendanceid]","qryMonthlyStaff","[attendanceid]=1&quot

But it doesn’t work – just gives #Error. Have tried various ways of doing the DCount - using square brackets, not using them, single quotes around the criteria etc etc.

I tried removing the date parameters in the query that feeds the report just to see if it was them that's causing the problem - and it works, but is giving me the number attended for the whole report and not for each organization, even though it's in the organization footer.

Tried it in VBA behind the report with a Label.Caption. This produced:

Run-time error ‘64479’.

The expression you entered as a query parameter produced this error: “The object doesn’t contain the Automation object “Enter Start Month yy/mm.”

Also tried a loop in a function but that came up with parameter errors too.

I would have thought the DCount should have done it. I'd be very grateful for any help / comments please.

 

[LittleSmudge]

DCount will took at the whole table source as specified by the tableName and Where clause criteria that you give it. It will NOT recognise any of the Report's Grouping and Sorting just because of the location that you place it on the Report layout.

How about having a hidden field next to the Attendance field, Call it DidCancel and set the ControlSource as:-
=IIf(AttendanceFieldName = "Canceled",1,0)
Then next to that another hidden field called CanceledCount and set the ControlSource = DidCancel and the Sum parameter to SumOverGroup
Then in the Footer you have a control with the ControlSource = CanceledCount and it will display the last value of CanceledCount in the Detail section above.

Repeat for DNA & Attended.



'ope-that-'elps.

G LS

 

[Garridon]

I went through this myself. DCount will also give that error message if you try to put it in the report footer.
Linda Adams
Visit my web site for writing and Microsoft Word tips:

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com

 

[Ally72]

Thanks so much LittleSmudge. It took a little while, but it's got it sorted.

Cheers - Ally