Solved

IsNull using multiple variables

Posted on 2004-08-03
13
577 Views
Last Modified: 2008-03-10
the help for IsNULL states "If expression consists of more than one variable, Null in any constituent variable causes True to be returned for the entire expression." what is the syntax for specifying multiple variables?

I'm working in Access and want to check several (mandatory) fields and was wondering if the was a short version of

If IsNull(field1) = true OR IsNull(field2) = true OR  IsNull(field3) = true THEN

Thanks

Mandy
0
Comment
Question by:MandyC
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 11704342
Hi MandyC,
In Excel VBA, I might try something like:
If IsNull(field1)+IsNull(field2)+IsNull(field3)>0 Then

Cheers!

Brad
0
 
LVL 3

Author Comment

by:MandyC
ID: 11704489
Brad,

for what I'm after <0 works (trying to find out if ANY field is null) but was hopping to get rid of those extra IsNull statements as in some cases I'm be checking a lot more than 3 variables.
0
 
LVL 81

Expert Comment

by:byundt
ID: 11704519
MandyC,
I could move this question into the Access TA if you would like.
Brad
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 3

Author Comment

by:MandyC
ID: 11704599
The reason I put it here is that IsNull solutions from Excel (and possibly Word/Project/ VB etc) may work. I'm never too sure what to to with vba questions but am happy to be guided by your experience. Perhaps a vba section under MS Office would be appropriate. (Or the ability to link a question to more that one topic)

I'll leave it up to you to do what you think is best.

Cheers
Mandy

:-)
0
 
LVL 81

Expert Comment

by:byundt
ID: 11704775
Mandy,
The preferred way of asking a question in several forums is to use Pointer questions that direct participating experts to your original question. That way, all the comments are captured in one place. The Pointer questions are deleted (with points refund) when the main question has been answered.

I posted one for you at http://www.experts-exchange.com/Databases/MS_Access/Q_21080532.html
byundt--Office TA Page Editor
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 125 total points
ID: 11704828
u could try this method

use the TAG property, give it a value to indicate that this is a mandatory field, say u put in 1 to indicate this

then in your code

    Dim ctl As Control
    Dim bNullsFound as Boolean

    bNullsFound=False
    For Each ctl In Me.Controls
        If ctl.Tag = 1 Then
            if NZ(ctl.Value,"") = "" Then
                 bNullsFound = True
            endif
         End if
    Next
 
    if bNullsFound = True then msgbox "one or more fields are empty"


0
 
LVL 39

Expert Comment

by:stevbe
ID: 11704963
You can use the + version of concatenation. If the expression on either side is null then the return for + is null

If IsNull((Field1 + Field2 + Field3)) Then
    Msgbox "bad record :)"
End If

Steve
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11705008
Are you doing this for an Access form?
Are the fields marked as "Required" in the table?


Steve
0
 
LVL 3

Author Comment

by:MandyC
ID: 11705082
Stevbe

I tried something similar (used "&" rather than "+" but I don't think that that should matter) but that only worked if ALL the fields were null rather than if ANYof them were.

Also the access form is a front end to SQL so the required element is set there.



Rockiroads

I hadn't come across Tag before but i like it. That was the sort of thing I'm after.

Thanks
0
 
LVL 3

Author Comment

by:MandyC
ID: 11705102
Also thanks to Brad for adding the link (especially if rockiroads followed the link)
M
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11705121
Urm, I did


hey byundt, thanks
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11705219
"used "&" rather than "+" but I don't think that that should matter"

it does matter,  & and + are different functions ...

test in the debug window ...

?"My" & Null & " Test"
My Test

?"My" + Null + " Test"
Null

Steve
0
 
LVL 3

Author Comment

by:MandyC
ID: 11705325
Stevbe

Thanks - I've posted a question in the MSOffice area to give you the points as well

<<At http://www.experts-exchange.com/Applications/MS_Office/Q_21080620.html
byundt--Office TA Page Editor>>
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
EXCEL 2010 7 70
Create Excel formula on dynamic data 5 44
Automatic sort formula 8 53
Need excel formula correction. 5 14
Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question