Need simple Sum in query, it's driving me nuts! 1

[CliffordTheRedDog]

I have a query which retrieves 5 fields called attendance
each field may or may not have a name in it
I need to calculate total attendance, up to 5 people obviously!
if there is a null entry the query returns a zero if there is an entry the field displays a 1.

example of function below (this bit works ok)
Att1: IIf(IsNull([Attendance1]),"0","1")
Att2: IIf(IsNull([Attendance2]),"0","1") etc.

I calculate total attendance with the following but it is not working.

NumAttendance: [Att1]+[Att2]+[Att3]+[Att4]+[Att5]

if all entries are null then i get 00000
if 1 3 and 5 are null i get 01010 instead of 2

TIA
It's gotta be so simple!!!

 

[Golom]

Try

NumAttendance: NZ([Attendance1])+NZ([Attendance2])+
               NZ([Attendance3])+NZ([Attendance4])+
               NZ([Attendance5])

 

[PHV]

0" and "1" are strings, not numbers !
So you should do this:
Att1: IIf(IsNull([Attendance1]),0,1)
Att2: IIf(IsNull([Attendance2]),0,1) etc.


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CliffordTheRedDog]

PHV,
Of course they are strings! Duh what a prat! Thanks, that has sorted it fine.

Golom,
Thanks for your help I'll go with PHV for this one, it works fine so far.
Cliff