Data Function

[Guest]

How can I create a query if I have alot of dates in this format '10/23/07
12:30AM' and I want to seperate it by shift like 1st shift would be between
5:00AM to 1:30PM and just look at the time and date is irelavent. Thanks

 

[fredg]

How can I create a query if I have alot of dates in this format '10/23/07
12:30AM' and I want to seperate it by shift like 1st shift would be between
5:00AM to 1:30PM and just look at the time and date is irelavent. Thanks

Look up the TimeValue function in VBA help.

?TimeValue(Now())
8:47:18 AM

 

[Guest]

I want to the time to always be set so when I run the query it will place 5AM
Shift in a colum where the time rates between 5:00AM to 1:30PM

 

[Guest]

Try a function like this:

Function get_shift(inDate As Date) As Variant
On Error GoTo handler
Dim tTime As Variant


tTime = TimeValue(DatePart("h", inDate) & ":" & DatePart("n", inDate))

Select Case tTime
Case TimeValue("5:00") To TimeValue("13:29"): get_shift = "1"
Case TimeValue("13:30") To TimeValue("19:59"): get_shift = "2"
Case Is > TimeValue("20:00"): get_shift = "3"
Case Else: get_shift = "ERROR"
End Select

Exit Function
handler:
get_shift = "Error: " & Err.Number
End Function