Topics Search

IIF Function Within A Query

IIF Function Within A Query
Views: 3
I only have some experience of IIF statements so please bare with me. I am trying to get the value of three fields into One field. The Problem I have is, there are three fields that are either Yes Or No Fields. For Example here are the only possible outcomes from any record displayed within a query with the three field values.

I would like to create a new field Called "Work Due" that will be able to pick up the Field data "Yes" from either of the three different Fields(1,2 & 3) from any record within the records returned by the query, so that when I
put the query into a form in datasheet view the form will only show 1 field header ("Work Due") instead of 3 with the data from the field type where the data equals "YES"
Sponsored Links:

Similar posts...


Using the IIF Function

Using the IIF Function Icon
Learn how to use the IIF Function in MS-Access query. (video tutorial)

Excel link with access differs from simple export from access

Excel link with access differs from simple export from access Icon
I am running a query in Access 2010. This query is using an IIF function in the expression. The query runs great. So I have an Excel spreadsheet which I want to link to the query.

I link great, it pulls in the data; however, in cases which would trigger the IIF function, those are not polling correctly. I have multiple sheets in the workbook and have use the IIF feature on those as well (different lookup) and they work fine.

If I export the data straight to excel (no linking) from access all data is correct.

Using the IIF function in MS Access

Using the IIF function in MS Access Icon
I want to use the IIF function, to check for the current day, and if the condition is true, then I would like to then run a specific select statement, if false then I would want to run a different select statement.

Is this possible to do with iif and have the result returned in on form with a subreport?


1) IIF(WEEKDAY(DATE(),2),, )

Query With IFF Function

Query With IFF Function Icon
In MS Access, the IIF function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE. IIF function returns one of two arguments depending on the evaluation of an expression just like as we use logic in IF-ELSE structure. It evaluates both true part and false part, even though it returns only one of them. This post covers creating a simple query with IFF function.

Nested IIf or how to use a function instead

Nested IIf or how to use a function instead Icon
I have a query that pulls together billing information. I need to change billing codes according to more specific criteria and I'm not sure how to go about it. I tried a nested IIf statement, but I keep getting an error (I think it's too long).

I know a function is a better way to go about it anyway though, When I seecode, I can generally figure out what it's trying to do, but I can't seem to grasp it enough to do it on my own.

Here's the nested IIf I tried (I tried several variations ~ this is the last one I tried):


Several of the codes are the same, such as If the Program is SVR and the type is 2 or 3, so I tried to do get thevalue to default to the correct Program code if none of the other statements were true, thereby eliminating the need for the extra IIf statements, but I didn't get anywhere with thateither.

Using the IIf Function?

Using the IIf Function? Icon
Can the IIf Function be used in referencing then more than 1-fld? What do I mean? In my MLB dbs I have the following flds:
- HmRd: Either "H" for Home games or "R" for Road Games.
- Team
- Score
- AResults: "W" = win, "L" = Lost
- W: IIf([AResults]="W",1,0) - that works
- L: IIf([AResults]="L",1,0) - that works
- HW: IIf([AResults]="W" & [HmRd]="H",1,0) that DOESN'T work
- RW: IIf([AResults]="W" & [HmRd]="R",1,0) that DOESN'T work

Does anyone follow my question. Is the IIf Function exculsively just involves 1-fld in it's statement or can multiply flds be in an IIf statement?

Using Caluculated fields to return text

Using Caluculated fields to return text Icon
I have a field where the values are limited to 5 or 6 values, e.g., 162,600,400, etc, but the field data type is text. the numbers are not to be used in calculations, they are just parts of a name.(162S124-18) What I want to do is, in another field, return a different string for each of the values.
I've areadey done this with nested iif functions with no problem, the only difference is in this case I want to return text intead of numerica characters, and it won't except this.
Here is an example of a function that work's:

Now If I take this same function and as the true argument,enter, S, or "S", it returns an error. If I enter the true argument as "S", It allowws the expression to go, but whne I save it I get the error

Nested IIF option?

Nested IIF option? Icon
I am using Access 2007 and in one of my queries, I have a lengthy nested IIF statement where I "Group" records based on the [Cost] Field.can this portion be shortened/condensed with another function I'm not aware of?

IIf([Cost]<0,"Proceeds Recv'd", IIf([Cost]=0,"$0", IIf([Cost]<5000,"$1-$4,999", IIf([Cost]<10000,"$5,000-$9,999", IIf([Cost]<25000,"$10,000-$24,999", IIf([Cost]<50000,"$25,000-$49,999", IIf([Cost]<75000,"$50,000-$74,999", IIf([Cost]<100000,"$75,000-$99,999", IIf([Cost]<150000,"$100,000-$149,999", IIf([Cost]<250000,"$150,000-$249,999", IIf([Cost]>=250000,"$250,000+","Blank"))))))))))) AS [Cost Range]

Iff Statement Meeting 2 Conditions

Iff Statement Meeting 2 Conditions Icon
I am trying to create a count function in the report footer. My IIf statements where only 1 piece of criteria needs to be met are working great. I am using:


I need to create an IIf statement that will count only if 2 piecec of criteria are met. I have tried a few things but I keep getting an error when I run the report that the expression is either typed incorrectly or is too complex. I have listed a few of the options I have tried below. Is there a way to fenagle one of these so that I can recieve a count of records meeting both criteria?

=Count(IIf([Control 1]="Yes" & [Control 2]="Yes",0))
=Count(IIf([Control 1] & [Control 2]="Yes",0))
=Count(IIf([Control 1]="Yes", Count(IIF([Control 2]="Yes",0)),0) this one returned a cannot have aggregate function error.
=IIf([Control 1]="Yes",Count(IIf([Control 2]="Yes

Flow chart from VBA IIF function

Flow chart from VBA IIF function Icon
I frequently use complex iif statements in queries or in VBA and after a certain number of months when I look at these functions, I first have to manually draw a flowchart to see what the statement actually does.

Is there an automated way/tool which could do this for me.
(then I could also document the complex statements immideately - as I should be doing like all other programmers.) )

an example would be :

IIf([Contract status]="w","W",IIf([Quantity] Is Not Null And [Quantity]>0,IIf([escalation items].[PN] Is Not Null,"1",IIf([bo]>0,"2",IIf([sos] Is Not Null And [sos]+90>Date(),"3",IIf([ut]="4" Or [ut]="5","4",IIf([PartStatus]="y","5",IIf([sos] Is Not Null,"6","7")))))),IIf([sos] Is Not Null,"8","X")))

I did find a tool which analyses VBA into flow charts, but not IIF's or IF functions