423,103 Members | 1,428 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,103 IT Pros & Developers. It's quick & easy.

A date Check/Comparison

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Shift_AfterUpdate()
  2.  
  3.     If Shift = 3 Then
  4.  
  5.         If LineDate = Date And Hour(Now) Then
  6.             LineDate = DateAdd("d", 1, Date)
  7.         Else
  8.             LineDate = Date
  9.         End If
  10.     End If
  11.  
  12. 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.
1 Week Ago #1
Share this Question
Share on Google+
11 Replies


Seth Schrock
Expert 2.5K+
P: 2,886
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.
1 Week Ago #2

P: 81
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.
1 Week Ago #3

twinnyfo
Expert Mod 100+
P: 2,499
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Shift_AfterUpdate()
  2.     If Shift = 3 Then
  3.         LineDate = DateAdd("d", 1, Date)
  4.     Else
  5.         LineDate = Date
  6.     End If
  7. End Sub
Hoep this hepps!
1 Week Ago #4

P: 81
twinnyfo,

No there will be times when it will be after, usually mondays as those are start up nights.
1 Week Ago #5

twinnyfo
Expert Mod 100+
P: 2,499
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).
1 Week Ago #6

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Shift_AfterUpdate()
  2.  
  3.     If Shift = 3 Then
  4.  
  5.         If LineDate = Date And Hour(22) Then
  6.             LineDate = DateAdd("d", 1, Date)
  7.         Else
  8.             LineDate = Date
  9.         End If
  10.     End If
  11.  
  12. 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.
1 Week Ago #7

twinnyfo
Expert Mod 100+
P: 2,499
You need a comparison operator:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Shift_AfterUpdate()
  2.     If Shift = 3 Then
  3.         If Hour(Now)>=22 Then
  4.             LineDate = DateAdd("d", 1, Date)
  5.         Else
  6.             LineDate = Date
  7.         End If
  8.     End If
  9. End Sub
Hope this hepps!
1 Week Ago #8

NeoPa
Expert Mod 15k+
P: 30,909
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.
1 Week Ago #9

zmbd
Expert Mod 5K+
P: 5,202
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.
1 Week Ago #10

NeoPa
Expert Mod 15k+
P: 30,909
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.
1 Week Ago #11

zmbd
Expert Mod 5K+
P: 5,202
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.
1 Week Ago #12

Post your reply

Sign in to post your reply or Sign up for a free account.