Using Iif Within A Query
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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.
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
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.
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]
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?
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
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
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
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))
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]
I tried this but it doesn't work