COMPARING TIME IN SELECT CASE 1

[jadams0173]

Hey all. I've got a function that I use to so I can summarize some data. A query calls the function and the function returns 1st,2nd or 3rd based on a time only stamp.

For some reason the part that would return 3rd is no longer working but 1st and 2nd work fine. The data in the Time column is stored as Date/Time data type in the table.

Values like 6:40:22 AM , 6:38:39 AM, 11:02:03 PM are being returned as "UNK"

Here is the query:

SELECT tblCheckedIn.Location, tblCheckedIn.CheckedInBy, tblCheckedIn.OpCheckInName, tblCheckedIn.Date, tblCheckedIn.Time, SHIFTCHECKIN([TIME]) AS SHIFT
FROM tblCheckedIn
ORDER BY tblCheckedIn.Date, tblCheckedIn.Time;

And here is the SHIFTCHECKING function

Function SHIFTCHECKIN(CHECKTIME As Date) As String
    
'    Debug.Print CHECKTIME

Select Case CHECKTIME
    Case "7:00:01 AM" To "3:00:00 PM"
        SHIFTCHECKIN = "1ST"
    Case "3:00:01 PM" To "11:00:00 PM"
        SHIFTCHECKIN = "2ND"
    Case "11:00:01 PM" To "7:00:00 AM"
        SHIFTCHECKIN = "3RD"
    Case Else
        SHIFTCHECKIN = "UNK"

End Select


End Function

I'm stumped. Any ideas?

 

[SkipVought]

This is NOT a contiguous range...

    Case "11:00:01 PM" To "7:00:00 AM"

is like saying case .9 to .1 since the 7AM value is lass than the first value.

rather

    Case "11:00:01 PM" To "11:59:59 PM"

    Case "00:00:00 AM" To "7:00:00 AM"

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[SkipVought]

duh. Just realized you're mixing DATE and STRING. You must convert your STRINGS to TIME...

Select Case CHECKTIME
    Case TimeValue("7:00:01 AM") To TimeValue("3:00:00 PM")
        SHIFTCHECKIN = "1ST"

End Select

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[jadams0173]

Hey Skip

Thanks for both replies. I think your first one is the key. If I change this:

Case "11:00:01 PM" To "7:00:00 AM"
        SHIFTCHECKIN = "3RD"

To this it works:

Case [b]"12:00:01 AM"[/b] To "7:00:00 AM"
        SHIFTCHECKIN = "3RD"

I'm also going to use the TIMEINTERVAL function as you suggested. I tried CDATE before I posted but didn't think of TIMEINTERVAL.

Thank you!

I think I will try 2 case statement for 11:00 PM to 7:00 AM

 

[jadams0173]

Hey Skip,

I changed my function to look like this and it works as expected now.

Function SHIFTCHECKIN(CHECKTIME As Date) As String
    
'    Debug.Print CHECKTIME

Select Case CHECKTIME
    Case TimeValue("7:00:01 AM") To TimeValue("3:00:00 PM")
        SHIFTCHECKIN = "1ST"
    Case TimeValue("3:00:01 PM") To TimeValue("11:00:00 PM")
        SHIFTCHECKIN = "2ND"
    [b]Case TimeValue("11:00:01 PM") To TimeValue("11:59:59 PM")
       SHIFTCHECKIN = "3RD"
    Case TimeValue("12:00:00 AM") To TimeValue("7:00:00 AM")
        SHIFTCHECKIN = "3RD"[/b]
    Case Else
        SHIFTCHECKIN = "UNK"

End Select


End Function

Thank you for the help. Enjoy the pinkie