Combining two IIf expressions into one

  • Thread starter Thread starter Peter Lawton
  • Start date Start date
P

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
 
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])
 
Why not?

IIf([SubPublisher]="Abracadabra" OR [Subpublisher] is Null,[SubPublisher],[Publisher])
 
Whoops! My original response wouldn't work correctly.

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

John said:
Why not?

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

Peter said:
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
Click to expand...
Click to expand...
 
Back
Top