424,847 Members | 3,094 Online
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

A date Check/Comparison

 100+ P: 120 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 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. Aug 3 '18 #1
11 Replies

 Expert 2.5K+ P: 2,932 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. Aug 3 '18 #2

 100+ P: 120 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. Aug 3 '18 #3

 Expert Mod 2.5K+ P: 3,158 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 Private Sub Shift_AfterUpdate()     If Shift = 3 Then         LineDate = DateAdd("d", 1, Date)     Else         LineDate = Date     End If End Sub Hoep this hepps! Aug 3 '18 #4

 100+ P: 120 twinnyfo, No there will be times when it will be after, usually mondays as those are start up nights. Aug 3 '18 #5

 Expert Mod 2.5K+ P: 3,158 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). Aug 3 '18 #6

 100+ P: 120 Ok, thank you. Here is what I put in: Expand|Select|Wrap|Line Numbers 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. Aug 3 '18 #7

 Expert Mod 2.5K+ P: 3,158 You need a comparison operator: Expand|Select|Wrap|Line Numbers 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! Aug 3 '18 #8

 Expert Mod 15k+ P: 31,307 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. Aug 3 '18 #9

 Expert Mod 5K+ P: 5,331 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. Aug 3 '18 #10

 Expert Mod 15k+ P: 31,307 `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. Aug 4 '18 #11

 Expert Mod 5K+ P: 5,331 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. Aug 4 '18 #12