434,572 Members | 963 Online
Need help? Post your question and get tips & solutions from a community of 434,572 IT Pros & Developers. It's quick & easy.

Count fields that are not null

 P: n/a Hello all, I have an Access 2003 database running on an XP network. I have a subform containing a 28 day roster - shift1 to shift28. I need to total the shifts for each employee and am unsure of how to do thid. Each record has 1 EmployeeNumber, 28 shift fields, and 1 shiftTotal field and 1 HoursTotal field. I investigated the Count function but I think this Counts on the basis of a field (Column) not a Record. Does anyone have an idea how I should tackle this problem or am I on the wrong track altogether? Regards Kevin Mar 20 '07 #1
5 Replies

 P: n/a On Mar 19, 11:10 pm, "Kev"

 P: n/a I think you want something like sum (iif (shift1, 1, 0). This assumes shift1 is boolean of course. Count won't work as you found out. Hello All, Unfortunately Eng, Shift1 through to Shift28 are all text, not boolean. I still do not understand how your solution would count or sum the shifts for a record (datasheet row). Isn't your solution summing all the non nulls for the shift1 field (the column). Sorry if I'm missing something which I obviously am. I was thinking of something like the function below in the afterUpdate event procedure but it seems like a lot of code and processing each time someone makes a change: Does anyone know of a more efficient method? I've never used the Select Case statement before, would this be appropriate or is there a predefined function for doing this? Regards Kevin Public Function ShiftCounter() Dim shiftCount As Long shiftCount = 0 If (IsNull(Shift1)) Then Else shiftCount = shiftCount + 1 End If If (IsNull(Shift2)) Then Else shiftCount = shiftCount + 1 End If If (IsNull(Shift3)) Then Else shiftCount = shiftCount + 1 End If If (IsNull(Shift4)) Then Else shiftCount = shiftCount + 1 End If If (IsNull(Shift5)) Then Else shiftCount = shiftCount + 1 End If Me![ShiftTotal] = shiftCount End Function Mar 20 '07 #3

 P: n/a On Mar 20, 3:52 pm, "Kev"

 P: n/a On Mar 20, 3:52 pm, "Kev"

 P: n/a On Mar 21, 7:17 pm, eng...@ridesoft.com wrote: On Mar 20, 3:52 pm, "Kev"