Combining two IIf expressions into one

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])

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Peter Lawton" wrote in message
...
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

Why not?

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


Peter Lawton wrote:

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

Whoops! My original response wouldn't work correctly.

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

John Spencer wrote:

Why not?

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

Peter Lawton wrote:

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

Thanks. First reply worked a treat.

peter

"Peter Lawton" wrote in message
...
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