Invalis use of Null

  • Thread starter Thread starter Jacco
  • Start date Start date
J

Jacco

Hi everyone,

I'm trying to validate a form, using VB. I have 4 time fields, and atleast
ONE of them needs to contain value. I've tried several approaches, but just
can't get it to work. Untill somebody enters a value in the fields, the are
set to Null.

I ended up trying this, but it won't process Null values and crash:

Dim CommandTime As String

CommandTime = [PIC].Value + [SIC].Value + [Dual].Value + [IP].Value
If CommandTime = 0 Then GoTo Forgot_new1_Click


Thank you in advance
Jacco
 
If you consider a form validated merely by having at least one field with
some value other than a null, and assuming you have the four fields mentioned
below, the following may work for you:

If IsNull([PIC]) + IsNull([SIC]) + IsNull([Dual]) + IsNull([IP]) > 3
Then GoTo Forgot_new1_Click
 
Todd B. said:
If you consider a form validated merely by having at least one field
with some value other than a null, and assuming you have the four
fields mentioned below, the following may work for you:

If IsNull([PIC]) + IsNull([SIC]) + IsNull([Dual]) + IsNull([IP])
3 Then GoTo Forgot_new1_Click
Click to expand...
Click to expand...

That won't work. The value of True in VB is -1, so your sum will give
values in the range from -4 (all Null) to 0 (none Null). You could do
this:

If IsNull([PIC]) + IsNull([SIC]) + IsNull([Dual]) + IsNull([IP]) < 0
Then

.... or this:

If IsNull([PIC]) _
Or IsNull([SIC]) _
Or IsNull([Dual]) _
Or IsNull([IP]) _
Then

.... or this:

Dim fForgotOne As Boolean

fForgotOne = IsNull([PIC])
If Not fForgotOne Then
If IsNull([SIC]) Then
fForgotOne = True
Else
If IsNull([Dual]) Then
fForgotOne = True
Else
If IsNull([IP]) Then
fForgotOne = True
End If
End If
End If
End If

If fForgotOne Then

' do something here ...

End If

.... or a variety of other things.
 
Dirk Goldgar said:
Todd B. said:
If you consider a form validated merely by having at least one field
with some value other than a null, and assuming you have the four
fields mentioned below, the following may work for you:

If IsNull([PIC]) + IsNull([SIC]) + IsNull([Dual]) + IsNull([IP])
3 Then GoTo Forgot_new1_Click
Click to expand...
Click to expand...

That won't work. The value of True in VB is -1, so your sum will give
values in the range from -4 (all Null) to 0 (none Null). You could do
this:

If IsNull([PIC]) + IsNull([SIC]) + IsNull([Dual]) + IsNull([IP]) < 0
Then

... or this:

If IsNull([PIC]) _
Or IsNull([SIC]) _
Or IsNull([Dual]) _
Or IsNull([IP]) _
Then

... or this:

Dim fForgotOne As Boolean

fForgotOne = IsNull([PIC])
If Not fForgotOne Then
If IsNull([SIC]) Then
fForgotOne = True
Else
If IsNull([Dual]) Then
fForgotOne = True
Else
If IsNull([IP]) Then
fForgotOne = True
End If
End If
End If
End If

If fForgotOne Then

' do something here ...

End If

... or a variety of other things.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Man, that looks hard. Finally got it to work, but lots easier (and cleaner):

Dim CommandTime As String
CommandTime = Nz([PIC].Value, 0) + Nz([SIC].Value, 0) + Nz([Dual].Value, 0)
+ Nz([IP].Value, 0)
If CommandTime = "0" Then GoTo Command_new1_Click

Thanks for the help nontheless
Jacco
 
Jacco said:
Dirk Goldgar said:
Todd B. said:
If you consider a form validated merely by having at least one field
with some value other than a null, and assuming you have the four
fields mentioned below, the following may work for you:

If IsNull([PIC]) + IsNull([SIC]) + IsNull([Dual]) +
IsNull([IP])
3 Then GoTo Forgot_new1_Click
Click to expand...

That won't work. The value of True in VB is -1, so your sum will
give values in the range from -4 (all Null) to 0 (none Null). You
could do this:

If IsNull([PIC]) + IsNull([SIC]) + IsNull([Dual]) + IsNull([IP])
< 0 Then

... or this:

If IsNull([PIC]) _
Or IsNull([SIC]) _
Or IsNull([Dual]) _
Or IsNull([IP]) _
Then

... or this:

Dim fForgotOne As Boolean

fForgotOne = IsNull([PIC])
If Not fForgotOne Then
If IsNull([SIC]) Then
fForgotOne = True
Else
If IsNull([Dual]) Then
fForgotOne = True
Else
If IsNull([IP]) Then
fForgotOne = True
End If
End If
End If
End If

If fForgotOne Then

' do something here ...

End If

... or a variety of other things.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Man, that looks hard. Finally got it to work, but lots easier (and
cleaner):

Dim CommandTime As String
CommandTime = Nz([PIC].Value, 0) + Nz([SIC].Value, 0) +
Nz([Dual].Value, 0) + Nz([IP].Value, 0)
If CommandTime = "0" Then GoTo Command_new1_Click

Thanks for the help nontheless
Click to expand...

LOL You're welcome, nonetheless.

Personally, I don't think that's cleaner than either of the first two
versions, and it involves at least one unnecessary data-type conversion,
but suit yourself.
 
Dirk Goldgar said:
Jacco said:
Dirk Goldgar said:
If you consider a form validated merely by having at least one field
with some value other than a null, and assuming you have the four
fields mentioned below, the following may work for you:

If IsNull([PIC]) + IsNull([SIC]) + IsNull([Dual]) +
IsNull([IP])
3 Then GoTo Forgot_new1_Click

That won't work. The value of True in VB is -1, so your sum will
give values in the range from -4 (all Null) to 0 (none Null). You
could do this:

If IsNull([PIC]) + IsNull([SIC]) + IsNull([Dual]) + IsNull([IP])
< 0 Then

... or this:

If IsNull([PIC]) _
Or IsNull([SIC]) _
Or IsNull([Dual]) _
Or IsNull([IP]) _
Then

... or this:

Dim fForgotOne As Boolean

fForgotOne = IsNull([PIC])
If Not fForgotOne Then
If IsNull([SIC]) Then
fForgotOne = True
Else
If IsNull([Dual]) Then
fForgotOne = True
Else
If IsNull([IP]) Then
fForgotOne = True
End If
End If
End If
End If

If fForgotOne Then

' do something here ...

End If

... or a variety of other things.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Man, that looks hard. Finally got it to work, but lots easier (and
cleaner):

Dim CommandTime As String
CommandTime = Nz([PIC].Value, 0) + Nz([SIC].Value, 0) +
Nz([Dual].Value, 0) + Nz([IP].Value, 0)
If CommandTime = "0" Then GoTo Command_new1_Click

Thanks for the help nontheless
Click to expand...

LOL You're welcome, nonetheless.

Personally, I don't think that's cleaner than either of the first two
versions, and it involves at least one unnecessary data-type conversion,
but suit yourself.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
I meant cleaner then your last option offcourse.
 
Back
Top