A date Check/Comparison

[DJRhino1175]

I have a code that automatically advances the date 1 day using code, but its not working 100% the way I want it to.

Private Sub Shift_AfterUpdate()
 
    If Shift = 3 Then
 
        If LineDate = Date And Hour(Now) Then
            LineDate = DateAdd("d", 1, Date)
        Else
            LineDate = Date
        End If
    End If
 
End Sub

I only need it to advance if the date is anything before midnight of the previous day. Example - Our third shift starts at 10:30 pm, I have an auto date entry, but it puts the date as Date(), but for our 3rd shift it is technically the next day. So when they select their shift (3) from the drop down, I would like the date to advance 1 day. I think I'm close, I think the (Now) part needs to change.

Thanks for all the help in advance.

[Seth Schrock]

I'm a little confused. First you say you want to advance the date if it is anything before midnight of the PREVIOUS day. Then your example describes the CURRENT day. Please clarify as that will change the calculation.

[DJRhino1175]

Yes my sample code is in correct, as I'm not sure what to put instead of (Now). My 3rd shift starts at 10:30pm, so when they start to enter data the date will be for example 8/3/2018, but the date of their shift should be 8/04/2018. So when the select or enter a 3 in the shift box I need it to advance one day. Currently no matter what time you input the 3 into shift it advances. This is the behavior I need to stop. Hope that cleared things up.

[twinnyfo]

DJ,

Will the 3rd Shift employees ALWAYS be entering data prior to Midnight? If so, then I think your solution is as simple as this:

Private Sub Shift_AfterUpdate()
    If Shift = 3 Then
        LineDate = DateAdd("d", 1, Date)
    Else
        LineDate = Date
    End If
End Sub

Hoep this hepps!

[DJRhino1175]

twinnyfo,

No there will be times when it will be after, usually mondays as those are start up nights.

[twinnyfo]

So, 3rd Shift will always be checking in after 10:30 PM or early in the morning? Then just set your paramaters thusly.

If they are checking in and the Hour() is after 22 then add a Day. If the Hour is Less than 22, then don't add a Day (this, then, would apply to all employees).

[DJRhino1175]

Ok, thank you. Here is what I put in:

Private Sub Shift_AfterUpdate()
 
    If Shift = 3 Then
 
        If LineDate = Date And Hour(22) Then
            LineDate = DateAdd("d", 1, Date)
        Else
            LineDate = Date
        End If
    End If
 
End Sub

I'm unable to test it at the moment as our IT depart doesn't allow us to change the time and date on our PC's.

[twinnyfo]

You need a comparison operator:

Private Sub Shift_AfterUpdate()
    If Shift = 3 Then
        If Hour(Now)>=22 Then
            LineDate = DateAdd("d", 1, Date)
        Else
            LineDate = Date
        End If
    End If
End Sub

Hope this hepps!

[NeoPa]

Hi DJ.

This is so unclear. Not one of your finer questions (You've done better). Why is the time of day at the point when the code's run important for anything?

Certainly Hour() needs a Date/Time parameter, but very rarely does it make sense to tie it to the current time.

How about you stop and explain all the details that need to be taken into consideration for this to work so we can consider the actual problem. I know when things get complicated the first instinct is to treat it like a difficult problem and just blurt out the question quickly. Try to take a step back. Be a little more confident in yourself and us here. Stop and think about what are all the bits of information and how they fit together in the logic.

Often once you've done that you'll have no need to post. When you do though, you can expect a single reply that's bang on. It's so much easier to work with a question that makes solid logical sense.

[zmbd]

I have two shift in our lab and a similar situation- just in reverse as the second shift spans midnight. We need all of the work from second shift after midnight to use prior date until end of shift.

So in OP's example his shift spans Midnight so all of the dates for work generated on 3rd shift such that:
2018-01-01:22h30 thru 2018-02-02:06h30
With the date 2018-02-02

Issue with his code is that at 2018-02-02:00h00 it's advancing the date to 2018-02-03 when OP still requires the date to be 2018-02-02.

As for tying it to the current time - that is entirely dependent on workflow. I pull the Hour(Now()) because I have to know when the sample was logged into the database as this determines which day's paperwork it is recorded (and for the cost-accountants, but that's a whole other level of voodoo!)

I think twinnyfo has the solution - looks similar to what I have at work... I'm home so I can't verify that.

[NeoPa]

Now() is only relevant in any way if you want to make when you logged the item a determining factor. As a general rule that's a very bad idea, though there are scenarios where it can be practical and required.

I just wanted to make sure you aren't shooting yourself in the foot by ensuring it will only ever work correctly if the item is logged at the time it becomes ready. Even if the period where it could be logged were a whole day it still leaves you in a position where retrostective entry of the data would be unreliable - essentially wrong.

As long as that problem is understood and accepted then you should be fine.

[zmbd]

NeoPa - as usual, very good point with the Now() - for my situation, the business rules dictate when the entry is made to the database for OP's that may be entirely different, instead, requiring the date and time the item was completed.