Topics Search

Using Iif Within A Query

Using Iif Within A Query
Views: 0
I am tryin gto build an iif statement which would do different things depending upon the information contained in a specific field. I have to search on a specific characters within a string but am having trouble trying to figure out the syntax for using AND's or OR' would I accomplish this....
Sponsored Links:

More topics


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.

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.

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]

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

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

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]

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,)