# Thread: IIF Function in Query

1. Registered User
Join Date
Nov 2004
Posts
23

## Unanswered: IIF Function in Query

I have a query called Weekly attendance, from the field total days attended, I would like an IIF function thats says if total days attended is less than 3 then it is equal to \$40 ,if it is more than 3 it is equal to \$75.

Any help is appreciated.

PS: here is the SQL for the query I'm trying to add the IIF function to:

SELECT Sum((Abs([DaysPresent]))) AS [Total Days Attended], QryWeeklyAttendance.ChildID, QryWeeklyAttendance.[First Name], QryWeeklyAttendance.[Last Name]
FROM QryWeeklyAttendance
GROUP BY QryWeeklyAttendance.ChildID, QryWeeklyAttendance.[First Name], QryWeeklyAttendance.[Last Name];

2. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
you basically wrote it for yourself

iif() statements are of the following syntax:

iif(if condition, then, else)

so:

if total days attended is less than 3 then it is equal to \$40 ,if it is more than 3 it is equal to \$75

iif([Total Days Attended] < 3, 40, 75)

3. Registered User
Join Date
Nov 2004
Posts
10
I'm going to differ here:

I doubt if "iif([Total Days Attended] < 3, 40, 75)" will work because I don't think aliases are availble inline (in the same query). At least, I've had a Dickens of a time using it.

iif(Sum((Abs([DaysPresent])))< 3, 40, 75), however, should be fine.

4. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Yup good catch, I didn't even notice it was aliased.

5. Registered User
Join Date
Nov 2004
Posts
10
terribly sorry. I should have tested my theory before speaking. Both will work.

you can't, however, get at them in the where clause (which shouldn't be that much of a surprise when considering the processing of the statement, though it was to me initially). Evidently, I went to the wrong conclusion.

6. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102

7. Registered User
Join Date
Jul 2004
Posts
156
I will have to say, personally, that aliases involved in IIF functions have saved me much headache. I have a query that would be murderous without using the alias in the function.

But I have to also say that I'm not sure why aliases will work sometimes and not others. I'm not a SQL guru and don't understand how it works exactly. Can anyone explain to me why an alias wouldn't work in one part of a query but will in another? I mean, how many books out there will tell you that you can't use the alias of one field to insert it into another field as in performing a calculation on it. But when I use that same alias in a different field in an IIF function...it works. Wha-?

8. Registered User
Join Date
Nov 2004
Posts
23
Thanks alot. It worked but I have another problem. How do I sum the monthly income based on the amount due weekly(am I making sense?)
Ok...what I mean is when I run the query weekly it gives me a total due for that week for each child...now if a Child paid \$40 in week one because he/she attended <3 days for the week but in week 2 the same child attendance was >3 days and had to pay \$75. At the end of the month can I get a summary showing \$115?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•