Trouble with IIF and time

[plenilunio]

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

 

[neileg]

What datatype is HOUR1?

 

[DCrake]

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