date time criteria not working

[boomer1974]

I have an event log and I need to determine which shift a given event occured on or if it was between shifts. Access does not apply the cirteria I am using and produce the correct result. I have tried every way I can think of Following is my code, any help would be greatly appreciated.
*********************start code*************************
Function Set_Shift()
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String
Dim st As Date
Dim tt As Date
Set dbs = CurrentDb

strsql = "SELECT tbl21303.field1 as st, tbl21303.shift FROM tbl21303 "
Set rst = dbs.OpenRecordset(strsql)

rst.MoveFirst
Do While Not rst.EOF
With rst
tt = TimeValue(rst!st)
If tt > #6:30:00 AM# And tt < #4:30:00 PM# Then
rst.Edit
rst(&quot;shift&quot = &quot;days&quot;
rst.Update
ElseIf tt > #4:31:00 PM# And tt < #5:00:00 PM# Then
rst.Edit
rst(&quot;shift&quot = &quot;Between D & N&quot;
rst.Update
ElseIf tt > #6:30:00 PM# And tt < #4:30:00 AM# Then
rst.Edit
rst(&quot;shift&quot = &quot;Nights&quot;
rst.Update
ElseIf tt > #4:30:00 AM# And tt < #5:00:00 AM# Then
rst.Edit
rst(&quot;shift&quot = &quot;Between N & D&quot;
rst.Update
Else
rst.Edit
rst(&quot;shift&quot = &quot;did not work&quot;
rst.Update
End If
rst.MoveNext
End With
Loop
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing
End Function

 

[beetee]

what is the exact behavior? Are only certain records being updated? are all records being updated incorrectly?

In any case, if you are going to write code, debugging skills are a good thing to have. Try setting break points in your code, (left click on the left margin of the module window to set/clear a breakpoint) and using the immediate window to view the values of variables and expressions.

Press ^G to invoke the immediate window.

You can then enter expressions like
print rst!st
or
print TimeValue(rst!st)
or
print (tt > #6:30:00 AM# And tt < #4:30:00 PM#)