If IsNull(multiple fields)?

Adrian Walsh

unread,

Apr 29, 1997, 3:00:00 AM4/29/97

to

Hi,

I am trying to get a button to evaluate three fields. If all three
fields are empty it will close the form, if all three fields have data
in them, then it will continue on to another form, but if some of the
fields are empty and some have data in them, I want it to put up a
message.

What is the best way of doing this? I thought I could use an if
statement along the lines of:-

If IsNull(Me![field1]) And IsNull (Me![Field2]) And IsNull(Me![Field3])
Then
DoCmd.Close
Else
If IsNull(Me![field1]) Or IsNull (Me![Field2]) Or IsNull(Me![Field3])
Then
MsgBox ("You Must fill in all details")
Else
DoCmd.OpenForm stDocName, , ,etc
Endif
Endif

For some reason, even if all three fields are empty it comes up with the
message, I take it that I can't do an "And" with IsNull, so I tried
IsEmpty, No Go, I also tried a nested if, but that didn't work properly
because it could only evaluate each field at a time and if all three
where blank then it didn't close.

Anyone got any ideas on an easier way of doing this.

Adrian

Adrian Walsh

unread,

Apr 29, 1997, 3:00:00 AM4/29/97

to

Got the answer after a bit of trial and error, adding NOT before the
IsNull sorted it out. It works the way I wanted it to but I was still
confused with how the IsNull actually returns the result in an IF (it
seems using the IsNull above takes the opposite result than the one I
expected)

shkohl

unread,

Apr 30, 1997, 3:00:00 AM4/30/97

to

You can use And with IsNull but you will need to use three separate
IsNull statements - one for each control. The following should do the
trick:

If IsNull(Me![Field1]) And IsNull(Me![Field2]) And IsNull(Me![Field3])
Then

IsNull will return a -1 for True if the field contains a null value and
a 0 if the field is not null.

If you want to determine if the field does contain a value, you can do
something like the following:

If Not IsNull(Me![Field1]) And Not IsNull(Me![Field2]) And Not
IsNull(Me![Field3]) Then

Shaun Kohl

Chris Bell [MVP]

unread,

May 4, 1997, 3:00:00 AM5/4/97

to

:....."Stan Hargrave" <stan...@fta.com> wrote:
: I am having a similar problem, and the coding for Access 97
: shouldn't be any different. I believe that using a compound IF in
: which one of the parts tests NULL (i.e. x <> null, Not IsNull(x))
: doesn't work properly. IT IS A BUG. Haven't found it documented by
: Microsoft, yet.
:................................

Hi Stan,

You cant test a field for Null using a comparison operator because it
will always return Null rather than True or False as you might expect.

For example type this in the debug window:

? NULL = NULL

This will return NULL rather than True.
For this very reason you need to use the IsNull function instead.

I've never encountered any problems using the Not IsNull(x) format.
If you have an example where this doesnt work could you post in on the
newsgroup so I can have a look at it and report the problem if there
is a bug.

-- Chris Bell --

Dmitry Kolchev

unread,

May 11, 1997, 3:00:00 AM5/11/97

to

Dim ev as integer
ev=IsNull([field1])+IsNull([field2])+isNull([field3])
select case ev
case 0
DoCmd.Close
case -3
DoCmd.OpenForm stDocName, , ,etc
case else

MsgBox ("You Must fill in all details")

end select

Adrian Walsh <fut...@itl.net> wrote in article <3365D2...@itl.net>...