IIf function not producing desired results

  • Thread starter Thread starter Hugh
  • Start date Start date
H

Hugh

Hello,
I am using Access 2000 and this problem is driving me bonkers! On a report I
have several controls named "Game 1", "Game 2", and "Game 3" which receive
their values from the query that the report is based upon. The query is
based upon a table in which I manually record the values. If a "Player"
misses a week his/her name is entered into the table but no scores (values)
are entered. I have another control named "Games Played" that keeps track of
the number of games played to date and is also used to calculate an average
for the player.

Problem: The "Games Played" control receives it's value from three IIf
statements on the report, to wit:
=IIf([Game 1] ,1,Null) and so on with Games 2 and 3.
The "Games Played" control source reads thus: =[Text32]+[Text33]+[Text34];
those corresponding to the IIF controls. This works fine unless someone
missed the first week then all the records following, including the first,
return a "0", AND if someone misses a week somewhere down the line the
"Game" controls are empty on the report yet the "Games Played" increments to
three more games in the Running Sum over the group. For example, it will
show 15 games being played when in actuality only 12 have been played.

I've tried several variations in the IIf function such as: =IIf([Game
1]=0,0,1). I've tried using IsNull, <=, >=, in the statement and nothing has
solved the problem. If any one has a solution to this problem I would
greatly appreciate your help. Also, does anyone know of any books out there
that covers these type of problems with possible solutions?

Hugh
 
You are committing spreadsheet if you have fields like Game1,...
To count the number of game fields that are not null, use:
= 3+IsNull([Game 1])+IsNull([Game 2]) + IsNull([Game 3])
 
Back
Top