TOTAL column in query

  • Thread starter Thread starter Aurora
  • Start date Start date
A

Aurora

I am using Access 2003
I have a database in which I keep track of retirement inform such as hours
worked per year which is convered into credited service year by each year.
The credited service field is [94cs], [95cs] etc through [10cs]. My db
begins with 1994 and I have just expanded it to year 2010. I have a total
column that adds up the credited service years - TotCS:
(+[94cs]+[95cs]+[96cs]+[97cs]+[98cs]+[99cs]+[00cs])+([01cs]+[02cs]+[03cs]+[04cs]+[05cs]+[06cs]).
Up to this point it adds everything correctly. But as soon as I add year
07, 08 etc it will not add up anything. Is there a limit to number of items
I can use to add up? Is there a better way to handle this type of total?
I would appreciate any help anyone can give me.
Aurora
 
Yes, you cannot have more than 255 fields (for the whole life of the query,
compact if you reach that limit, since deleted field still counts, if you
don't compact).


That being said, you probably need to use Nz:
.... + [06cs] + NZ( [07cs], 0 ) + Nz( [08cs], 0 )


else, NULL value propagates. In the Debug window (or the Immediate window)
try:


? 1 + 2
3

? 1 + NULL
' no visible result, it is a NULL

? IsNull( 1 + NULL)
True




And Nz replaces, here, that possible null with a zero


? 1 + Nz( NULL, 0)
1




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top