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.

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

iif Function

iif Function Icon
In Access, the iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

Truble with Date in Query and IIF

Truble with Date in Query and IIF Icon
I'm still having a problem trying to use a date in a query where there's an IIF column.

This is what I have:

90Amt: IIf([TransDate]#5/1/2010#,[Debit],0) and it doesn't work. Not sure, but Dick told me I should use format for the date, but I don't know how to do that in an IIF or if that is the problem.

IIF function to return 3rd and 5th letter in a result

IIF function to return 3rd and 5th letter in a result Icon
I am trying to write an IIF statement in MsQuery, but I keep getting syntax errors.

IIF(((LEFT(ATA_MAN_NBR,3)='-' AND IIF(LEFT(ATA_MAN_NBR,5)='-','True','False))

iif(isnull) Function

iif(isnull) Function Icon
I am getting an invalid error for the syntax below
Is =iif(isnull(combo box name in form,qry field Description),,)

Is it possible to use IIF and ISNULL together in an unbound text field and use that expression to get a data from a query?

So basically there are different levels: I, II, I and each has their own description. sometimes Level II and I will be null so I would use the description of Level I but if those combo boxare NOT NULL, I will be using Description of Level II or I.