UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Date() - Weekday() Help, Any Version    
 
   
sneakerhead22
post Oct 3 2017, 01:27 PM
Post#1



Posts: 26
Joined: 18-July 17



Hey guys,

below youll see a portion of my code, criticize it as you please, but I have highlighted the area I am in need of some mathmatic help on.

The highlighted portion is working, and set in stone. But youll see I have a variable called finishedLate, which becomes true if the button is clicked AFTER the second Monday after the week ending date (on a Saturday). So for this week ending 10/7, the report is due by end of day 10/16

Could someone help me process this in my brain cause I am having trouble, how can i get this due date into a variable so that it consistently changes with the new week. If i didn't clarify good enough let me know, I understand to an extent how the Weekday() works, its just the number/logic thats tripping me up currently.



CODE
Public Sub cmdSignSend_Click()
Dim strSql As String
Dim currentSupervisor As String
Dim supervisorTemp As String
Dim strWEDDate As String
Dim finishedLate As Boolean


supervisorTemp = "'" & Me!txtAName.Value & "'"
currentSupervisor = Replace(supervisorTemp, ",", " ")
[b]strWEDDate = "'" & Date - WeekDay(Date, 2) + 6 & "'"[/b]

If MsgBox("By signing this Electronic Signature Acknowledgment Form, I agree that my electronic signature is the legally binding equivalent to my handwritten signature. Whenever I  execute an electronic signature, it has the same validity and meaning as my handwritten  signature. I will not, at any time in the future, repudiate the meaning of my electronic signature  or claim that my electronic signature is not legally binding.", vbYesNo) = vbYes Then
strSql = "INSERT INTO tblPriceOverrideSignatures "
strSql = strSql & "VALUES (" & currentSupervisor & ", 'Wausau', " & strWEDDate & ", True, '" & Date & "', False);"
DoCmd.RunSQL strSql

End If
End Sub
Go to the top of the page
 
BruceM
post Oct 3 2017, 01:52 PM
Post#2


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


I don't understand "AFTER the second Monday after the week ending date (on a Saturday)." My best guess is that something happens from time to time. Because that thing happened a report needs to be completed. That report is due a week from the following Monday, no matter when the thing happened. Is that correct?
Go to the top of the page
 
sneakerhead22
post Oct 3 2017, 01:57 PM
Post#3



Posts: 26
Joined: 18-July 17



Hey bruce,

Almost correct, Every week we have to document certain things agents do, the supervisor enters the comments and then clicks a button called sign and send. This sign and send button send a report. The report is due two MODNAYS from the current weeks SATURDAY.

So this weeks report is due Monday the 16th, if its late (submitted after the 16th) then a LATE field will be marked as TRUE.

CODE
finishedLate = "'" & Date + WeekDay(Date, 2) + 11 & "'"
MsgBox finishedLate


This gives me Monday the 16th, but I am worried that if say tomorrow i hit submit, it will give me Tuesday the 17th..
Go to the top of the page
 
BruceM
post Oct 3 2017, 03:13 PM
Post#4


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


QUOTE
The report is due two MONDAYS from the current weeks SATURDAY.

Is that different from the second Monday from today (or a week from the following Monday, as I put it originally)?

I don't have time to look at this any more today, but you seem to be using an unbound form. Is that correct? If so, I am curious as to why. If not, I'm not sure I understand the point of the INSERT SQL.

In any case, I don't see any reason to store the due date, which can be calculated as needed.
Go to the top of the page
 
theDBguy
post Oct 3 2017, 04:06 PM
Post#5


Access Wiki and Forums Moderator
Posts: 71,230
Joined: 19-June 07
From: SunnySandyEggo


Hi,

You could try something like:

DueDate = DateAdd("d",9,DateAdd("d",7-Weekday(Date),Date))

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 08:00 AM