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:

More topics

 

Using the IIF Function

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

Using the IIF function in MS Access

Preview
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?

IE.

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

Excel link with access differs from simple export from access

Preview
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.
 

excel link with access differs from simple export from access

Preview
I have a wierd one. 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
 

Query With IFF Function

Preview
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

Preview
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):

[code]

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?

Preview
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

Preview
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:
IIf([Mils]=18,25,IIf([Mils]=27,22,IIf([Mils]=30,20,IIf([Mils]=33,20,IIf([Mils]=43,18,IIf([Mils]=54,16,IIf([Mils]=68,14,IIf([Mils]=97,12,0))))))))

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
 

Iff Statement Meeting 2 Conditions

Preview
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:

=Count(IIf([ControlName]="Yes",0))

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
 

Nested IIF option?

Preview
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]