IIF Condition

What is wrong with my iif condition below.

=IIF([text87]< 0,"0",[text87])

Access is giving me the following error message.

You omitted an operand or operator , You entered an invalid character or 
comma, or you entered text without surrounding it in quotation marks. Please 
help me find the problem.

Thanks.
0
Utf
1/11/2010 5:46:01 PM
access 16762 articles. 3 followers. Follow

6 Replies
1715 Views

Similar Articles

[PageSpeed] 32

Is the value in text87 a number type or is it text that consists of numbers. 
You can try the following variants to see if they work.

=IIF([text87]< "0","0",[text87])

or

=IIF(Val([text87])< 0,"0",[text87])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Pinacle wrote:
> What is wrong with my iif condition below.
> 
> =IIF([text87]< 0,"0",[text87])
> 
> Access is giving me the following error message.
> 
> You omitted an operand or operator , You entered an invalid character or 
> comma, or you entered text without surrounding it in quotation marks. Please 
> help me find the problem.
> 
> Thanks.
0
John
1/11/2010 6:16:25 PM
Hi - 

I'm assuming that [Text87] contains text data, i.e. "-1" and not -1.  Try
this:

=IIF(val([text87])< 0,"0",[text87])

which converts the text to a number (but only when evaluating the IIF) before
making the less than zero test, and then returns "0" as text or the original
text that was in [Text87].

HTH 

John





Pinacle wrote:
>What is wrong with my iif condition below.
>
>=IIF([text87]< 0,"0",[text87])
>
>Access is giving me the following error message.
>
>You omitted an operand or operator , You entered an invalid character or 
>comma, or you entered text without surrounding it in quotation marks. Please 
>help me find the problem.
>
>Thanks.

-- 
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via http://www.accessmonster.com

0
J_Goddard
1/11/2010 6:28:32 PM
Hi John,
  I have tried both options but it is still throwing that error message. 



"John Spencer" wrote:

> Is the value in text87 a number type or is it text that consists of numbers. 
> You can try the following variants to see if they work.
> 
> =IIF([text87]< "0","0",[text87])
> 
> or
> 
> =IIF(Val([text87])< 0,"0",[text87])
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> Pinacle wrote:
> > What is wrong with my iif condition below.
> > 
> > =IIF([text87]< 0,"0",[text87])
> > 
> > Access is giving me the following error message.
> > 
> > You omitted an operand or operator , You entered an invalid character or 
> > comma, or you entered text without surrounding it in quotation marks. Please 
> > help me find the problem.
> > 
> > Thanks.
> .
> 
0
Utf
1/11/2010 9:01:02 PM
Where are you trying to use this?
Is this the source for a control?
What is the name of the control that this is the source for?

Is Text87 a field or is it the name of a control?



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Pinacle wrote:
> Hi John,
>   I have tried both options but it is still throwing that error message. 
> 
> 
> 
> "John Spencer" wrote:
> 
>> Is the value in text87 a number type or is it text that consists of numbers. 
>> You can try the following variants to see if they work.
>>
>> =IIF([text87]< "0","0",[text87])
>>
>> or
>>
>> =IIF(Val([text87])< 0,"0",[text87])
>>
>> John Spencer
>> Access MVP 2002-2005, 2007-2010
>> The Hilltop Institute
>> University of Maryland Baltimore County
>>
>> Pinacle wrote:
>>> What is wrong with my iif condition below.
>>>
>>> =IIF([text87]< 0,"0",[text87])
>>>
>>> Access is giving me the following error message.
>>>
>>> You omitted an operand or operator , You entered an invalid character or 
>>> comma, or you entered text without surrounding it in quotation marks. Please 
>>> help me find the problem.
>>>
>>> Thanks.
>> .
>>
0
John
1/11/2010 9:30:01 PM
Try ; instead of , !!!

Sometimes in the query editor access needs ; but in sql it is , again. 


0
Lutz
1/12/2010 7:17:45 AM
It worked...Thanks alot. Actually this happened because of my language 
settings..my list seperator was changed to ';' insead of ','.

Thanks again.

"Lutz Uhlmann" wrote:

> Try ; instead of , !!!
> 
> Sometimes in the query editor access needs ; but in sql it is , again. 
> 
> 
> .
> 
0
Utf
1/12/2010 9:05:01 PM
Reply:

Similar Artilces:

Conditional Formatting #91
Hi, I am trying to get my head around the conditional formatting tool but I seem to be having a bit of a problem, I have a list columd of data that looks a bit like this 117.4998 84.3664 117.5 117.922 116.978 I want to highlight the figures where the data is above 105.0 but no matter how I type the CountIF statement in nothing gets highlighted. My code looks like :- =COUNTIF($A$2:$A$500,A2)>105.0 When I go back to check the formula that I have typed in, the .0 has been removed, shall I take it there is something wrong with my syntax. Thanks for any help offerend Phil Hi Phil If I...

Conditional formatting and row highlighting
I have an extensive list of T-Bills. I would like to highlight those rows where the Maturity Date was before today. I can do this for those dates in Maturity Date column that meet this criterion using the conditional format db. But How can a go a step further and have the entire row highlighted? Thanks Say the "row" you're talking about is A:K, with your date of maturity located in Column G. With Row1 as a header row, select A2 to K100, then: <Format> <ConditionalFormat>, click "Formula Is", and enter this: =$G2<TODAY() Click on "Format&qu...

Text conditions
I am writing 2 user guides, one for operators and one for supervisors. Some content is the same, how can I create condition to publish the different contents? -- B. L. ...

Match words
I want to fill a cell with color if it contains a word that contai letters that I specify. For example words then end with letter "s". Wildcard search doesn't seem to work? What is the formula to use -- KH_G ----------------------------------------------------------------------- KH_GS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3292 View this thread: http://www.excelforum.com/showthread.php?threadid=52741 use conditional formatting and formula is =RIGHT(A1)="s" click the format button and pattern and select the colour -- Reg...

how do I create conditional format
I am trying to set up conditional formatting for my spreadsheet to check for duplicate addresses. The addresses are entered in two columns (house number and street name). If there is a match on these two columns I would like the duplicate data to appear red. If somebody knows the formula I should use, I would really appreciate it! Thanks- Paula Hi! Assume your data is in the range A1:B20. Select the range A1:B20 Goto Format>Conditional Formatting. Formula is: =COUNTIF($A$1:$B$20,A1)>1 Click the Format button then select the styles you want. OK out Biff >-----Original Mess...

Conditional Formatting based on deadlines #3
Both suggestions work well, however the conditional formatting i applying if the cell in column E is blank. I want the blank rows t stay white. How do I make it do that? David McRitchie Wrote: > Try =$E2 < (TODAY() + 7) > more information on Conditional Formatting > http://www.mvps.org/dmcritchie/excel/condfmt.htm > > if you tried = DATE + 7 > on the worksheet you would see that it is invalid > --- > HTH, > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm > Se...

Select Records for Subtotals Based on a Given Condition
I have a LONG list of jobs that are sort alphabetically by customer name in one worksheet. I would like to create another worksheet that can go through the first worksheet ('KEN_S_QUERY') and select the rows where in a certain column there is a certain customer, and total up the dollar amount in the column next to the customer name. I am very familiar with access, which makes it harder to word my question here. I would use access, but I need the graphing a charting functions of excel in this case. Is there a way (perhaps through SQL? please dear god, SQL.) to select certain ...

Formula for multiple conditions
The formula i have entered does not work for all conditions. Here is the formula i have entered and works for 2 conditions but not all 4. =IF(A12=0,"",IF(S12=0,"",IF(s12<=R12,"GREEN",IF(S12>R12,"RED/LATE",IF(R12<=TODAY()+3,"YELLOW","WHITR"))))) A12, R12, S12 contain dates and Column T is a status column. If no date is in column A then i want column T to be blank. And if column S date is blank, then i want column T to be white (on target for due date, column R), or yellow (3 days before due date). If column S is >...

Conditional Statement/IRR Formula
A1 through A20 are a series of cash flows (some negative and som positiive). In cell B1, I need to write a conditional statement tha includes only those cash flows from A1 through A20 that will yield 10% (or any other variable target) IRR. Any ideas on how to write th formula? Thanks -- wilson3141 ----------------------------------------------------------------------- wilson31416's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1628 View this thread: http://www.excelforum.com/showthread.php?threadid=31932 ...

Conditional Formatting Excel 2007
Part 1 I have a series of variable numbers in Columns B, C and D (approx 100). I want to be able to shade cells in both cols B and C when the value in the Col C cell is greater than the value in the corresponding Col B cell. Can it be done? If , help would be appreciated Part 2 I also wish to do a comparison with Col B and Col D values and would wish to have similar shading. (As far as col B is concerned, it would shade if Col C /or/ D was bigger - rather than both.) Any help would be appreciated. Tx in advance. Rob Rob, For this answer, let's assume that your table is ...

Conditional Formatting using result of Cell Formulas
Hi, I'm trying to make a conditional format when the result of a formula results in a value being entered in a cell. I'm using Excel Xp and my formula is as follows. =IF(F160="Need Info",TODAY()-S160,"") I've tried using formulas like >0, <>"", <>BLANK etc. but they all seem to fail. All cells are formatted, even entirely blank cells, as if the condition has been met. BTW, is this formula better strategically than the above formula. =IF(F160<>"Need Info","",TODAY()-S160) Just curious. TIA!!! Norm If ...

iif statements
I'm trying to create a calculated field on a form that tracks stock levels. i have three subforms - frmOpeningStock, frmOrdered and frmAllocated. i'm using the following statement which works fine when all fields are populated and works when neither the Total Ordered or the Total Allocated fields are populated. =IIf(IsNull([frmOrdered].[Form]![Total Ordered]) And IsNull([tblAllocated].[Form]![Total Allocated]),[frmOpeningStock]. [Form]![number],[frmOpeningStock].[Form]![number]+[frmOrdered].[Form]! [Total Ordered]-[tblAllocated].[Form]![Total Allocated]) How can i get this to work ...

IIF(AND) statement
Hi I have a number of things I need to test in the query. I need to use IIF statement with AND (for example in Excel you can do it),. Example I need this IIf (and(cat="HS",cat2="JTK",cat3="NT"),"1","2") how to do it in access? -- Greatly appreciated Eva On 7 apr, 21:09, Eva <E...@discussions.microsoft.com> wrote: > Hi > I have a number of things I need to test in the query. I need to use IIF > statement with AND (for example in Excel you can do it),. > Example > I need this > IIf (and(cat="H...

What do I need: multiple nested IIF? Switch()? DateDiff?
Hi again, I have a situation that may call for either a multiple nested IIF, or a Switch(), or a DateDiff, something else I haven't even heard of. Basically, I need to get the number of days during a given month that a given client was in residence. Here's what I need the contol in my report to show: if [Admit Date]>=[Beginning Date], then [Ending Date]-[Admit Date] if [Discharge Date]<=[Ending Date], then [Discharge Date]-[Beginning Date] if [Discharge Date]<=[Ending Date] AND [Admit Date]=>[Beginning Date], then [Discharge Date]-[Admit Date] if [Discharge Date]>[...

More IIf Problems
How do you do between numbers in an if statement? If Me.TxtItemID < 31 AND > 41 Then DoCmd.OpenForm "frmMsgSelect" Forms!frmMsgSelect!TxtMsg = "DISCOUNTS ONLY" Else: End If This syntax doesn't seem to be right. Thanks DS Your statement < 31 AND > 41 is not between but outside. Between would be >=31 AND <=41 for all inclusive of 31 through 41. -- KARL DEWEY Build a little - Test a little "DS" wrote: > > How do you do between numbers in an if statement? > > If Me.TxtItemID < 31 AND > 41 Then...

sound clip as a result of conditional formatting... ?
Does anyone know if it is possible to have Excel play a sound clip as a result of conditional formatting... ? Or maybe as a macro ? I'm a fairly junior Excel user, so go easy, k ? You can play a sound with a VBA call to a Windows API: Declare Function sndPlaySound32 Lib "winmm.dll" Alias _ "sndPlaySoundA" (ByVal lpszSoundName As String, _ ByVal uFlags As Long) As Long Sub AAA() sndPlaySound32 "chimes.wav", 0 End Sub Change "chimes.wav" to the sound file you want to play. -- Cordially, Chip Pearson Microsoft MVP - Excel Pea...

Help with tricky IF statement. Somewhat conditional.
You guys have been so helpful, and I sure do appreciate everything you all have done to each me about Excel. Thanks. The formula in G14 is =B14. There will always be a value in B14. I only want G14 to equal B14 is there is a specific value in F6. If there is not the value that I want in F6, I want G14 to remain blank and not copy the contents of B14 into G14. For example: If F6=n12345 then G14 = B14. If F6=n567AB then G14 = B14. but, If F6=n6609K then G14 should not copy B14 and stay blank. or If F6=n145AB then G14 should not copy B14 and stay blank. There are maybe 5 nXXXXX that I w...

Return result when condition is met
The following formula returns the row number =IF(INDIRECT("A"&ROW(IV4))="","",ROW()) I want it now to only return a result if "D" contains the value "No record" Thank you Gotroots wrote: > The following formula returns the row number > > =IF(INDIRECT("A"&ROW(IV4))="","",ROW()) > > I want it now to only return a result if "D" contains the value "No record" > > Thank you Do you mean this: =IF(INDIRECT("D"&ROW(IV4))="No...

IIF Statement with a twist
I have this already in place, but I need it to also look at the MotherBaby Field and if it is checked then it over rides this and inputs a default of $2256 Rate: IIf([Age]<=4,1589,IIf([Age] Between 5 And 8,1648,IIf([Age] Between 9 And 11,1697,IIf([Age] Between 12 And 14,1787,IIf([Age] Between 15 And 19,1865,0))))) krc547 wrote: >I have this already in place, but I need it to also look at the MotherBaby >Field and if it is checked then it over rides this and inputs a default of >$2256 > >Rate: IIf([Age]<=4,1589,IIf([Age] Between 5 And 8,1648,IIf([Age] Between 9...

Conditional Formatting #123
I would like the lowest value of columns d ,e, & f to be bold. Some of the cells have text. Thank you, Dan On Mon, 10 Oct 2005 13:46:04 -0700, "dbrumit" <dbrumit@discussions.microsoft.com> wrote: >I would like the lowest value of columns d ,e, & f to be bold. Some of the >cells have text. > >Thank you, > >Dan Highlight columns D:F Format/Conditional Formatting Cell Value Is Equal To =MIN($D:$F) Format/Font/Bold OK OK If there is an error in the range, then nothing will be formatted bold. If that is a problem, you could set up two condition...

Conditional formatting with strcomp fails
I've been trying to set up conditional formatting based on the values in two cells, and wanting case sensitivity, using strcomp. A simplified example of the problem: Formula =$A1<>$A2 works fine, and the chosen format is evident only if the values in A1 and A2 are different. But anything with the strcomp function doesn't work. eg if I put in a formula =strcomp($B1,$B2) <> 0 =strcomp($B1,$B2,0) <> 0 =strcomp($B1,$B2) = 0 =strcomp($B1,$B2,0) = 0 =strcomp($B1,$B2) Doesn't matter what values I put in, can't get the format to show. TIA Terry strcomp sounds li...

Like within IIf
I have areport based on a query. The rpeort is rquired by Product or for all products. My pproblem is with the query criteria, in the ProductCode Field: IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>",Like "*",[Forms]![frmPrintSelection]![FilterField]) FilterField is a ComboBox on a Union query. No error message, just a blank report. I have isolated the problem to my use of Like "*" by using other criteria in its place. - used a ProductCode and get the report for this product when I select <<ALL>>. Example: IIf([Forms]![fr...

Doubt with 3 condition
Can anyone help in solving the following doubt? I have to 3 parameters Condition 1, condition 2 and condition 3 If Condition 1 or Condition 3 has value 1 then my result should show as 10. How can I do this using if condition. I thought it was working? saraskandan@gmail.com wrote: > > Can anyone help in solving the following doubt? > > I have to 3 parameters > > Condition 1, condition 2 and condition 3 > > If Condition 1 or Condition 3 has value 1 then my result should show as > 10. How can I do this using if condition. -- Dave Peterson Hi Sara Try =IF(...

conditional formatting #72
how do I copy the conditional format to another cell without the formul changing? When I cut and paste the format, Excel automatically changes the row o column number and on this occasion I don't want this I have over 1000 cells to do with most cells conditional format formul pointing to one other cells value. I am having to edit each cel individually. Place $ around the cells to freeze rows and/or columns. Example: Relative: A2 Mixed (freeze col.): $A2 Mixed (freeze row): A$2 Absolute (freeze row & col.): $A$2 You can click inside the cell reference and press F4 to cycle thro...

Conditional formatting code that looks up a list of criteria
Hi All I'm using Excel 2007, I have a brilliant conditional formatting code that applies conditional formatting to a column of data that contains 40 different product names. The code looks at strings in $C$95:$C$300 (user enters any text but always includes the product name somewhere). When it finds the product name it colours the cell for that product. My problem is that new product codes will need be added to the list. I'd like the user to be able to add new products to the list and the code pick up those new products and include them in the conditional formatting....