Hello,

I've created a field called "WeekHours". In a continuous form (detail section), it displays the working hours per employee. To calculate this, the function "TotalHours()" is called under the "Control element source" of the textfield properties. Note: I'm not sure if this is the correct name in English; it's the second option under textfield properties, guide card "All".

Under VBA, the function looks like this:


Private Function TotalHours() As Integer
Dim Mo As Integer
Dim Tu As Integer
Dim We As Integer
Dim Thu As Integer
Dim Fr As Integer

If IsNull(Me.Inp_Mo_Hours) Then Mo = 0 Else Mo = Me.Inp_Mo_Hours
If IsNull(Me.Inp_Tu_Hours) Then Tu = 0 Else Tu = Me.Inp_Tu_Hours
If IsNull(Me.Inp_We_Hours) Then We = 0 Else We = Me.Inp_We_Hours
If IsNull(Me.Inp_Thu_Hours) Then Thu = 0 Else Thu = Me.Inp_Thu_Hours
If IsNull(Me.Inp_Fr_Hours) Then Fr = 0 Else Fr = Me.Inp_Fr_Hours

If Not (Mo + Tu + We + Thu + Fr = 0) Then
TotalHours = Mo + Tu + We + Thu + Fr
End If

End Function


As you see, I've summed the hours of all days, come from other text fields within the continuous form. If this sum is 0, I don't fill my function Totalhours(). That's because I want my WeekHours textfield record to be left blanket. Unfortunately, this doesn't work... All empty records are being filled with '0' in the form. :(
Is it possible to leave the textfield records with zero worked hours empty? I've tried several things, but they're always '0' or an error is generated...

My second question: I've created another textfield in the form footer section, called "WeekHours_Total". As you will understand, it should display the sum of all weekhours in the detail section. To calculate this, I've placed the text "=Sum([WeekHours])" under the "Control element source" of the textfield properties.
Unfortunately this doesn't work, because Weekhours is connected to a function. If Weekhours should contain "hard" values (no functions), the calculation does work (I've tested this).
Anyway, how do I get my total weekhours without having to remove my function? :confused:

Any help on this two questions would be greatly appreciated! Thanx!

Private Function TotalHours() As Integer

Dim Mo As Integer
Dim Tu As Integer
Dim We As Integer
Dim Thu As Integer
Dim Fr As Integer

Mo = Switch(Nz(Me.Inp_Mo_Hours,"")="","",True, Me.Inp_Mo_Hours)
Tu = Switch(Nz(Me.Inp_Tu_Hours,"")="","",True, Me.Inp_Tu_Hours)
We = Switch(Nz(Me.Inp_We_Hours,"")="","",True, Me.Inp_We_Hours)
Thu = Switch(Nz(Me.Inp_Thu_Hours,"")="","",True, Me.Inp_Thu_Hours)
Fr = Switch(Nz(Me.Inp_Fr_Hours,"")="","",True, Me.Inp_Fr_Hours)

TotalHours = Nz(Mo + Tu + We + Thu + Fr,"")

End Function

You're correct about the sum issue, so for your WeekHours field, just recreate the formula:

WeekHours = Sum(Nz(Mo + Tu + We + Thu + Fr,""))

Unfortunately, this generates an error too... Thanx anyway for your help! Suggestions are still welcome.

For weekhours to work, you need to reference the Mo, Tu, etc. as they are named on the report. Do you have the exact same field names on the report?

Actually, Mo, Tu etc. are declared as variables (see the code) and do not appear in the form. The fields "Inp_Mo_Hours', "Inp_Tu_Hours", etc. refer to the exact field names in the detail section of the form.