IIF Function in Query

[Latoya]

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];

 

[evel79]

You can try something like this to a subquery from your original query:

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

 

[Latoya]

Thanks alot.

 

[Mile-O]

Suggestion

Open a new module.
Enter the following into the module.

Public Function GetValue(ByVal bytDays As Byte) As Currency
    ' The constants. You can change the default numbers from here.
    Const Value1 As Currency = 40
    Const Value2 As Currency = 75
    Const MinDays As Byte = 3
    ' The function
    If bytDays < MinDays Then
        GetValue = Value1
    Else
        GetValue = Value2
    End If
End Function

Now, you can change your query expression to:

Expr1: GetValue([Total Days Attended])

This eliminates the need for the IIf to work as the IIf function can be slow - especially over a large number of records as it has to evaluate both sides of the expression. The code above does one evaluation and, should you need to change the values, you can just change it in the code without having to look through queries for the expression you want to change.