MS Access 2010:Summing the values from multiple controls and - Microsoft Community

dear all

apologies in advance. may seem simple.  self taught , can not figure out have gone wrong. looking assistance - (and else post seperately).

so - have 7 controls on form:

1. sr06_datapoints
2. sr10_datapoints
3. sr15_datapoints
4. gap_datapoints
5. legacy_datapoints
6. nonreg_datapoints
7. total_included_datapoints

number 6 should total of number 4 , 5 , number 7 should total of items 1 5.

i want anytime enter value in number 1, 2, 3, 4 or 5, entries in number 5 (if appropriate) update , entry in number 7 update, move next control.

i have tried 7 create function follows:

option explicit

public function calculateincludeddatapoints(sr06 variant, sr10 variant, sr15 variant, gap variant, legacy variant) variant

dim includeddatapoints variant

includeddatapoints = sr06 + sr10 + sr15 + gap + legacy

calculateincludeddatapoints = includeddatapoints

end function

then in before update event in control have put:

private sub total_included_datapoints_beforeupdate(cancel integer)
me.[total_included datapoints] = calculateincludeddatapoints(me.[sr06_datapoints], me.[sr10_datapoints], me.[sr15_datapoints], me.[gap_datapoints], me.[legacy_datapoints])
end sub

this not seem work when add data in each field. tried adding same codse in above sub before update events in controls items 1 5, didn't work.

i sure there may bea more elegant bit of code "calculateincludeddatapoints", either or calling wrong. assistancew ill appreciated.

jonathan

any time have null in expression + b + c + d, result null.

the first question attempting save calculated value field? if so, have reason this? bad idea when value can calculated.

you code need like:

public function calculateincludeddatapoints(sr06 variant, sr10 variant, _ 
        sr15 variant, gap variant, legacy variant) variant
    dim includeddatapoints variant
    includeddatapoints = nz(sr06,0) + nz(sr10,0) + nz(sr15,0) + nz(gap,0) + nz(legacy,0)
    calculateincludeddatapoints = includeddatapoints
end function

i'm not totally satisfied table structure sums values across fields rather across records. typically suggests issue normalization.

Office / Access / Other/unknown / Office 2010