Evaluate multiple controls for Null values

[Steve]

Greetings:

I would like to calculate the sum of 4 text fields (txtFld1, txtFld2,
txtFld3, txtFld4) when the user clicks cmdCalc. Prior to performing the
calculation, I would like to make sure that no null values exist in any of
these text fields. Is there a more efficient way of determining this without
individually assessing each field as I've done below? Thanks for any
assistance. Steve

Private Sub cmdCalc_Click()
On Error GoTo Err_cmdCalc_Click

'define variables
Dim intScore1 As Integer
Dim intScore2 As Integer
Dim intScore3 As Integer
Dim intScore4 As Integer
Dim intTotal As Integer

'make sure each of the 4 fields have a value
If Not IsNull(Me.txtFld1) Then
intScore1 = Me.txtFld1
Else
Exit Sub
End if

If Not IsNull(Me.txtFld2) Then
intScore2 = Me.txtFld2
Else
Exit Sub
End if

If Not IsNull(Me.txtFld3) Then
intScore3 = Me.txtFld3
Else
Exit Sub
End if

If Not IsNull(Me.txtFld4) Then
intScore4 = Me.txtFld4
Else
Exit Sub
End if

'add scores
intTotal = intScore1 + intScore2 + intScore3 + intScore4

'put the total into text field
Me.txtTotalScore = intTotal


Exit_cmdCalc_Click:
Exit Sub

Err_cmdCalc_Click:
MsgBox Err.Description
Resume Exit_cmdCalc_Click

End Sub

 

[bhicks11 via AccessMonster.com]

Are you doing that because it fails if there is a null? You can use NZ().

Bonnie
http://www.dataplus-svc.com

 

[John W. Vinson]

I would like to calculate the sum of 4 text fields (txtFld1, txtFld2,
txtFld3, txtFld4) when the user clicks cmdCalc. Prior to performing the
calculation, I would like to make sure that no null values exist in any of
these text fields. Is there a more efficient way of determining this without
individually assessing each field as I've done below? Thanks for any
assistance. Steve

You don't need *any code at all*!

Just set the control source of another textbox to

=NZ([txtFld1]) + NZ([txtFld2]) + NZ([txtFld3]) + NZ([txtFld4])

The NZ - Null To Zero - function returns zero if the argument is null.

 

[Steve]

Thanks for your reply. I did not make myself completely clear. I do not want
the sum to occur unless all 4 fields have been completed. In other words, I
do not want to treat nulls as zeros. In fact, what I would like to do is
prompt the user to complete the missing field before attempting the
calculation. Still it seems inefficient and repetitive to assess each field.
I was hoping that one could evaluate more than one field using isNull but
that does not appear to be the case. Sorry for the confusion.
--
Steve

I would like to calculate the sum of 4 text fields (txtFld1, txtFld2,
txtFld3, txtFld4) when the user clicks cmdCalc. Prior to performing the
calculation, I would like to make sure that no null values exist in any of
these text fields. Is there a more efficient way of determining this without
individually assessing each field as I've done below? Thanks for any
assistance. Steve

You don't need *any code at all*!

Just set the control source of another textbox to

=NZ([txtFld1]) + NZ([txtFld2]) + NZ([txtFld3]) + NZ([txtFld4])

The NZ - Null To Zero - function returns zero if the argument is null.

 

[Dirk Goldgar]

Greetings:

I would like to calculate the sum of 4 text fields (txtFld1, txtFld2,
txtFld3, txtFld4) when the user clicks cmdCalc. Prior to performing the
calculation, I would like to make sure that no null values exist in any of
these text fields. Is there a more efficient way of determining this
without
individually assessing each field as I've done below? Thanks for any
assistance. Steve

Private Sub cmdCalc_Click()
On Error GoTo Err_cmdCalc_Click

'define variables
Dim intScore1 As Integer
Dim intScore2 As Integer
Dim intScore3 As Integer
Dim intScore4 As Integer
Dim intTotal As Integer

'make sure each of the 4 fields have a value
If Not IsNull(Me.txtFld1) Then
intScore1 = Me.txtFld1
Else
Exit Sub
End if

If Not IsNull(Me.txtFld2) Then
intScore2 = Me.txtFld2
Else
Exit Sub
End if

If Not IsNull(Me.txtFld3) Then
intScore3 = Me.txtFld3
Else
Exit Sub
End if

If Not IsNull(Me.txtFld4) Then
intScore4 = Me.txtFld4
Else
Exit Sub
End if

'add scores
intTotal = intScore1 + intScore2 + intScore3 + intScore4

'put the total into text field
Me.txtTotalScore = intTotal


Exit_cmdCalc_Click:
Exit Sub

Err_cmdCalc_Click:
MsgBox Err.Description
Resume Exit_cmdCalc_Click

End Sub

You can write

If IsNull(Me.txtFld1) _
Or IsNull(Me.txtFld2) _
Or IsNull(Me.txtFld3) _
Or IsNull(Me.txtFld4) _
Then
Exit Sub
End If

Me.txtTotalScore = _
Me.txtFld1 + Me.txtFld2 + Me.txtFld3 + Me.txtFld4


Or, if the text boxes really are named like that, it's possible to loop
through them this way:

Dim I As Integer

For I = 1 to 4
With Me.Controls("txtFld" & I)
If IsNull(.Value) Then
Exit Sub
Else
intTotal = intTotal + .Value
End If
End With
Next I

Me.txtTotalScore = intTotal

 

[John W. Vinson]

Thanks for your reply. I did not make myself completely clear. I do not want
the sum to occur unless all 4 fields have been completed. In other words, I
do not want to treat nulls as zeros. In fact, what I would like to do is
prompt the user to complete the missing field before attempting the
calculation. Still it seems inefficient and repetitive to assess each field.
I was hoping that one could evaluate more than one field using isNull but
that does not appear to be the case. Sorry for the confusion.

Sounds like Dirk's solution (either of them!) will be right on the money for
you. Just throw in a msgbox to warn the user.