Results 1 to 8 of 8
  1. #1
    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.

    Thanks in advance.


    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. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    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. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yup good catch, I didn't even notice it was aliased.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    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. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It's bad practice anyways, I would still go your route.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    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-?
    DocX

    The teachings of God's Begotten: 2 John 1:9

  8. #8
    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
  •