If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IIF and formatting
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. |
#2
|
|||
|
|||
IIF and formatting
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Huber57" wrote in message ... 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. |
#3
|
|||
|
|||
IIF and formatting
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? "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Huber57" wrote in message ... 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. |
#4
|
|||
|
|||
IIF and formatting
"Huber57" wrote in message
... 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 |
#5
|
|||
|
|||
IIF and formatting
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!? "Keith Wilby" wrote: "Huber57" wrote in message ... 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 |
#6
|
|||
|
|||
IIF and formatting
Huber57 wrote:
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. -- Marsh MVP [MS Access] |
#7
|
|||
|
|||
IIF and formatting
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 .. "Huber57" wrote in message ... 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!? "Keith Wilby" wrote: "Huber57" wrote in message ... 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 |
#8
|
|||
|
|||
IIF and formatting
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Huber57" wrote in message ... 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? "Allen Browne" wrote: 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" wrote in message ... 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. |
#9
|
|||
|
|||
IIF and formatting
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? "Huber57" wrote: 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. |
#10
|
|||
|
|||
IIF and formatting
On Fri, 19 Mar 2010 12:54:07 -0700, Haitham
wrote: 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|