Adding third clause to IIF 4

[miletracker]

In a access Query I have 3 fields "Location 1" "Location 2" and "Current Location"
Using the if function I want "CurrentLocation" to show the following :
If "location 2" is null return "location 1" if location 1 is also null return "No Location Given" Thanks for the help

 

[CFBaber]

IIF([Location 2] is null, [Location 1], (IIF([Location 1] is null, "No Location Given", [Location 2]))) as [CurrentLocation]

Should work,
Cheers

 

[miletracker]

Thanks For the Reply Could not seem to get it will try your tip
thanks

 

[dhookom]

This is a good use for Nz()
Nz([Location2],Nz([Location1],"No Location Given&quot)

Duane
MS Access MVP

 

[miletracker]

The Following Does Not Work As Expected If Both Locations Are Blank A Blank Is Returned
CurrentLocation: IIf([FinisherID] Is Null,[NurseryID],(IIf([NurseryID] Is Null,"No Location Given&quot))
Changed slightly from CFBaber's Tip
Both do not work

 

[nicsin]

Hi miletracker,

try this one:

IIF([Location 2] is null, iif([Location1] is null, "No Location Given",[Location1]), [Location2])

regards,
nicsin

 

[TonyJollans]

Hi miletracker,

Duane's solution using NZ is the one to use, but if you want to use the IIFs then what you have says (in pseudocode) ..

IF FinisherID is null
Use NurseryID
Else (i.e. FinisherID is NOT Null)
If NurseryID is null
Use "No Location Given"
Else
Use (nothing specified)
endif
endif

What I think you want is ..

IF FinisherID is null
IF NurseryID is null
Use "No location"
ELSE
Use NureseryID
endif
else
use finisherID
endif

which is ..

IIF(isnull([FinisherID]),iif(isnull([NurseryID]),"NO Location",[NurseryID]),[FinisherID])

Enjoy,
Tony

 

[miletracker]

dhookom's answer works perfectly thanks again guys

miletracker

 

[SgtJarrow]

While dhookom's answer may have worked, you should seriously consider the suggestion TonyJollans proposed. Once you break the normal IIF(whatever, true, false) setup, you should consider a custom function designed to use multiple IFs or Select Case to determine the coutcome. If you don't understand why, just say to yourself..."When the boss asks me to add two more clauses, this is going to get ugly." IIFs are good for a simple true/false evaluation....anything else should be a function.

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[dhookom]

mstrmage1768 and Tony deserve stars for this. I see way too many questions in these forums that have long, ugly expressions that are used in queries, control sources, etc.

These should be converted to a small, easily maintained function that can be used everywhere within your application. Keep all of these types of functions in a module with a name like "basBusinessRules" or something similar.

Ideally some of these could be replace with lookup tables and a little SQL so that the code doesn't have to be maintained.

Duane
MS Access MVP

 

[CajunCenturion]

I certainly appreciate that sentiment dhookom, and agree in many cases, but on the other hand, have you considered the performance implications of having a function be called on the client's machine for each and every record involved in the query?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dhookom]

I haven't tested this but I couldn't imagine a significant difference between an expression and a function. There are so many other slower processes in applications such as screen updates, printing, reading records from disk, etc.

I would prefer to have a more maintainable solution than one that runs a fraction of a second faster.

I think the real question is why there are three location fields. This seems un-normalized to me in most cases. There could be a good reason. If this structure works, then use it.

Duane
MS Access MVP

 

[CajunCenturion]

Much depends on where and whom. Where is the function, and where is expression. Which process is evaluation the function, and which process evaluates the function. Especially in client-server situations.

Until you actually test it, you'll never know. I remember thinking that

""

wouldn't be much faster than using

vbNullString

, and so I ran some tests. To my surprise,

vbNullString

is almost twice as fast.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[hwkranger]

Cajun,

In reguards to this:
I remember thinking that "" wouldn't be much faster than using vbNullString, and so I ran some tests. To my surprise, vbNullString is almost twice as fast.

is this for anywhere you would use "", eg. docmd.openform "someformname", "", "somesetting" or just in comparison? eg. if me.somefield = "" then..



Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[CajunCenturion]

There are some places where vbNullString will behave differently than "", but they are so few and far between, and I can count on 1 hand the number of times where it mattered. (and you probably don't even want to know what I was trying to do at time). It's a safe bet you can use them throughout VBA.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein