Combining two IIf expressions into one

[Peter Lawton]

I am trying to combine two IIf expressions that work fine by themselves into
a single expression.



I have fields [Publisher] and [SubPublisher] and two Expressions



a) FinalPub: IIf([SubPublisher]="Abracadabra","Abracadabra")

b) FinalPub: IIf(IsNull([SubPublisher]),[Publisher])



I want a field (I have called it FinalPub) to either show Abracadabra if the
SubPublisher is Abracadabra or the contents of the Publisher field if not.
If I run Expression a) my query fills the FinalPub field correctly with
Abracadabra if the Sub Publisher is Abracadabra but if there is no sub
publisher it returns a blank record.



Expression b) returns the Publisher if the SubPublisher filed is blank



I don't know if I am getting the syntax wrong or asking an IIf expression to
do something it was not designed to do.



Help gratefully received. Also if anyone knows of a web or print guide to
the basics of building expressions in Access I'd be grateful to know about
it. (I find the help in Access 2002 up labyrinthine and unintuitive)



peter

 

[Douglas J. Steele]

IIf statements have 3 parts: the boolean comparison, what to do it it's
true, and what to do if it's false.

If you want to return the Publisher information if the SubPublisher isn't
Abracadabra, use:

FinalPub: IIf([SubPublisher]="Abracadabra","Abracadabra", [Publisher])

If what you want is either Abracadabra or what's in Publisher, and want to
handle Nulls and blanks in SubPublisher, try:

FinalPub: IIf([SubPublisher] & "" ="Abracadabra","Abracadabra", [Publisher])

 

[John Spencer]

Why not?

IIf([SubPublisher]="Abracadabra" OR [Subpublisher] is Null,[SubPublisher],[Publisher])

 

[John Spencer]

Whoops! My original response wouldn't work correctly.

IIf([SubPublisher] & "" ="Abracadabra",[SubPublisher],[Publisher])

Why not?

IIf([SubPublisher]="Abracadabra" OR [Subpublisher] is Null,[SubPublisher],[Publisher])

I am trying to combine two IIf expressions that work fine by themselves into
a single expression.

I have fields [Publisher] and [SubPublisher] and two Expressions

a) FinalPub: IIf([SubPublisher]="Abracadabra","Abracadabra")

b) FinalPub: IIf(IsNull([SubPublisher]),[Publisher])

I want a field (I have called it FinalPub) to either show Abracadabra if the
SubPublisher is Abracadabra or the contents of the Publisher field if not.
If I run Expression a) my query fills the FinalPub field correctly with
Abracadabra if the Sub Publisher is Abracadabra but if there is no sub
publisher it returns a blank record.

Expression b) returns the Publisher if the SubPublisher filed is blank

I don't know if I am getting the syntax wrong or asking an IIf expression to
do something it was not designed to do.

Help gratefully received. Also if anyone knows of a web or print guide to
the basics of building expressions in Access I'd be grateful to know about
it. (I find the help in Access 2002 up labyrinthine and unintuitive)

peter

 

[Peter Lawton]

Thanks. First reply worked a treat.

peter