calculating total with Nz - too long

  • Thread starter Thread starter Nadine
  • Start date Start date
N

Nadine

I am trying to calculate a total using Nz as follows,but
there are too many fields and it is too long. How can I
do this? Thanks for any help:

DBC_TOT = Nz(([1]) + Nz([2]) + Nz([3]) + Nz([4]) + Nz([5])
+ Nz([6]) + Nz([7]) + Nz([8]) + Nz([9]) + Nz([10]) + Nz
([11]) + Nz([12]) + Nz([13]) + Nz([14]) + Nz([15]) + Nz
([16]) + Nz([17]) + Nz([18]) + Nz([19]) + Nz(([20]) + Nz
([21]) + Nz([22]) + Nz([23]) + Nz([24]) + Nz([25]) + Nz
([26]) + Nz([27]) + Nz([28]) + Nz([29]) + Nz([30]) + Nz
([31]) + Nz([32]) + Nz([33]) + Nz([34]) + Nz([35]) + Nz
([36]) + Nz([37]) + Nz([38]) + Nz([39]) + Nz([40]) + Nz
([41]) + Nz([42]) + Nz([43]) + Nz([44]) + Nz([45]) + Nz
([46]) + Nz([47]) + Nz([48]) + Nz([49]) + Nz([50]) + Nz
([51]) + Nz([52]) + Nz([53]) + Nz([54]) + Nz([55]) + Nz
([56]) + Nz([57]) + Nz([58]) + Nz([59]) + Nz([60]) + Nz
([61]) + Nz([62]) + Nz([63]) + Nz([64]) + Nz([65]) + Nz
([66]) + Nz([67]) + Nz([68]) + Nz([69]) + Nz([70]) + Nz
([71]) + Nz([72]) + Nz([73]) + Nz([74]) + Nz([75]) + Nz
([76]) + Nz([77]) + Nz([78]) + Nz([79]) + Nz([80]) + Nz
([81]) + Nz([82]) + Nz([83]) + Nz([84]) + Nz([85]) + Nz
([86])+Nz([87]) + Nz([88]) + Nz([89]) + Nz([90]) + Nz
([91]) + Nz([92]) + Nz([93]) + Nz([94]) + Nz([95]) + Nz
([OVRALL96]))
 
I am trying to calculate a total using Nz as follows,but
there are too many fields and it is too long. How can I
do this?
Click to expand...

I'd VERY strongly suggest that you consider either normalizing your
table structures - fieldnames [1] and [83] are indicative of
completely non-normalized table design - or doing this spreadsheet
operation in a spreadsheet program (Excel) rather than in a relational
database (Access).

To do it in Access, I think you'll need to use three queries - one to
add half the fields, another to add the other half, and a third
joining these two to add the two sums. NOT pretty!
 
I would suggest using a For...Next loop in a routine to add all the fields.
Since your fields are numbered it should be very easy to write something
like:

Dim I as Integer
Dim MyTotal as Long (or whatever)
' You need a string to hold the field name which
' coincidently is the same as I
Dim MyFieldName as String

MyTotal = 0
For I = 1 to 95
MyFieldName = Str(I)
MyTotal = MyTotal + Me.MyFieldName
Next I

Me.[OVRALL96] = MyTotal

I am assuming that you want it so when any of the fields on the form
changes, the total will change so you either need to make this a function
and call it from every box find a way for the form to detect if a field that
you want to total is changing and then call the function. Or you could also
have a button to call the function and also have it called on record changes
and record loads. None of the latter are real time changes.


John Vinson said:
I am trying to calculate a total using Nz as follows,but
there are too many fields and it is too long. How can I
do this?
Click to expand...

I'd VERY strongly suggest that you consider either normalizing your
table structures - fieldnames [1] and [83] are indicative of
completely non-normalized table design - or doing this spreadsheet
operation in a spreadsheet program (Excel) rather than in a relational
database (Access).

To do it in Access, I think you'll need to use three queries - one to
add half the fields, another to add the other half, and a third
joining these two to add the two sums. NOT pretty!
Click to expand...
 
Back
Top