Time Intervals in Select Case

[cityboy3]

In Access 2002, I'm trying to use a Select Case statement to assign a time of day to a particular shift (numbered 1,2, or 3). Here's a code snippet:


myEnd is a date/time parameter passed to the function. For now, I only care about the time portion.

Select Case FormatDateTime(myEnd, vbLongTime)
Case #8:00:00 AM# To #2:00:00 PM#
EndPeriod = 1
Case #2:00:00 PM# To #8:00:00 PM#
EndPeriod = 2
Case Is > #8:00:00 PM#, Is < #8:00:00 AM#
EndPeriod = 3
End Select

When I send it a time such as 3:00 AM, it satisfies the second Case (2pm-8pm) and sets EndPeriod to 2. It should be setting EndPeriod to 3. Why would the Select case consider 3AM to be between 2PM and 8PM?

thanks.

 

[DoubleD]

Select Case Hour(myEnd)
Case 8 to 13
EndPeriod = 1
Case 14 To 19
EndPeriod = 2
Case Else
EndPeriod = 3
End Select

This should do it. You could change the CaseElse to:
Case 0 to 7, 20 to 23
EndPeriod = 3


I am what I am based on the decisions I have made.

DoubleD

 

[cityboy3]

thanks. any idea why the "time arithmetic" doesn't work? just curious...

 

[PWise]

try this
Select Case TimeValue(FormatDateTime(myEnd, vbShortTime))
Case #8:00:00 AM# To #1:59:59 PM#
EndPeriod = 1
Case #2:00:00 PM# To #7:59:59 PM#
EndPeriod = 2
Case #8:00:00 PM# To #7:59:59 AM#
EndPeriod = 3
End Select

 

[DoubleD]

VBLongTime is a risky format to use. It is dependent on Regional Settings. If you really wanted to use FormatDateTime, I would suggest VbShortTime. This will put the value in 24 hour format HH:MM. No confusion regarding AM, PM etc.
My guess is that it's a problem with Regional Settings, and/or the value being passed in.

I am what I am based on the decisions I have made.

DoubleD

 

[PWise]

i think that the problem is that FormatDateTime() returns a string try in the immediate window ? typename(FormatDateTime(#2:00pm#,VBshortTime))

 

[DoubleD]

FormatDateTime Function

Description
Returns an expression formatted as a date or time.


Straight from Access Help.

I am what I am based on the decisions I have made.

DoubleD

 

[PWise]

i think anything formatted Returns a string

 

[MichaelRed]

Public Function basShift(myEnd As Date)

    Select Case FormatDateTime(CDbl(myEnd), vbLongTime)
        Case CDbl(#8:00:00 AM#) To CDbl(#2:00:00 PM#)
            basShift = 1
        Case CDbl(#2:00:00 PM#) To CDbl(#8:00:00 PM#)
            basShift = 2
        Case Is > CDbl(#8:00:00 PM#), Is < CDbl(#8:00:00 AM#)
            basShift = 3
    End Select

End Function

Cast /coerce the arg and test values to the type required by the Select case statement.

per the ubiquitous {F1} (aka H E L P) it must be numeric or string. While the INTERNAL representation of date-time values is numeric, this is (aparently) not 'recognized' with / by select case statement.

Of course the 'ammended' routine shown above is NOT the preferred method, as it requires at least two type 'conversions'. The better approach would be to 'generate' the correct values off-line and include hte conceptual values as comments, or possible as Constants.

Using the Latter:

Public Function basShift_II(myEnd As Date)

    Const StrtFrst = 0.333333333333333  'CDbl(#8:00:00 AM#)
    Const StrtScnd = 0.583333333333333  'CDbl(#2:00:00 PM#)
    Const StrtThrd = 0.833333333333333  'CDbl(#8:00:00 PM#)

    Select Case FormatDateTime(CDbl(myEnd))
        Case StrtFrst To StrtScnd
            basShift_II = 1
        Case StrtScnd To StrtThrd
            basShift_II = 2
        Case Is > StrtThrd, Is < StrtFrst
            basShift_II = 3
    End Select

End Function

MichaelRed

 

[VBslammer]

VB is notorious for its quick and easy string-handling abilities. Everyone seems to be stuck on the built-in formats, but you can format the time any way you want to. In military format, it's pretty simple:

Function basShift(ByVal time As Date) As Integer
On Error GoTo ErrHandler

  Dim strTime As String
  
  strTime = Format(time, "hh:mm")
  Debug.Print "Time Arg: " & strTime

  Select Case strTime
    Case "08:00" To "13:59"
        basShift = 1
    Case "14:00" To "19:59"
        basShift = 2
    Case Is >= "20:00", Is <= "07:59"
        basShift = 3
  End Select

ExitHere:
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

VBSlammer

Unemployed in Houston, Texas