Full Version: If Saturday or Sunday, Jump to Monday
UtterAccess Forums > Microsoft® Access > Access Date + Time
dashiellx2000
I have a db which is supposed to remind users to look at an account a set number of days after a certain action has taken place. I know how to skip weekends entirely, but I don't want to skip every weekend. If I preform an action today, I need to follow up on the account in say 15 calendar days. But if that 15th day ends up being a saturday or sunday, the followup date should be that monday. Does anyone know of a function I can use for this?
Thanks.
freakazeud
Hi,
you could look at the weekday function. It returns a specific result for each day in the week starting with 1 or no argument is Sunday, 2 is Monday, 7 is Saturday...! You could try to incorperate that in a calculation.
HTH
Good luck
dashiellx2000
Thanks Freakazeud. That got going in the correct direction. Here's the code for what I got worked out:

CODE

im FollowupDate As Date
FollowupDate = Date + 30
If WeekDay(FollowupDate) = 7 Then
    FollowupDate = FollowupDate + 2
    Else
    If WeekDay(FollowupDate) = 1 Then
        FollowupDate = FollowupDate + 1
        Else
        FollowupDate = FollowupDate
    End If
End If


No I just need to figure out how to make my own Public Function to use so I don't have to retype this too many times. What I'd really like to do is make it a function where I can tell it the number of days to count would be variable.

Thanks.
Edited by: dashiellx2000 on Thu Jan 5 16:09:32 EST 2006.
ScottGem
CODE
Public Function FollowupDate() As Date
im FollowupDate As Date
Dim intDOW As Integer
FollowupDate = Date() + 30
MsgBox FollowupDate
intDOW = WeekDay(FollowupDate,vbSaturday)
If intDOW < 3 Then
    FollowupDate = FollowupDate + (3 - intDOW2)
End If
End Function

Just add that in a Global Module
dashiellx2000
Thanks Scott.
Can you explain the difference between your intDOW = etc... and what I was doing? Also, how could I make it so in my code I can declare the number of days to count rather then have it hard coded at 30.
Thanks again.
r_cubed
Jumping in on Scott's reply (as he may not be around now) .....
is intDOW part is returning the number via the WeekDay function (which has in itself been redened with a 'StartDay' of Saturday (vbSaturday).
If the value returned via the WeekDay is less than 3 it indicates that the req'd date is for a Saturday or Sunday and therefore adjusts accordingly ... just smarter coding ....
Now, as to NOT having the 30 'hard-coded' m you could amend the function so that an OPTIONAL parameter is passed to it, and therfore altered dynamically ..... so using Scott's code (plus the Optional Parameter), it would look like this:
CODE
Public Function FollowupDate(OPTIONAL intDayAdjuster as Integer = 30) As Date
Dim FollowupDate As Date
Dim intDOW As Integer
FollowupDate = Date() + intDayAdjuster
MsgBox FollowupDate
intDOW = WeekDay(FollowupDate,vbSaturday)
If intDOW < 3 Then
    FollowupDate = FollowupDate + (3 - intDOW2)
End If
End Function

Now, when you 'call' the function from anywhere you can simply pass the required number of days applicable at THAT time ( i.e. Call FollowUpDate(15) ).
If you make the call WITHOUT passing a paremeter value, then it will simply use the default value of 30 (again).
ScottGem
What Rob said. wink.gif (and I am flattered to have Rob call anything I did "smarter coding")
Just expand a little, the idea is to do what you need to in as few lines of code as possible. So we start with the premise that you need to add 1 if the date falls on Sunday and 2 if it falls on Saturday. So the question becomes how can we arrive at those numbers in as few lines of code as possible. Knowing that the Weekday function can can be set to start at any day of the week I figured it would be easier to have Sat and Sun be together instead of at opposite ends of the 1-7 range. By setting Sat as the week start I could then subtract the returned day number from 3 to get the correct number of days to add.
I want have eliminated intDOW and just used the expression in place of it, but I felt it would be easier to type it just once wink.gif
dashiellx2000
Rob, Scott, thank you very much for the explanation. I knew you wouldn't lead me astray Scott, but I want to improve my understanding of VBA coding rather then just go on blind faith.

Odid have to poke the code a little to get it work the way I needed to. Here's the final version:

CODE
Public Function dhFollowupDate(Optional intDayAdjuster As Integer = 30) As Date
Dim intDOW As Integer
dhFollowupDate = Date + intDayAdjuster
intDOW = WeekDay(dhFollowupDate, vbSaturday)
If intDOW < 3 Then
    dhFollowupDate = dhFollowupDate + (3 - intDOW)
End If
End Function


Thanks again for the help.
ScottGem
William,
didn't take any offense at your request. I assumed you were asking just to increase your understanding. Hence my expanded explanation of my thought process.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.