Hi, I have this function in my query, but the results are wrong (i don't get error, but the results are unexpected)

IIF(HOUR1 BETWEEN '8:30' AND '10:00','OK E', IIF(HOUR1 BETWEEN '18:30' AND '20:00', 'OK S', 'NO OK')) AS P_OK

HOUR1 field has the format '8:21:00'

Any ideas? Thanks

What datatype is HOUR1?

First add these functions to your modules section

Function TimeToMins(AnyTime As String) As Integer
If AnyTime = "" Then Exit Function
If Len(AnyTime) = 8 Then
AnyTime = Left(AnyTime,5)
End If

TimeToMins = (Left(AnyTime, 2) * 60) + Right(AnyTime, 2)
End Function

Function TimeOk(Minutes As Integer) As String

Select Case Minutes
Case 510 To 600
TimeOk = "OK E"
Case 1080 To 1200
TimeOk = "OK S"
Case Else
TimeOk = "No Ok"
End Select

End Function


Then in your query create 2 new columns

TimeInMins:TimeToMins(Hour1)
P_OK:TimeOK(TimeInMins)

The first column calculates the number of minutes past midnight
The second evaluates which response to return. By using functions as opposed to nested iff's is that a) there is a maximum number of nested iffs b) using a case statement is easier to maintain.

David