time and date VBA

[awake2424]

I am trying to create a code that accounts for Time Received > 12:00 PM and adjusts a Date Received by adding 1 day if it is a Monday thru Thursday and 3 days if it is a Friday.

 Private Sub Test_AfterUpdate()
  
  If Me.Test = "LR" And Me.Time_Received >= #12:00:00 PM# And Me.Time_Received <= #8:00:00 PM# Then
    Me.[Date_Received] = Me.[Date_Received] + 1
    
Else
  If Me.Test = "LR" And Me.Time_Received >= #12:00:00 PM# And Me.Time_Received <= #8:00:00 PM# And Me.Date Received >= 5 Then
    
End If
    
End Sub

Thank you.

 

[vbaInet]

This is the same code from one of your threads isn't it

Where in your code have you tried to incorporate the Monday through Thursday bit? And which field is it checking that against?

 

[awake2424]

It is the same code, but I did not account for the day of the week. So I am hoping just to ad that condition to the code, but having a bit of trouble. I have not incorporated the Monday through Thursday bit because I am not sure how. The Date Received field is the one to check against.

If the Test = "LR" And Date Received = 8/1/2014 (Friday) And Time Received is 10:00 AM Then Date Received is unchanged and the Due Date is 8/7/2014 (4 days later, excluding weekends).

If the Test = "LR" And Date Received = 8/1/2014 (Friday) And Time Received is 1:00 PM Then Date Received is changed to 8/4/2014 and the Due Date is 8/8/2014. I hope this helps and thank you .

 

[vbaInet]

You seem to understand the logic so if I tell you the functions would you be able to give it a go?

Weekday(), vbMonday = 2 and vbThursday = 5 so between 2 and 5. Have a look here:
http://www.techonthenet.com/access/functions/date/weekday.php

 

[awake2424]

So would this maybe do what I need (or close to it):

 Private Sub Test_AfterUpdate()
      If Me.Test = "LR" And Me.Time_Received >= #12:00:00 PM# And Me.Time_Received <= #8:00:00 PM# And
    Me.[Date_Received]= Weekday(), vbMonday = 2 and vbThursday = 5 Then                     
    Me.[Date_Received] = Me.[Date_Received] + 1
              Else
  If Me.Test = "LR" And Me.[Time_Received] >= #12:00:00 PM# And Me.[Time_Received] <= #8:00:00 PM# And Me.[Date 
  Received ]=  Weekday(), vbFriday=6 Then
  Me.[Due_Date] = [Me.Due_Date] + 3
  Me.[Due_Date] = GetBusinessDay([Date_Received], IIf([Test] = "LR", 4, IIf([Test] = "HR", 5, IIf([Test] = "STR", 2, IIf([Test] = "MOLDX", 10, 0)))))
End If
End Sub

Thanks .

 

[vbaInet]

No. Did you read the link I sent you?

 

[awake2424]

Yes, but I am afraid I do not understand how to use it correctly. I was hoping that the code was close. Thanks.

 

[vbaInet]

Not close but in the link it explains how to use the Weekday function and it also gives examples.

 

[awake2424]

Second try:

 Private Sub Test_AfterUpdate()
      If Me.Test = "LR" And Me.Time_Received >= #12:00:00 PM# And Me.Time_Received <= #8:00:00 PM# And
    Me.[Date_Received]= Weekday(2) Or  Weekday(3) Or Weekday(4) Or Weekday(5) Then                     
    Me.[Date_Received] = Me.[Date_Received] + 1
              Else
  If Me.Test = "LR" And Me.[Time_Received] >= #12:00:00 PM# And Me.[Time_Received] <= #8:00:00 PM# And Me.[Date 
  Received ]=  Weekday(6) Then
  Me.[Due_Date] = [Me.Due_Date] + 3
  Me.[Due_Date] = GetBusinessDay([Date_Received], IIf([Test] = "LR", 4, IIf([Test] = "HR", 5, IIf([Test] = "STR", 2, IIf([Test] = "MOLDX", 10, 0)))))
End If
End Sub

Thanks .

 

[vbaInet]

Maybe a third try? Weekday is used to get the weekday of a date. The date needs to be the parameter.