Use Cdate and write yesterday's date?

[Elin]

Hi!
Background: We have shifts working from 23h00 to 06h00. However I would like to add a column with the Starting date of the shift, to be able to sort on that in reports.

SID - Shift ID


StartDatum = start of shift
DatumTid = time stamp of record

 
    Dim szTime As String
 
    szTime = Time
 
            If (MyRs![SID] = 3 Or MyRs![SID] = 5 Or MyRs![SID] = 6) _
                And (TimeValue(szTime) > "00:00:00" Or TimeValue(szTime) < "17:00:00") Then
                
                MyRs![StartDatum] = CDate(MyRs![DatumTid]) - 1
            ElseIf (MyRs![SID] <> 3 Or MyRs![SID] <> 5 Or MyRs![SID] <> 6) Then
                MyRs![StartDatum] = CDate(MyRs![DatumTid])
                
            End If

Why does it not work? What to do to make it work?
I always get StartDatum = DatumTid

Thanks for help

[ranman256]

I think
(MyRs![SID] <> 3 Or MyRs![SID] <> 5 Or MyRs![SID] <> 6) Then

needs to be AND...
(MyRs![SID] <> 3 AND MyRs![SID] <> 5 AND MyRs![SID] <> 6) Then

otherwise the logic allows it to fail.

[Missinglinq]

First off, the Time() Function returns a Date value, not a String. So

Dim szTime As String

needs to be

Dim szTime As Date

When denoting explicit DateTime values, you have to use the Octothorp (aka pound sign) as a delimiter.

Next, to test if the time is between Midnight and 5 PM (I assume this is what you're trying to do) you have to use And rather than Or. So for this part of your code

Dim szTime As Date

szTime = Time

 If TimeValue(szTime) > #12:00:00 AM# And TimeValue(szTime) < #5:00:00 PM# Then

  'Code if time is between 12 MN and 5 pm

 Else

  'Code if time is Not between 12 MN and 5 pm

 End If

Lastly, is this all of your code or have you omitted some of it? With

MyRs![SID]

you appear to be referring to a RecordSet (MyRs) but you don't show where you're setting MyRs as such.

Linq ;0)>

[Elin]

Thank you! I also see why I should change also "00:00:00" Or TimeValue(.. to 00:00:00" And TimeValue !
Howevers it's something more to it as I still get Datumtid=StartDatum

I think
(MyRs![SID] <> 3 Or MyRs![SID] <> 5 Or MyRs![SID] <> 6) Then

needs to be AND...
(MyRs![SID] <> 3 AND MyRs![SID] <> 5 AND MyRs![SID] <> 6) Then

otherwise the logic allows it to fail.

[Elin]

Thank you!
Yes I omitted quite a lot of code

I'm going to change szTime toAs Date and put it to the test tonight!
(And I realized the And bit in testing time! )