invalid use of null when reach to textbox with no value

[onusonu]

How do I get it to stop when the record is null instead of getting the error "invalid use of null"
Function TotalW() As Double

Dim TotalWeight As Double
Dim SWeight As Double
Dim count As Integer
Dim TWeight As Variant
Dim i As Integer


For i = 1 To 13
If Not IsEmpty(TWeight) Or IsNumeric(TWeight) Or TWeight <> Null Then
TWeight = Me("Wt" & CStr(i))
TotalWeight = TotalWeight + TWeight
MsgBox i
End If



TotalW = TotalWeight

Next i

End Function

 

[SkipVought]

Hi,

Check out HELP in VB in Null.

It has a VERY SPECIAL meaning

Rather use TWeight <> ""

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[rmikesmith]

Can you explain this construct: Me("Wt" &CStr(i))?

For the sake of argument, let's replace that with an array which I'll name arrValues [ Dim'd arrValues(1 to 13) ]. You could re-write your function as:

Function TotalW() As Double

Dim TotalWeight As Double
Dim SWeight As Double
Dim count As Integer
Dim i As Integer


    For i = 1 To 13
      TotalWeight = TotalWeight + arrValues(i)
    Next i
    TotalW = TotalWeight

End Function

Note, I'm only using an array for example. Also, did you intend to put TotalW = TotalWeight inside the loop? Why?

Regards,
Mike

 

[PHV]

For i = 1 To 13
TWeight = Me("Wt" & i)
If IsNumeric(TWeight) Then
TotalWeight = TotalWeight + TWeight
End If
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[onusonu]

Thank you, but now i have another problem.
when I have 3 textbox with numbers,
it loop through the 3 textbox and repeat 2 more times. And when I have 7 textboxes with numbers, it loops through the 7 textbox and another 6 set, each 7 times.
I can't see why it's doing that.

Function TotalW() As Double

Dim TotalWeight As Double
Dim SWeight As Double
Dim count As Integer
Dim TWeight As Variant
Dim i As Integer


For i = 1 To 13
TWeight = Me("Wt" & CStr(i))
If TWeight<>"" Or IsNumeric(TWeight) Or Then

TotalWeight = TotalWeight + TWeight
MsgBox i
End If
next i


TotalW = TotalWeight


End Function

 

[onusonu]

Hi PHV,
with the code you showed me, it gave me "invalid use of null" message

 

[onusonu]

Me("Wt" &CStr(i)) refers to each textbox. Wt1, Wt2, Wt3...Wt13

 

[SkipVought]

How is TotalW being called?

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[PHV]

it gave me "invalid use of null" message
At which line of code ?
For i = 1 To 13
TWeight = Me("Wt" & i) & ""
If IsNumeric(TWeight) Then
TotalWeight = TotalWeight + TWeight
End If
Next i

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kspurr]

onusonu,

I just happened to come accross this thread and noticed that no one has suggested the use of the: IsNull() function. You could use:

    For i = 1 To 13
        TWeight = Me("Wt" & i) & ""
        If Not IsNull(TWeight) Then
            TotalWeight = TotalWeight + TWeight
        End If
    Next i

Also, nesting your if clauses like you did in:

    For i = 1 To 13
    If Not IsEmpty(TWeight) Or IsNumeric(TWeight) Or TWeight <> Null Then
        TWeight = Me("Wt" & CStr(i))
        TotalWeight = TotalWeight + TWeight
        MsgBox i
    End If

will cause all of your if's to be evaluated, when you probably only need the one.