Calculated Field Problem

[Ally]

This should be so simple, but it’s completely baffled me.

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, I’d like to breakdown the number of Attended (1), Cancelled (2) and DNAd (3).

Eg:
Name- - - - - - - - - - Course - - - - - - - - -Attendance Details
Fred Flintstone- - - -MS Word Beg- - - - - Attended
Barney Rubble- - - - MS Word Adv- - - - -DNA
Joe Bloggs- - - - - - -MS Access- - - - - - -Attended
Posh Spice- - - - - - 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")

But it doesn’t work – just gives #Error. I’ve tried all sorts of variations of this and normal Count. 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. There are date parameters behind the query which feeds the report.

I would have thought the Dcount should have done it.

[This message has been edited by Ally (edited 05-22-2002).]

 

[Rich]

=Sum(Iif([AttendanceID]="Attended",1,0))

 

[Ally]

Sorry Rich:

The expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

 

[indyaries]

Ally,

It appears that your referencing the same field twice.

I use something like this to count the number of employees in a certain job series:

=DCount("[EmployeeID]","0_All CURRENT Employees","[Series] = '303'")

Without knowing the properties of your tables, I would guess the following might work:

=DCount("[attendanceid]","qryMonthlyStaff","[Attendance Details]='Attended'")

This should count the number of people who 'Attended'. Use the same for DNA and Cancelled.

Hope this helps!

Bob


[This message has been edited by indyaries (edited 05-22-2002).]

[This message has been edited by indyaries (edited 05-22-2002).]

 

[Ally]

No sorry - this doesn't work either!

Just gives #Error

[This message has been edited by Ally (edited 05-22-2002).]

 

[Ally]

Thanks for all the help. I got some help from ... shhh another forum. Put some hidden fields in Detail, CourseID Footer and Organization Footer. Complicated, but it worked. The link should anyone be interested.
http://www.tek-tips.com/viewthread.cfm?SQID=277930&SPID=703&page=1&CFID=64684265&CFTOKEN=56540502