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.



0
Utf
12/5/2007 2:46:01 PM
access.queries 6343 articles. 1 followers. Follow

7 Replies
580 Views

Similar Articles

[PageSpeed] 34

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" <Huber57@discussions.microsoft.com> wrote in message
news:76C8A10F-A893-4546-8205-D872DAD9EC01@microsoft.com...
>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. 

0
Allen
12/5/2007 2:55:21 PM
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" <Huber57@discussions.microsoft.com> wrote in message
> news:76C8A10F-A893-4546-8205-D872DAD9EC01@microsoft.com...
> >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. 
> 
> 
0
Utf
12/5/2007 3:10:00 PM
"Huber57" <Huber57@discussions.microsoft.com> wrote in message 
news:76C8A10F-A893-4546-8205-D872DAD9EC01@microsoft.com...
>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 

0
Keith
12/5/2007 3:36:53 PM
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" <Huber57@discussions.microsoft.com> wrote in message 
> news:76C8A10F-A893-4546-8205-D872DAD9EC01@microsoft.com...
> >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 
> 
> 
0
Utf
12/5/2007 3:50:06 PM
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]
0
Marshall
12/5/2007 4:22:18 PM
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" <Huber57@discussions.microsoft.com> wrote in message 
news:2557731D-5A57-4BCA-8170-D4242156EBDE@microsoft.com...
> 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" <Huber57@discussions.microsoft.com> wrote in message
>> news:76C8A10F-A893-4546-8205-D872DAD9EC01@microsoft.com...
>> >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
>>
>> 


0
John
12/5/2007 4:43:56 PM
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" <Huber57@discussions.microsoft.com> wrote in message
news:3B86F8B5-849B-4E05-8128-F7F03EEED7DB@microsoft.com...
> 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" <Huber57@discussions.microsoft.com> wrote in message
>> news:76C8A10F-A893-4546-8205-D872DAD9EC01@microsoft.com...
>> >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. 

0
Allen
12/5/2007 11:42:00 PM
Reply:

Similar Artilces:

Format as Percent in Formula
Hi, I know this must be somewhere but I can't find it. I have the following formula: ="M.J.F.("&DOLLAR(Variables!C26,2) &") x "&Variables!C8 Which is all ok, but I need the last variable to be a percent so the cell shows: M.J.F.($1850.00) x 10% and not M.J.F.($1850.00) x 0.1 Can anyone tell me how I can do this. Many thanks, Anthony. How about: ="M.J.F.("&DOLLAR(variables!C26,2) &") x "&TEXT(variables!C8,"0.00%") Anthony wrote: > > Hi, > > I know this must be somewhere but I can't fi...

Receipt Formats Refuse to Change
I have looked high and low for a solution on this one to no avail: I am editing receipt templates in the Manager>Database>Registers>Receipt Format screen There is only one entry in the Receipt Formats window, titled "40 Column Receipt". I select Properties, then Properties on the "Sales" receipt. The preview looks exactly as I'd like it to be. For good measure I select "Open" and then "Save As", so I know it's saving a fresh copy. I click OK to exit then Yes to update all transaction types. Now I click on the magnifying glass by "S...

is it possible to export my outlook email files to some standard format ?
I am trying to save all my email messages because I want to try to install linux. One might think there was some way to export your email file to some standard format, but alas It seems I can only export to Microsoft Outlook or Microsoft Exchange from Outlook Express 6. Where is the mail file located ? maybe I can decipher most of it with a perl script or something if there is no other means. Perhaps a linux program like one in open office can read it ? __/ [ surfunbear@yahoo.com ] on Sunday 27 August 2006 17:16 \__ > I am trying to save all my email messages because I want to try t...

formating a column
I am retrieving data through a SQL query and one of the data elements is actually a hyperlink. The cell populates with the entire link reference but it is not an active hyperlink. Is there a way to format a cell to let it know the contents are a hyperlink address? Thanks Art Art In an adjacent column enter =HYPERLINK(cellref) Drag/copy down the column. When/if happy, Edit>Copy>Paste Special(in place)>OK Delete original column. Alternative.......... Use a macro. Sub MakeHyperlinks() 'David McRitchie Dim cell As Range For Each cell In Intersect(Selection, _ ...

format cells
cells BP36-38 won't change when I try to format them with a white or "n color" pattern. Pls. see attached file Attachment filename: san francisco county training sched-updated.xl Download attachment: http://www.excelforum.com/attachment.php?postid=59412 -- Message posted from http://www.ExcelForum.com Hi,' They're not formatted in the 'regular' sense, but conditionally. Go to menu--Format--Conditional format.. and delete the conditions. jeff >-----Original Message----- >cells BP36-38 won't change when I try to format them with a white or "n...

Outlook 2000 "save as" .MSG is not saving in message format??
All: We're trying to save individual messages from Outlook 2000 SR1 to message format (standard Internet RFC822 format) files. For most of last week it was working properly. File -> Save As, change file type to .MSG, and save the message, and it'd be RFC822-format text. Now, Outlook saves the message as a Word document. WTF? How do I get it to go back to the correct behavior, namely saving the message as an RFC822-format text file instead of a Word document? I'm not aware of anything having been changed, and this is happening on many users' systems. I don't see any...

REPOST: Date format pre-1900.
Hi, I have a table of immigration ships that have arrived in Australian shores. Some....okay most.....of the ships arrived before 1900. I was trying to sort by the arrival date, which is in the format of DD MMM YYYY, but that didn't work, as there were some 1900 dates. Tried to re-style the column to DD/MM/YYYY, but the pre-1900 dates don't change to the new style. Any suggestions on how to utilise the pre-1900 dates, so that Excel can recognise them. Cheers in advance. Craig. P.S. Originally sent on the 15/02/2005 - not sure if someone knowledgable missed it in the other 1000s o...

Formatting a cell with a concatenated formula
Sorry for the double post but I'm not sure which is the best forum fo this question so I've posted it in 2 different ones. I have a concatenated formula that pulls text from 2 different cells The 2 cells are formatted differently (i.e. 1 cell has blue text an the other cell has red text and a different font) and I want to kee these formats. However, the cell with the concatenated formula take the format of that cell. Is there anyway to do this -- andy281 ----------------------------------------------------------------------- andy2812's Profile: http://www.excelforum.com/member...

Custom number format always defaults last number to 0.
I am trying to format cells to input credit card numbers in Excell 2000. When I set up a custom number format for four sets of four numbers (####-####-####-####) the result always changes the last number to a 0. How do I overcome that? Excel only lets 15 digits be used in numbers To have the 16th number most people use text. "scubadave" wrote: > I am trying to format cells to input credit card numbers in Excell 2000. > When I set up a custom number format for four sets of four numbers > (####-####-####-####) the result always changes the last number to a 0. How...

Count the Colorindex of a cell with a conditional format
Is there any way (with or without code) to count the number of cells that have a condition that displays a specific colorindex tied to a conditional format? Thanks, Barb Reinhardt Barb, I answered no to this once and Bob Philips corrected me and pointed me towards his page below. You can count conditionally formatted cells but it ain't easy. http://www.xldynamic.com/source/xld.CFConditions.html -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the questio...

Help with IIF statement
I need help with an iff statement on my form. Basically I have a new record when the "Insert" key is pressed. But I want to stay with the current record if [TotalHrs] is greater or less than IDRb_subform.Form!SumHours. Otherwise a new record is called for. I think I'm close here but a little help would be appreciated. Thanks, Randy Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer) On Error GoTo ErrRoutine Select Case KeyCode Case vbKeyInsert If [TotalHrs] > 0 Then If IDRb_subform.Form!SumHours <> [TotalHrs] Then If MsgBox("Total Ho...

Address Format Problem
I am using Pocket PC 2002 and I'm trying to send email via active sync to MS Outlook 2002 and I'm experiencing problems. ActiveSync transfers the email from Pocket Outlook over to the Outlook 2002 (desktop) outbox, however, it drops it into the outbox with quotations around the email address (in the To: field). For example, the email address will read as follows: 'jdoe@sbcglobal.net' The quotations then cause the following error reply from my ISP: Message from yahoo.com. Unable to deliver message to the following address(es). <'jdoe@sbcglobal.net'>: Sorry, I c...

Another Conditional Format Question
I've formatted a number of cells so that their contents "go blank" if the cell value is zero, but display with their values otherwise. The "blank is accomplished by setting the font color to "white". Now is there either a conditional formatting or macro trick which would enable me to only apply globally the conditional formatting when I want it? In other words, the cells would normally show zero values, but could be globally turned "blank" at my command. -- PT One way would be to add a switch to your condition in the CF. For instance: Defi...

Tiff format problems with Access 2007
We have some databases created in Access 2000 with .tif files embedded to the forms and reports. And we are planning to migrate to Access 2007. We open databases created in Access 2000 with Access 2007 and we get the following error massage: 2114 <database name> doesn't support of the file 'I:\Tmimage\2005\20051028.tif,' or file is too large. Try converting the file to BMP format. Is Access 2007 supports tif format or is there any solution for this problem? The TIFF graphics filter was withdrawn in Office 2003, so many users with tiff images found that their app...

Cell formatting protection
Here is my delima. I have excel 2002 and i have a sheet that i do no want anyone editing without entering in a password to unprotect it. Currently i have every cell on the sheet locked. and I wanted to setu some advanced permissions so that certain users dont even have t unlock the file that part works great. This sheet background color are used to indicate if the data has been processed or not ( not m idea to do it that way ) so i want to make it so that if some rando person that is not specified in my permissions list tries to change th background color it wont let him without typing in a...

Importing bank statement that has a different date format
Is there a way import a money format statement file that has a different date format? I use mm-dd-yyyy format in my Money but i would like to import statements that have the dd-mm-yyyy format! Curretnly i open the file in notepad and manually change the dates and then import the file into money. It would be nice to have an option to automate this during the import process! Thanks VJ ...

formatting columns #3
This is the first time I've posted to this group. I have some how managed to get by using Excel while never having any training with it. With that said here's my question/problem. I also use Access and I'm exporting a query from Access to Excel. The query has many dimensional entries. An example would be; Part # 1 Hole Size 1 Hole Size 2 Hole Size 3 2.000 2.001 2.004 Part # 2 Hole Size 1 Hole Size 2 Hole Size 3 2.001 1.999 2.002 Part # 3 Hole Size 1 Hole Size 2 Hole Size 3 2.002 2.001 2.003 ...

Formatting a date different from the date options available
I am using ACCESS 2007 and would like to format the date as ex: 2009-Nov-19 in a table. It would also be nice if this cell would automatically format the date to the above format even if the person entering the date typed in 7/9/1975. Could anyone help me do this, or can it even be done? Thanks, Janet B Janet B <Janet B@discussions.microsoft.com> wrote: >I am using ACCESS 2007 and would like to format the date as ex: 2009-Nov-19 >in a table. It would also be nice if this cell would automatically format >the date to the above format even if the person entering ...

Formatting Subtotals #2
When using Data, Subtotals to create subtotals in a detailed spreadsheet, how do I set the format so that all subtotal lines are bolded and are followed by a specified number of blank rows? One option - Collapse the sheet to show only the subtotals, then select visible cells only (F5>Special>Visible Cells only) Then Bold those cells and, instead of adding empty rows, make the subtotal rows taller and format them with the vertical alignment at the top "slh" wrote: > When using Data, Subtotals to create subtotals in a detailed spreadsheet, > how do I set the f...

new currency format
How can I add a new currency format? I want to have CCN appear before the amount. select your cell(s) format|cells|number tab|Custom category "CCN"0.00 Maybe???? Poison wrote: > > How can I add a new currency format? I want to have CCN appear before the > amount. -- Dave Peterson <Format> <Cells> <Number> tab, Click on "Custom", and select one of the currency formats that you like. Then, just select the currency sign in the "Type" window, and change it to CNN -- HTH, RD ---------------------------------------------------...

Money 2007 -- QIF import
Good Evening! After a little trial and error, I am able to import account transactions from another system (not a bank statement, my actual register transactions) with little trouble except for the date format. (Before someone gets mad at me, I really don't think this has anything to do with my regional settings!) As recommended by an MVP from this forum, I open my CSV file with excel, then use a converter (add-in) called XL2QIF to create a qif output file. The actual "data" in a date field in my original csv file is "060106" (mm/dd/yy) which is June 1, 2006,...

Conditional Formatting with Text
Hei gurus, How can I tell CF to turn a cell green if the cells contain text "great" and red if the cells contain "unacceptable??" TX a great deal cell value>="great">format>patterns (choose green) add cell value>="great">format>patterns (choose red) >-----Original Message----- >Hei gurus, > >How can I tell CF to turn a cell green if the cells >contain text "great" and red if the cells >contain "unacceptable??" > >TX a great deal >. > Select the range of cells Format > Co...

How do I format a long number fields as text in an Excel .CSV file
I am creating an Excel .csv text file with a program and I am trying to format a field that contains about 20 numbers as a text filed. When I open the .csv file with Excell that column does not show correctly. It appears that Excel is trying to treat it as a number. It tried bounding the field with double quotes and that doesn't seem to make a difference. The filed still shows up with an "E" towards the right end of the filed that I can't make go away. Cyber, It's probably happening when you open the csv file with Excel, not when the file was written. Open t...

More than 3 on Conditional Formatting
Can it be done? I don't want to have to write a routine in VBA to scan all the cells just to change the colour..... i.e. 1 is red 2 is a lighted red, 3 is amber 4 is a ligther amber, and 5 green and 6 a lighter green... I need some help here guys... Anyone point me in the right directions... Regards. modify one of these. The first one belongs in the SHEET code module. right click on the sheet tab>view code>insert this>save Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 1 Then Exit Sub With Target.Interior Select Case UCase(Target) Case "...

SQL Reporting Services
I have deployed the canned SRS Reports to the report server some of them run, some of them don't. I have two questions. 1. The Trial Balance Detail Report works. When I export it to excel, the dollar amounts come out formatted as "General" with a $ sign. I have to go change them to a number format so I can add numbers together. Where do I find this original report so I can make changes to it. I'm looking for the ..rdl file and I just can't find it. 2. The reports that run are the ones that have one data source, I think it is called dynamicsgp10. The ones t...