Using a time in a Select Case statement

  • Thread starter Thread starter EAB1977
  • Start date Start date
E

EAB1977

Hi everyone,

I want to know if the below code will work. I am trying to compare
times to get a shift value in a text box.


Select Case Format(Me.datTime.Value, "Medium Time")

Case Format(rst!Shift1StartTime, "Medium Time") To Format(rst!
Shift1EndTime, "Medium Time")
Me.numShift.Value = 1

Case Format(rst!Shift2StartTime, "Medium Time") To Format(rst!
Shift2EndTime, "Medium Time")
Me.numShift.Value = 2

Case Format(rst!Shift3StartTime, "Medium Time") To Format(rst!
Shift3EndTime, "Medium Time")
Me.numShift.Value = 3

Case Else
Me.numShift.Value = "Unknown"

End Select

What am I doing wrong?
 
hi,
I want to know if the below code will work. I am trying to compare
times to get a shift value in a text box.
What am I doing wrong?
Click to expand...
Try this:

Dim v1, v2, v3

v1 = Now - 1
v2 = Now
v3 = Now + 1

Select Case CDate(v2)
Case CDate(v1) To CDate(v3)
MsgBox "yeah!"
Case Else
MsgBox "oh no..."
End Select

btw, your table in rst seems not to be normalized. Think about this
structure:

Table TimeShift(ID, StartTime, EndTime)

Dim yourDate As String
yourDate = Format(yourDateTime, "\#m\/d\/yyyy hh\:nn\:ss\#")
DLookup("ID", "TimeShift", yourDate & " BETWEEN StartTime AND EndTime" )

mfG
--> stefan <--
 
Try something more like the following.

Select Case Me.datTime
Case rst!Shift1StartTime To rst!Shift1EndTime
Me.numShift.Value = 1

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Try something more like the following.

Select Case Me.datTime
Case rst!Shift1StartTime To rst!Shift1EndTime
Me.numShift.Value = 1

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
Click to expand...

Hi John,

I did try to use just the Case rst!Shift1StartTime To rst!
Shift1EndTime, but the record has the date and time in the cells and I
only want to retrieve the time to base this against, so that's why I
used the Format function with the Medium time parameter. Basically the
table is set up with the times of the 1st, 2nd, and 3rd shifts with
entries like 1/1/1900 (for midnight) and the end date would be
1/1/1900 8:00:00 A.M. for the 1st shift and the like for the rest.

Anyway, I still can't get this to work correctly. Am I going to just
have to declare each value?

Eric
 
Use the timeValue function to strip off the date

Select Case TimeValue(Me.datTime)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top