How to sum a form's controls and display the result in a control?

[sueb]

I have a form the displays some 3,1 decimal fields from my table, and I want to sum these fields and display them in an unbound control. I've tried putting the addition statement in the .Control Source property:

=[Discharge Rounds]+[ICU Rounds]+[NICU Rounds]+[Peds Rounds]+[Palliative Care]+[Weekly 10-Day]+[Peri-Natal Substance Abuse]

but nothing shows up in the control.

I tried making each field re-calculate AfterUpdate:

Private Sub Calculate_Total_Hours()
    Me.[Total Hours] = Me.[Discharge Rounds] _
                    + Me.[ICU Rounds] _
                    + Me.[NICU Rounds] _
                    + Me.[Peds Rounds] _
                    + Me.[Palliative Care] _
                    + Me.[Weekly 10-Day] _
                    + Me.[Peri-Natal Substance Abuse]
End Sub
 
Private Sub Discharge_Rounds_AfterUpdate()
    Calculate_Total_Hours
End Sub
 

but, again, nothing.

What am I doing wrong? I thought this, at least, would be straightforward in Access...

[TheSmileyCoder]

You could always take the approach of summing the fields within your query, and then binding that to a textbox.

If you want to go with the function you have defined, I think you should use the CONTROL NAMES instead of the FIELD NAMES, and make sure your control names are NOT equal to your field names, or access can get confused. I know this has caused me grief before, but I've never really investigated it fully.

Also make sure you have OPTION EXPLICIT set at the top of your module.

[sueb]

Okay, I tried putting an expression in the query (and removing the AfterUpdate code), but still nothing shows up in my control. In fact, nothing shows up in the datatable view, either. Here's the expression in the query:

TotalHours: [Discharge Rounds]+[ICU Rounds]+[NICU Rounds]+[Peds Rounds]+[Palliative Care]+[Weekly 10-Day]+[Peri-Natal Substance Abuse]

[NeoPa]

Is it possible any of the fields (in the query) that are used in the calculation may have no values in them?

In that case the field results would be Null, thus converting any calculation based on them to Null.

Try using the following form for your query :

TotalHours: Nz([Discharge Rounds],0)+Nz([ICU Rounds],0)+...

[sueb]

Do you mean that, if one of two fields is null, Access can't perform addition on them?

[sueb]

Well, @NeoPa, that did it! I'm constantly amazed by Access' ability to un-intuit (new word there, in honor of this most wondrous of applications) even the simplest functions. I mean, who doesn't think that "two plus nothing still equals two"?

Thank you, NeoPa, for remembering how it is for we noobs, and being able to convey that which you learned so long ago that you've practically forgotten it! :-)

[NeoPa]

Always a pleasure Sue :-)

I suggest you stop thinking of Null as anything close to zero though. It is no more zero than it is 13,425 or any other numeric value. It is less close to zero than infinity is. It is an absence of a value. It is a place-holder for what maybe sometimes is a value but this time isn't. It doesn't even have a type of numeric. It is merely there to indicate nothing is there.

I hope that explanation makes working with Nulls in future a more intuitive experience for you. I'm afraid Access (or Microsoft if you prefer) didn't get anything wrong here. The misunderstanding does not come from that quarter. If you look in Help for Null you will find :

Null

A value indicating that a variable contains no valid data. Null is the result of an explicit assignment of Null to a variable or any operation between expressions that contain Null.

[sueb]

Yes, of course, you're right about Null, NeoPa. Thanks for the explanation!