Time Problem

[mansonpj]

I have a problem with a logbook database that i created. We work 7am to 7pm but what i want it to do is that when the clocks turn midnight i want it to revert to the previous days date from midnight till 7am

When the user is inputting the data he selects either dayshift or nightshift and using the after update if nightshift is selected the date automatically goes back to the day before. But this is no use from 7pm till midnight as it is still the same day.

At the moment i use the following.

Private Sub Shift_AfterUpdate()

If Shift = 2 Then (2=Nightshift)
Date = Date - 1
End If

End Sub

Any help would be greatly appreciated.

Paul

 

[WayneRyan]

manson,

I'd suggest staying with Access's Date/Time fields. All of the things that
you mention introducing make things really complicated. Access is so
good at Date/Time arithmetic; why try to do it yourself?

Look in the Help files for DateAdd, DateDiff, and other functions. The only
complexity that you'll have to add later is the logic for the different shifts.
Time worked, etc. should be easy.

Wayne

 

[mansonpj]

Wayne,

I have changed the visual basic to the following

Private Sub Shift_AfterUpdate()

If Time >= #12:00:00 AM# <= #6:59:59 AM# Then
Date = Date - 1
End If

So that instead of using the shift option i am using time the entry was made but when i select a time eg. 20:30 and select nightshift it still deducts the date by a day instead of just doing it between the times asked for.

Any ideas

Paul

 

[jonifen]

Dim tempdate <<< not sure if something more would go here (im a poor coder lol) >>>
If Time() < 0.29 Then
   If shift = "nightshift" Then
      tempdate = date - 1
   ElseIf shift = "dayshift" Then
      timedate = date
   End If
ElseIf Time() > 0.29 Then
timedate = date
End If

perhaps something like that? Not entirely accurate for the time, but thats how I normally sort time out by that decimal (was shown by someone else how to do that ) whack that on afterupdate and see if it works

hth

 

[WayneRyan]

Manson,

Date is reserved for use by Access.

Wayne