What Does +IIF Do as Opposed to IIF?

What Does +IIF Do as Opposed to IIF?James Igoehttp://code.comparative-advantage.com/
0
james
3/7/2007 4:46:31 PM
access 16762 articles. 3 followers. Follow

5 Replies
1130 Views

Similar Articles

[PageSpeed] 20

AFAIK, there's no such function as +IIf in VBA. If I had to guess, I'd say that the + is simply arithmetic: add the results of the IIf function to whatever preceeded it.How are you seeing it used?-- Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)<james.igoe@gmail.com> wrote in message news:1173285991.823665.303480@p10g2000cwp.googlegroups.com...>> What Does +IIF Do as Opposed to IIF?>> James Igoe> http://code.comparative-advantage.com/> 
0
Douglas
3/7/2007 5:50:29 PM
On 7 Mar 2007 08:46:31 -0800, "james.igoe@gmail.com"<james.igoe@gmail.com> wrote:>>What Does +IIF Do as Opposed to IIF?>>James Igoe>http://code.comparative-advantage.com/Nothing much, other than (perhaps) adding the numeric value returnedby IIF to some other expression. What's the context? Why do you ask?             John W. Vinson [MVP]
0
John
3/7/2007 6:27:35 PM
+IIf(UnmatchedDeals.System<>UMD2.System,"111","999") &+IIf(UMD2.Index>UnmatchedDeals.Index,UMD2.Index,UnmatchedDeals.Index)On Mar 7, 1:27 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>wrote:> On 7 Mar 2007 08:46:31 -0800, "james.i...@gmail.com">> <james.i...@gmail.com> wrote:>> >What Does +IIF Do as Opposed to IIF?>> >James Igoe> >http://code.comparative-advantage.com/>> Nothing much, other than (perhaps) adding the numeric value returned> by IIF to some other expression. What's the context? Why do you ask?>>              John W. Vinson [MVP]
0
james
3/7/2007 6:39:57 PM
Those plus signs would appear to serve no useful purpose.-- Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)<james.igoe@gmail.com> wrote in message news:1173292797.239325.203620@64g2000cwx.googlegroups.com...>> +IIf(UnmatchedDeals.System<>UMD2.System,"111","999") &> +IIf(UMD2.Index>UnmatchedDeals.Index,UMD2.Index,UnmatchedDeals.Index)>>>> On Mar 7, 1:27 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>> wrote:>> On 7 Mar 2007 08:46:31 -0800, "james.i...@gmail.com">>>> <james.i...@gmail.com> wrote:>>>> >What Does +IIF Do as Opposed to IIF?>>>> >James Igoe>> >http://code.comparative-advantage.com/>>>> Nothing much, other than (perhaps) adding the numeric value returned>> by IIF to some other expression. What's the context? Why do you ask?>>>>              John W. Vinson [MVP]>> 
0
Douglas
3/7/2007 7:07:10 PM
If you're trying to add the values, leave out the ampersand.  The first + sign doesn't seem to be doing anything.  You can also use + as a concatenation operator if you want the concatenation to return Null if any of the values are Null.  For instance:[Address1] & Chr(13) & Chr(10) & ([Address2] + Chr(13) + Chr(10)) & [City]will evaluate this part of the expression to Null if Address2 is null:([Address2] + Chr(13) + Chr(10))If there is no Address2 field, the carriage return and line feed Chr(13) + Chr(10) become part of the null expression within parentheses, and are in effect skipped, which means there won't be an empty space in the address where the Address2 field would be.<james.igoe@gmail.com> wrote in message news:1173292797.239325.203620@64g2000cwx.googlegroups.com...>> +IIf(UnmatchedDeals.System<>UMD2.System,"111","999") &> +IIf(UMD2.Index>UnmatchedDeals.Index,UMD2.Index,UnmatchedDeals.Index)>>>> On Mar 7, 1:27 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>> wrote:>> On 7 Mar 2007 08:46:31 -0800, "james.i...@gmail.com">>>> <james.i...@gmail.com> wrote:>>>> >What Does +IIF Do as Opposed to IIF?>>>> >James Igoe>> >http://code.comparative-advantage.com/>>>> Nothing much, other than (perhaps) adding the numeric value returned>> by IIF to some other expression. What's the context? Why do you ask?>>>>              John W. Vinson [MVP]>> 
0
BruceM
3/7/2007 7:52:01 PM
Reply:

Similar Artilces:

IIF Statement in a Query
I am writing a query where I want it to check a form for the option selected in a frame. I want it to choose a single item for the field if one option is selected (option 2) and select everything except that single item if the other option is selected (option 2). I am using an IIF Statement to do this and it will work for Option 2 (show all records with the field as the single item) but not for option 1 (show all records except where the field is that single item. My IIf statement is as follows: IIf(TicketACD()=1,<>"BSC Web Client","BSC Web Client") I have als...

Using IsNULL/IsError with IIF funtion.
I've built an expression query to calculate two fields as: % NCOTH_NEW ORDER RATE: IIf(IsNull([VOL NCOTH_NEW]/[VOLUME SIEBEL ORDERS_NEW]),0,[VOL NCOTH_NEW]/[VOLUME SIEBEL ORDERS_NEW]) The issue here is if I replace 0 with text "N/A" the calculations for rest of the other fields won't display properly. They become something like 8.695652E-02 Can someone pls help on what am I doing wrong here. Leaving 0 isn't an option as that shows 0.0% for fields where both VOL NCOTH_NEW & VOLUME SIEBEL ORDERS_NEW has 0 values. Thanks Try this: % NCOTH_NE...

Weird results (I think) for IIF
Access 2003 I have a table with fields and columns as follows: DIED DEATH DATE ------- --------------------- 1926(31.10.) null null 1 Aug 1871 1857(1.4.) 1 Apr 1857 no details null null null 1857(1.4.) * I need to create a formatted DAY, MONTH and YEAR column for each with the following rules: if DIED starts with a year DAY = day in DIED date MONTH = month (3 alpha) in DIED date YEAR = year in...

IIf function Problem
I am getting an error message that indicates that Access is still trying to perform the DLookup or "False" when the result should be "True". Here is my code: IIf(IsNull(Me.Days), "No repair time should be allotted.", _ DLookup("Return", "ltblDays", "Lookup = " & Me.Days)) So when Me.Days is tabbed into and out of w/o entry, or an entry is made and then deleted, the DLookup attempts to run but is null and so cannot complete it's Dlookup. Any ideas why the code isn't recognizing a True statement and stopping b...

IIF Expression returning Zero Records in Query
hello, MS ACCESS 2003 on XP PRO. I have a query (obviously) and one of the criteria (in design mode) used to be: <>"PDI And <>"PD" But now I need exception in case I am specifying (on a form) that I mean PDI or PD. so I'm trying to write the criteria something like this: IIf([Forms]![MainMenu]![g_Route]="Pre-Drive Checklist","PDI",IIf([Forms]![MainMenu]![g_Route]="Post Drive Checklist","PD","<> 'PDI' And <> 'PD'")) This works if either of the conditionals is met...

Using If or Iif to determine a value
I have a query that calculates various costs. There's one field that has 3 possible values, and depending on what it is the value (cost) can be shown in another field. It's along these lines: If [Field1] = US then [Field2] = 1.00; If [Field1] = Europe then [Field2] = 2.00; If [Field1] = Rest of World then [Field2] = 3.50 Could somebody give me the correct syntax, please? Many thanks CW While you can do this with an IIF() statement (see Access HELP for exact syntax), what about the idea of using the lookup table that holds "US", "Europe" and other va...

Complicated IIf Statement Problem
This IIf statement is in an unbound control on a report. When I paste it into the Control Source under the unbound control's properties - I get an error saying the expression is too complicated. I've double checked it, and it is crafted correctly. I'm guessing it is too long. Is there a maximum amount of IIf checks? Just a little background - There are 29 different possible fields from a query that I might want to populate this control with, depending on the value of another field. Unfortunately, I am stuck working with a badly built database and trying to make this work ...

Nested "IIF" or....?
My question is quite complicated so I will try and explain it a simple as possible On a form, I have a set of radio buttons that selects the criteria I need to use when deciding if something "Passes" or "Fails" The the options are: [1] 98 - 102 - a pass here would be between 98 and 102 otherwise fail [2] 95 - a pass here is simply above 95 Selecting one of these would put a 1 or a 2 in the "Specification" field on the table "Lot" I have manged to sucessfully bring the specification field into my datasheet using a simple query, but now I need to ...

IIf then
I have an unbound text box in a report where I want to do a simple calculation. IIf [UnitCount] has data then [UnitSize]/[UnitCount] IIf [SubUnitCount] has data then [UnitSize]/[UnitCount] I can't seem to get the syntax right. Any help would be appreciated. THANKS! -- www.Marzetti.com Try something like this: =IIf([UnitCount] <> 0, [UnitSize] / [UnitCount], Null) That avoids the division by zero 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....

IIF statement 08-23-07
I would like to use an IIF statement to look for the text in field [Type] and if it is not Voluntary, to show Employer on the report. I have tried IIF([Type] Not Like "Voluntary","Employer","Voluntary") and all I get is Employer listed on the report. It changes those that are voluntary to employer. Not sure what I am doing incorrectly. Karen Check Access HELP on "Like". Your IIF() expression tells Access to look for EXACTLY "Voluntary" in the [Type] field. If you meant "Voluntary" SOMEWHERE IN the [Type] field, you'd ne...

IIF Statement
Hello, In my Access 2003 database I have the following IIF statement in one of the modules. DoCmd.OpenQuery IIf(ans = vbYes, "qryGetMWF", "qryGetWF") This works fine but I want to change it so that a 2nd query is run in both conditions. If True I want the following 2 queries run. qryGetMWF and qryTrainingDatesMWF If False. qryGetWF and qryTrainingDatesWF How does the IIF statement need to be changed? Thanks. -- Regards, Chris "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message news:F4AB3547-70C8-46AC-89DB-...

Multiple Yes/No fields + IIF
I am working in Access 2003 and trying to figure out a query from my table that contains multiple Yes/No fields. The user can select Yes or No for up to five shop names. I am trying to run one query with the following IIF statement in the criteria row of the query for each field name instead of 5 queries and 5 reports running off each query: IIf([Forms]![frmSelectShop]![cboShopName]=1,Yes,No) The User would select the shop from a dialog form that would run the report based on the query Now, each record could have more than one Shop selected as "Yes" or "No". If I wa...

IIf Syntax
Can this be done? If CDSub = 2 or 3 Then MsgBox "OK" Else if CDSub = 4 Then MsgBox "Maybe" Elseif CDSub = 5 Then MsgBox "NO" End If Of course the if statement is more complex but the point I'm gong after is that I need to combine the 2 and 3 conditions together. Thanks DS DS <bootybox@optonline.net> wrote in news:d%6Yi.767$o41.295@newsfe09.lga: > Can this be done? > > If CDSub = 2 or 3 Then > MsgBox "OK" > Else if CDSub = 4 Then > MsgBox "Maybe" > Elseif CDSub = 5 Then > MsgBox "NO" > End If...

IIF
I,m using two bound text fields to create a thrid unbound field they are: 2 bound to same table - [contractnum] and [fmscase] - both are text, format = >, required = no, allow zero lenght = no, indexed = yes (dups ok). The 3rd unbound field is [contract or fms no.] in it's control source I'm using the following: "=IIF([contractnum]=null,[fmscase],[contractnum])" The above simply doesn't work and I'm not sure why. The goal is to have the 3rd field show either the contract no. or the fmscase no (one or the other) - if there is a contract no there will not b...

Iif Statement help 03-09-07
Hi, basically i need a soltuion which is a huge Iif statement ibelieve maybe a nested Iif staement.OK basically i want the statement to be like this:IIf[X]=Bob,IIf[BobTotal] Between 1 and 2,"Green",Iif[BobTotal] Between2 and 3,"Blue",IIf[X]=Ben, Iif[BobTotal] Between 3 and 4,"Grey" etc..So basically if field X has a value then it gives a result dependingon the value in field y. However the value in field X may be one of 5options, therefore i suppose i need some sort of if statement in an ifstatement in an if statement..Many thanks in advance to all help recieved. ...

iif, len, replace queries
Hi, I am somewhat of a beginner in regards to my Access skillset. I am trying to create two separate queries and am in need of assistance. I have been trying to use iif, len and replace but am struggling. 1. Text Field (numeric and alphanumeric): If the length of the string is not equal to 10 characters I need to set that value to "9999999999". 2. Text Field (numeric and alphanumeric): I need to remove all "-" and all spaces. From this result I need to display the first 10 characters. If there are less then 10 characters then no value is to be displayed. Thank you...

IIF statement 01-15-10
I have want to specify certain keyword and put them a value. For example:keyword "CD", "Book", "newspaper" to have a value 0 which i can accomplish by using the iif Expr1: IIf([Keyword]="CD","0",IIf([Keyword]="Book","0"), ([Net]/0..1*0.55*1.1)) What if just say iif not these (CD, Book, newspaper) then put a value of -1? This type of logic should be built using values in tables. Hard coding values like "CD" and "Book" into expressions in queries or other places will only lead to high mai...

How to put IIF expression
I have this data in my tblsk LA UN 10000 0 10000 5000 10000 10000 How to write an iif function if UN = 0 ,"FD" UN = LA,"ND" UN >0<LA,"PD" Thanks IIf([UN]=0,"FD",IIf([UN]=[LA],"ND",IIf([UN]>0 And [UN]<[LA],"PD",""))) "zyus" wrote: > I have this data in my tblsk > > LA UN > 10000 0 > 10000 5000 > 10000 10000 > > How to write an iif function if > > UN = 0 ,"FD" > UN = LA,"ND" > ...

Sum [Price] IIF [BelowSetFloor] >0
How do I write the calculation? for this consignor group I have to list each element of the consignor settlement as a separate item - provide a step by step calculation for each variable (they want to audit each calc) So I first have gross sales= Sum([Price])+Sum([Adjust Sale Price]) next calc I need to do is to take result of this field and subtract prices for lots that no saled because the purchase price was below the established minimum floor. These lots can be identified by a currency field [BelowSetFloor] which is either = 0 (lots sold >= minimum purchase price...

Help with iif statement in query
I have a query which has the following iif statment: Accrued Vacation: (([Consolidated]![Regular Day (REG)]/8)/24) This tells me how many hours have been accrued for vacation in 2008 I have another iif statment in this query which is: Expr1: IIf([Vacation_Balances]![Vacation]=" ",[Accrued Vacation],[Vacation_Balances]![Vacation]) This statement is returning a blank cell for those employees who it should be returning the result from the previous iif statment, however it is returning a blank cell. Can someone help me with this - please let me know what other information is nec...

Need help for msdata expression = "IIF
I am using below expression in xsd... I am getting devide by zero error. For ur information I am filling the dataset with the results obtained from Stored procedure. Now again I need to calculate one field which is the divison of two fields returned from SP. Please notice that the third field is not available in the database. We need to calculate inside xsd using the two fields returned from SP. Please help <xs:element name="CALC_BLDG_SBMT_PER_SQFT_AMT" type="xs:decimal" msdata:Expression="IIF((IsNULL(TOTFLRAREASQFTNBR, 0) > 0), (IsNULL(BLDG_VAL_AMT, 0) / ...

Making items inactive as opposed to deleting them
I have read that it's bad to delete items, and I think I found that true because I went back recently to look at a prior sale and it wouldn't pull up any info on it cause I had deleted the item in question. So, I went in and made a few items inactive to try it, and now I can't find them. Sorry for being an idiot, but how do I access the inactive items in inventory? Brad Couple things you can do is run an item quantity report and filter for inactive = yes....you can also run an inventorry wizard task "set items active" it will show you the items that are flagged...

IIf and return value
I have an access db that has linked tables that are in my payroll system. In a query I am trying to track the changes to a table, the problem is that this table is huge and the programmers put too many fields in it. For every change made a snapshot is sent to an "AT" table and then the change is made to the "T" table. This query concerns federal and state w-4 withholdings. If the value is the same, I want "0" returned, if the value is not the same, then I want the value returned. I am wondering if i have made a circular reference. help? This is the fu...

IIF Statement syntax?
I have a timesheet template that allows a user to enter their Time In, Time out for Lunch, Time back In, Time Out for the day, then calculates the time differences to give total number of hours worked. I am trying to make it so that if the total time for the day is Negative, it assigns zero as the default. Here is the statement that I tried IIF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, ROUND((((E7-E6)+(E4-E3))*24),2),0 Can anyone give me some help on what I've done wrong? I just get #NAME in the box Thanks for the help in advance Mac Could it be that IFF should be IF? Bernard "m...

IIF question
Hello. Doing a report that uses an IIF in a textbox that is text. If the IIF is false I would like the text (mainly 1 or 2 words) not to be underlined. How do I end the IIF so the text is not underlined? Thanks. John I am not sure how you mean, but if you want this underlined based on the true or false then use the properties box and choose the text decoration item and write an expression in there. "JohnE" <JohnE@discussions.microsoft.com> wrote in message news:5C6EEFBC-0DC0-41C0-A35D-3A01209E568A@microsoft.com... > Hello. Doing a report that uses an IIF...