IIF and formatting

[Huber57]

I hope someone can help!

I am running MS Access 2003. I have two Expressions built in a query that
look like this:

Expr5: IIf([ConstructionCost]>0,[ConstructionCost],"TBD")

Expr2: Format([ConstructionCost],"$#,##0;-$#,##0")

I want to combine the two so that the output is either: $500,000 (not
500000) or TBD.

I can't figure out how to wrap the Format around the IIF.

 

[Allen Browne]

First thing to consider is what kind of data you want in this field.

If you just want text (no need to perform any mathematical operations such
as summing), use:
IIf([ConstructionCost] > 0, Format([ConstructionCost],"$#,##0;-$#,##0"),
"TBD")

But if you want the result to be a true number so you can sum or sort it
correctly, you cannot use text such as "TBD", and you cannot use the
Format() function, since both of these will convert it into text.

The real problem here might be the use of 0 where the value has not yet been
determined. You could solve that problem by using Null instead of zero. Null
means the value is as yet unknown (which is what I assume TBD is about),
whereas zero should be reserved for those cases where the cost is known to
be zero.

Then in any form or report, you can use the Fomat property to show the field
in any way you want, including using the literal letters TBD as the format
for Null.

 

[Huber57]

Allen,

Thanks much.

I have it formatted a number right now. In another expression, i have that
ConstructionCost divided by SqFt to get the cost per sq ft.

If I format it as text in this one expression, will it affect the others?

First thing to consider is what kind of data you want in this field.

If you just want text (no need to perform any mathematical operations such
as summing), use:
IIf([ConstructionCost] > 0, Format([ConstructionCost],"$#,##0;-$#,##0"),
"TBD")

But if you want the result to be a true number so you can sum or sort it
correctly, you cannot use text such as "TBD", and you cannot use the
Format() function, since both of these will convert it into text.

The real problem here might be the use of 0 where the value has not yet been
determined. You could solve that problem by using Null instead of zero. Null
means the value is as yet unknown (which is what I assume TBD is about),
whereas zero should be reserved for those cases where the cost is known to
be zero.

Then in any form or report, you can use the Fomat property to show the field
in any way you want, including using the literal letters TBD as the format
for Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I hope someone can help!

I am running MS Access 2003. I have two Expressions built in a query that
look like this:

Expr5: IIf([ConstructionCost]>0,[ConstructionCost],"TBD")

Expr2: Format([ConstructionCost],"$#,##0;-$#,##0")

I want to combine the two so that the output is either: $500,000 (not
500000) or TBD.

I can't figure out how to wrap the Format around the IIF.

 

[Keith Wilby]

I hope someone can help!

I am running MS Access 2003. I have two Expressions built in a query that
look like this:

Expr5: IIf([ConstructionCost]>0,[ConstructionCost],"TBD")

Expr2: Format([ConstructionCost],"$#,##0;-$#,##0")

I want to combine the two so that the output is either: $500,000 (not
500000) or TBD.

I can't figure out how to wrap the Format around the IIF.

Try

IIf([ConstructionCost]>0,Format([ConstructionCost],"$#,##0;-$#,##0"),"TBD")

Keith.
www.keithwilby.com

 

[Huber57]

Keith,

Thanks. One more for you.

This is what I have:

IIf([ConstructionCost]/[Sqft]>0,Format([ConstructionCost][Sqft],"Currency"),â€TBDâ€)

When I try to run it, it puts [ ] around the "TBD" and asks for a value.

Help!?

I hope someone can help!

I am running MS Access 2003. I have two Expressions built in a query that
look like this:

Expr5: IIf([ConstructionCost]>0,[ConstructionCost],"TBD")

Expr2: Format([ConstructionCost],"$#,##0;-$#,##0")

I want to combine the two so that the output is either: $500,000 (not
500000) or TBD.

I can't figure out how to wrap the Format around the IIF.

Try

IIf([ConstructionCost]>0,Format([ConstructionCost],"$#,##0;-$#,##0"),"TBD")

Keith.
www.keithwilby.com

 

[Marshall Barton]

This is what I have:

IIf([ConstructionCost]/[Sqft]>0,Format([ConstructionCost][Sqft],"Currency"),”TBD”)

When I try to run it, it puts [ ] around the "TBD" and asks for a value.

That's the problem that Allen was warning you about. Go
back and review his post. Also check VBA Help on the topic:

Format Property - Number and Currency Data Types

so you see how to use a custom format in a text box to
display TBD for a Null value.

 

[John Spencer]

Error in your expression you left out the divisor operator

IIf([ConstructionCost]/[Sqft]>0,Format([ConstructionCost]/[Sqft],"Currency"),"TBD")

Alternative solution that should return a string value:

Format([ConstructionCost]/[Sqft],"$0.00;TBD;TBD;TBD")

That will mean you cannot do math on the value returned by this expression.
Well, you can buit then you will need another expression to test if the
value is numeric before trying to use it in any math expressions.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Keith,

Thanks. One more for you.

This is what I have:

IIf([ConstructionCost]/[Sqft]>0,Format([ConstructionCost][Sqft],"Currency"),"TBD")

When I try to run it, it puts [ ] around the "TBD" and asks for a value.

Help!?

I hope someone can help!

I am running MS Access 2003. I have two Expressions built in a query
that
look like this:

Expr5: IIf([ConstructionCost]>0,[ConstructionCost],"TBD")

Expr2: Format([ConstructionCost],"$#,##0;-$#,##0")

I want to combine the two so that the output is either: $500,000 (not
500000) or TBD.

I can't figure out how to wrap the Format around the IIF.

Try

IIf([ConstructionCost]>0,Format([ConstructionCost],"$#,##0;-$#,##0"),"TBD")

Keith.
www.keithwilby.com

 

[Allen Browne]

You're right: outputting text means you will not be able to divide the value
by another reliably.

Attempting to divide "TBD" by something will probably give a "mismatched
data type" error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks much.

I have it formatted a number right now. In another expression, i have
that
ConstructionCost divided by SqFt to get the cost per sq ft.

If I format it as text in this one expression, will it affect the others?

First thing to consider is what kind of data you want in this field.

If you just want text (no need to perform any mathematical operations
such
as summing), use:
IIf([ConstructionCost] > 0,
Format([ConstructionCost],"$#,##0;-$#,##0"),
"TBD")

But if you want the result to be a true number so you can sum or sort it
correctly, you cannot use text such as "TBD", and you cannot use the
Format() function, since both of these will convert it into text.

The real problem here might be the use of 0 where the value has not yet
been
determined. You could solve that problem by using Null instead of zero.
Null
means the value is as yet unknown (which is what I assume TBD is about),
whereas zero should be reserved for those cases where the cost is known
to
be zero.

Then in any form or report, you can use the Fomat property to show the
field
in any way you want, including using the literal letters TBD as the
format
for Null.

I hope someone can help!

I am running MS Access 2003. I have two Expressions built in a query
that
look like this:

Expr5: IIf([ConstructionCost]>0,[ConstructionCost],"TBD")

Expr2: Format([ConstructionCost],"$#,##0;-$#,##0")

I want to combine the two so that the output is either: $500,000 (not
500000) or TBD.

I can't figure out how to wrap the Format around the IIF.

 

[Haitham]

i am having problem in building 2 iif in one expression:
i have a file called CUR including the following 840, 422,... ect
i was building an expression when cur=840 to put it USD and cur= 422 to put
it gbp

Expr1: iif([cur]=840, "usd", [cur]=422,"lbp")

i received syntex error.
if i choose only one canges it works :

iif([cur]=840,"usd") this work with me, but when putting 2 iif it dosent work.

any suggestion?

 

[John W. Vinson]

i am having problem in building 2 iif in one expression:
i have a file called CUR including the following 840, 422,... ect
i was building an expression when cur=840 to put it USD and cur= 422 to put
it gbp

Expr1: iif([cur]=840, "usd", [cur]=422,"lbp")

i received syntex error.
if i choose only one canges it works :

iif([cur]=840,"usd") this work with me, but when putting 2 iif it dosent work.

any suggestion?

Read the VBA Help for IIF. It doesn't take four arguments, it takes three: an
expression which evaluates to TRUE or FALSE, a value to be returned if it's
TRUE, and a value to be returned if it's FALSE. Your third argument makes no
sense in that context!

Take a look at the VBA help for Switch(), it may be closer to what you want;
on the other hand, if you have more than two or three codes, you may want to
create a very simple little lookup table with two fields, Cur and CurText, and
join it in your query.