Related Topics

Using Iif Within A Query

Truble with Date in Query and IIF
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.

Multiple iif statements
I am trying to combine two iif statements and I am trying enter the correct iif statement for it. This is what I have so far.

Original iif statement that exists

I am trying to add another iif statement to the existing iif statement with the following methodology.
Iif original lock does not fall within the filter below, then change [ORIGINAL_LOCK] to [new_App_DT] otherwise leave as [ORIGINAL_LOCK]

Dynamic Multivalue Crosstab Query
I'm after a clever way of creating a cross tab query of data from a Source Table by specifying the column headings in a Column Table without using Iif. Can it be done?

1. Source Table
2. Column Table contains a list of Column Names that are in Source Table.

In a query, is there a way of achieving something like

Data:[SourceTable].[[ColumnTable].[Column Name]]

without using Iif, which presumes that you know what is in Column Table?

I know the Iif approach works but the nested Iif statement needs to match the entries in Column Table.

IIf Update Query working OK but a better way?
I have set up this query in the builder (sql below & db attached):

UPDATE Table3 SET Table3.Item = IIf([Table3]![Item]="Coffee","70%",IIf([Table3]![Item]="Tea","60%",IIf([Table3]![Item]="Herbal Tea","50%",IIf([Table3]![Item]="Vodka","100%",IIf(IsNull([Table3]![Item]),"Get a drink",[Table3]![Item])))));

This is working. Null fields are covered as well as those which are not the above are left unchanged.

How could I do a


in the query builder instead or would this have to be done from a module?

Nested iif statement for last gift amount
I am trying to get the last gift amount in a query.

My query has multiple fields for gifts from 2007-2011. I need one field that shows the last gift since a donor may have given in 2008 but not in any of the other years.

So 2007 maybe blank but 2008 has a gift.

Anyway I thought I could use something like this but it isn't returning the results I am looking for.

Last gift: IIf([2010AAA]=0,[2009AAA]),Iif([2009AAA] =0,[2008AAA], Iif [2008AAA

Outputting proper grammar . . .
So I'm using a nested IIF statement to get a/an from a query.

Paragraph2: IIF([Paragraph3] LIKE "A*", "an ", IIF([Paragraph3] LIKE "E*", "an ", IIF([Paragraph3] LIKE "I*", "an ", IIF([Paragraph3] LIKE "O*", "an ",
IIF([Paragraph3] LIKE "U* ", "an ", IIF([Paragraph3] LIKE "Y*", "an ", "a "))))))

The thing is, that it's working ALMOST all the time, if Paragraph 3 begins with a vowel, it always outputs "an " but if it begins with a consonant it returns "a " about 80% of the time. Why in the world would it ALMOST always work?

Note: [Paragraph3] has it's own IIF statement based on a RND() integeger (if RND()=X, return Sentence1, if RND()=Y, return Sentence2

I don't know if that has anything to do with it

IIF statements in a report
I am trying to format a report using an IIF statement. Basically I have a list of 3 possible names under a group. Each group could have 1-3 names included. Each name is housed in a different column within the query.

I would like to do an IIF statement to not show Staff1 if it is blank and so on, and move the next non-blank staff to the top so there aren't large empty spaces. Itried this:

=IIF([Staff1] IsNull, [Staff2], IIF([Staff2] IsNull, [Staff3], Null))

Nested IIF option?
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]

Union Queries
I have been successful in joining 4 years of data through a union query (which I am very novice at) and off of that query I would like to categorize them by year (crop) and have written the following iif statement which is not producing CY08 records, they are reflecting CY09. I see CY10 & CY11 but just something I've done in the iif statement. Could you please advise my error:

CY: IIf([Pay Period End]<=#10/25/2008#,"CY08",IIf([Pay Period End]<=#10/24/2009#,"CY09",IIf([Pay Period End]<=#10/23/2011#,"CY10"IIf([Pay Period End]<=#4/23/2011#,"CY

Flow chart from VBA IIF function
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

Help with IIf and is null
I am trying to create in the query a criteria to say IIf [a condition is true] then return IS NULL, if false then do nothing.

I tried this but it doesn't work
IIf([Test]![Name]="Peter",Is Null,)

#Error on IIF Statement
I have an IIF statement in one of my reports. The statement works great has long as there is data in the field [Text48]. If that field is blank then I get the #Error. I have tried several NZ or Is Null statements in it but all I get is that the statement is to complex then. What I need is to add a statement that if its blank then 0

=IIf([Text48]>97 And [Text48]<101,"A+",IIf([Text48]>92 And [Text48]<98,"A",IIf([Text48]>89 And [Text48]<93,"A-",IIf([Text48]>86 And [Text48]<90,"B+",IIf([Text48]>82 And [Text48]<87,"B",IIf([Text48]>79 And [Text48]<83,"B-",IIf([Text48]>76 And [Text48]<80,"C+",IIf([Text48]>72 And [Text48]<77,"C",IIf([Text48]>69 And [Text48]<73,"C-",IIf([Text48]>66 And [Text48]<70,"D+",IIf([Text48]>62 And [Text48]<67,"D",IIf([Text48]>59 And [Text48]<63,"D-",IIf([Text48]>0 And [Text48]<60,"F")))))))))))))

Sum in a Query using Fixed Format
I have the following formula in one query (which works):
TotalContribution: Format(IIf([FsOpt1]='1',(([Hourly Rate]*2080)*0.006),IIf([FsOpt2]='2',(([Hourly Rate]*2080)*0.008),IIf([FsOpt3]='3',(([Hourly Rate]*2080)*0.01),0)))+IIf([FsOpt4]='4',([FsAmt4]*[FsMos4]),0)+IIf([FsOpt5]='5',[FsAmt5],0),"Fixed")

In a separate query, I have the following, with "Expression" in the Total field:
CompanyTotal: Sum(CDbl([TotalContribution]))

Data type mismatch in criteria expression

IIF Statement Problem
I have created an invoice report and am using the following IIF expression for sales tax: =IIf([TaxStatusID]=1, [ctlSubtotal]*.075,0)

Whenever I switch to Report View, it keeps prompting me to Enter Parameter Value for TaxStatusID. I used the expression =IIf([TaxStatusID]=1, "Taxable", "Exempt") in a query report before and it worked fine.

How do I fix this?

Keep getting syntax error (comma) in query question
I'm trying to to nested iif statements. I copied it down just like my professor did and it's not working for me. Here is the formula.
" Depth_Level: IIf([Depth]<15="surface",IIf([Depth]<40="shallow",IIf([Depth]<100="medium","deep"))) "
To the left of this field would be the label Depth with a bunch of numbers

Iif statement gives #Name? error
I have created an Iif statement on my text box in a report.
=IIf([Acty_Priority]="3-Medium" And [Acty_Cmplxty]="Low",1,0)
The 2 fields are the headings in a query. Why do I receive a #Name? error when I view the report? The name of the query is "Stats

Pivot with "Iif" statement in SQL
I have a query that worked using the "Switch" option in the Pivot Statement.
I needed to ensure that if in some instances there was no data available in a given Selection of records for one of the fields the Pivot would create, that the query not crash but return a null or "0" value in that field.

To accomplish that, I have added an "Iif" statement to the query.


I get an error now that their is a data mismatch in the query criteria.


TRANSFORM Count(tmp_PA_Att_Range.Type) AS CountOfType
SELECT tmp_PA_Att_Range.WCode, tmp_PA_Att_Range.Type, Count(tmp_PA_Att_Range.Type) AS [Total Of Type]
FROM tmp_PA_Att_Range
GROUP BY tmp_PA_Att_Range.WCode, tmp_PA_Att_Range.Type
PIVOT Switch(IIf([Credits]=1,"1 Credit","0"),IIf([Credits]=2,"2 Credits","0"),IIf([Credits]=3,"3 Credits","0"),IIf([Credits]>=4,"4 or more Credits","0"));

Access 2010, Windows 7.

Skill Level - SQL beginner

Nested iif statements in a query
How many are allowed, it have currently 12 and seems to error out if I try another? My problem is I have a control on a form and many other forms, "WIP Status", this control is set according to a date entered in other controls; WIP Status: =iif(date Recvd] 1 , "New Item", iif([Date tech pickup] 1, "Test", iif(date AWP]1, "AWP", iif([date issue][date AWP]," Ready for Retest", this goes now for many mores date controlls, 12 exact))))))))).

I was told to try nesting them in a macro but I am not sure how to get started. Also I have several queries that have a WIP Status on other forms and reports that if I change the date selection I have to go to every query and make the change.

Sql Syntax Query
So, I'm trying to create a query that will take what the Options Groups in my forms input and change them from 1, 2, 3, or -1 to Yes, No, N/A and 1. I am trying to use an iif statement to do this, this is what I have:
iif([Data].[Customer]=1, [Data].[Customer]= "Yes", (iif([Data].[Customer]=2,[Data].[Customer]= "No",[Data].[Customer]="N/A"))) AS Customer, etc.
Instead of changing each cell from 1,2 or 3 to Yes, No, N/A, it's creating new columns and leaving them blank. I can't figure out what I'm doing wrong. I know sql very roughly, having mostly taught myself. Any thoughts would help!
Notes: Data.Customer is one of the columns in the data- table. I am trying to modify multiple columns. I have also tried something like iif([Data].[Customer]=1, "Yes", (iif([Data].[Customer]=2, "No", "N/A"
-this did not work either

Excel link with access differs from simple export from access
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.