Problem Initialising Date and Time variables

[sear100]

I am writing a process for a hospital application in the UK

We have a concept of "Takes" which are periods to which patients coming into hospital are assigned to and thus the consultant (specialist) who looks after them.

They are fixed times between 0800-2000 (Day take) and 2000-0800 (+1) Night take.

I have written the following function to try and determine which take a patient should be assigned to based on the time now to power various queries to enable the end user to quickly see "today's day take" and "yesterday's take"

Testing it I seem to not be able to hit the right "Take times" in my output….was wondering if someone can explain the deficiencies in my logic as… I suspect it's a problem with the way I'm initiating my variables as Today 0800 and Today 2000

Function GetTakeStarttime()

Dim StartTime As Long
Dim EndTime As Long
Dim CurrentTime As Long
Dim YestStartTime As Long
Dim YestEndTime As Long
Dim CurrentTake As String

'Initialise Variables
StartTime = (Date + TimeSerial(8, 0, 0)) 'Today at 0800
EndTime = (Date + TimeSerial(20, 0, 0)) 'Today at 2000
YestStartTime = ((Date - 1) + TimeSerial(8, 0, 0)) '
YestEndTime = ((Date - 1) + TimeSerial(20, 0, 0))
CurrentTime = Now() ' '  This is not fixed can change according to the usage 
CurrentTake = ""

If CurrentTime >= StartTime Then 'if now if after the day take has started '####IF 1
        If CurrentTime <= EndTime Then 'and if it's before day take ends 'IF ####2
            CurrentTake = "Currently Day Take"
            Else 'otherwise
            CurrentTake = "Currently Night Take" 'it's after the day take has ended and therefore the start of the night take
        End If 'IF ####2
    Else 'if it's not after the current day's take has started
        If CurrentTime >= YestStartTime Then 'but it's after the start of the last take (yesterday morning) '###IF 3
            If CurrentTime >= YestEndTime Then '...and it's AFTER the night take making it yesterday's night take '###IF 4
                    CurrentTake = "Yesterday's Night Take"
                Else 'otherwise it's the day take from yesterday
                    CurrentTake = "Yesterday's Day Take"
        End If '###IF 4
        Else 'or...
            CurrentTake = "Before Yesterday's Take!" 'it's before either today or yesterday's take
        End If '##### IF 3
End If '####IF 1

Debug.Print CurrentTake

End Function

 

[MarkK]

A Date/Time value is stored as a decimal with the days to the left of the decimal, and the hours to the right. The variables you've declared, however, are Long Integers, which don't support decimals. So if you do this test . . .

StartTime = (Date + TimeSerial(8, 0, 0)) 'Today at 0800
EndTime = (Date + TimeSerial(20, 0, 0)) 'Today at 2000
MsgBox StartTime = EndTime

. . . the MsgBox will say True, which means that your start time and end time are the same, despite your math with TimeSerial().
To solve this problem, declare your Date variables as Dates

Dim StartTime As [COLOR="DarkRed"]Date[/COLOR]
Dim EndTime As [COLOR="DarkRed"]Date[/COLOR]

 

[sear100]

Sorry for the very delayed reply! That was perfect thank you! So simple... (Duh!)