If statement not displaying text if conditions not met

[Ruth]

I want to do a calculation which calculates selling price from either Margin
% or Margin £ and if there is nothing in either field then I want to display
the text "no margin". I have tried the following formula:-

Sell Ex VAT: IIf([Margin %] Is Not Null,[Cost Price]+([Margin %]*[Cost
Price]),(IIf([Margin £] Is Not Null,[Cost Price]+[Margin £],"no margin")))

However, if there is nothing in either field, then it returns #Error.

Anyone got any ideas?

 

[mscertified]

What happens if you change "no margin" to 0 (zero). Does it then work?

-Dorian

 

[Duane Hookom]

I try to create expressions that return only a single data type. Your
expression might return a number or possibly a string. I would replace the
"No Margin" with Null. You can then use the format property of your text box
to display "No Margin" for all Null values.

 

[Ron2006]

I want to do a calculation which calculates selling price from either Margin
% or Margin £ and if there is nothing in either field then I want to display
the text "no margin". I have tried the following formula:-

Sell Ex VAT: IIf([Margin %] Is Not Null,[Cost Price]+([Margin %]*[Cost
Price]),(IIf([Margin £] Is Not Null,[Cost Price]+[Margin £],"no margin")))

However, if there is nothing in either field, then it returns #Error.

Anyone got any ideas?

Change the format of the is null condition:

Sell Ex VAT: IIf(not isnull([Margin %]),[Cost Price]+([Margin %]*[Cost

Price]),(IIf(not isnull([Margin £]),[Cost Price]+[Margin £],"no margin")))

or alternately:

Sell Ex VAT: IIf(isnull([Margin %])=false,[Cost Price]+([Margin
%]*[Cost

Price]),(IIf(isnull([Margin £])=false,[Cost Price]+[Margin £],"no margin")))

Ron

 

[Ruth]

yes this does work - I guess its because its returning a number as opposed to
some text.......

What happens if you change "no margin" to 0 (zero). Does it then work?

-Dorian

I want to do a calculation which calculates selling price from either Margin
% or Margin £ and if there is nothing in either field then I want to display
the text "no margin". I have tried the following formula:-

Sell Ex VAT: IIf([Margin %] Is Not Null,[Cost Price]+([Margin %]*[Cost
Price]),(IIf([Margin £] Is Not Null,[Cost Price]+[Margin £],"no margin")))

However, if there is nothing in either field, then it returns #Error.

Anyone got any ideas?

 

[Ruth]

Replaced "No Margin" with Null and yes this does work - it does not display
anything in the column. How do I change the format to display "No Margin"
for Null values? I'm using the formula in a query which feeds into a form.

I try to create expressions that return only a single data type. Your
expression might return a number or possibly a string. I would replace the
"No Margin" with Null. You can then use the format property of your text box
to display "No Margin" for all Null values.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I want to do a calculation which calculates selling price from either Margin
% or Margin £ and if there is nothing in either field then I want to display
the text "no margin". I have tried the following formula:-

Sell Ex VAT: IIf([Margin %] Is Not Null,[Cost Price]+([Margin %]*[Cost
Price]),(IIf([Margin £] Is Not Null,[Cost Price]+[Margin £],"no margin")))

However, if there is nothing in either field, then it returns #Error.

Anyone got any ideas?

 

[Ruth]

Thanks for your help, but I can't get this to work it still returns #Error.

I want to do a calculation which calculates selling price from either Margin
% or Margin £ and if there is nothing in either field then I want to display
the text "no margin". I have tried the following formula:-

Sell Ex VAT: IIf([Margin %] Is Not Null,[Cost Price]+([Margin %]*[Cost
Price]),(IIf([Margin £] Is Not Null,[Cost Price]+[Margin £],"no margin")))

However, if there is nothing in either field, then it returns #Error.

Anyone got any ideas?

Change the format of the is null condition:

Sell Ex VAT: IIf(not isnull([Margin %]),[Cost Price]+([Margin %]*[Cost

Price]),(IIf(not isnull([Margin £]),[Cost Price]+[Margin £],"no margin")))

or alternately:

Sell Ex VAT: IIf(isnull([Margin %])=false,[Cost Price]+([Margin
%]*[Cost

Price]),(IIf(isnull([Margin £])=false,[Cost Price]+[Margin £],"no margin")))

Ron

 

[Ofer Cohen]

You can use the Nz function to replace the Null with the String

Nz(YourExpresionHere,"No Margin")

--
Good Luck
BS"D

Replaced "No Margin" with Null and yes this does work - it does not display
anything in the column. How do I change the format to display "No Margin"
for Null values? I'm using the formula in a query which feeds into a form.

I try to create expressions that return only a single data type. Your
expression might return a number or possibly a string. I would replace the
"No Margin" with Null. You can then use the format property of your text box
to display "No Margin" for all Null values.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I want to do a calculation which calculates selling price from either Margin
% or Margin £ and if there is nothing in either field then I want to display
the text "no margin". I have tried the following formula:-

Sell Ex VAT: IIf([Margin %] Is Not Null,[Cost Price]+([Margin %]*[Cost
Price]),(IIf([Margin £] Is Not Null,[Cost Price]+[Margin £],"no margin")))

However, if there is nothing in either field, then it returns #Error.

Anyone got any ideas?