Today is the nth day of the month (1 Viewer)

[Grey Shaman]

Greetings to all!

This is my first post. (Yes I know that I joined a while ago, but most of my questions before were answered by someone or somewhere so I didn't see the need).

Having spent the last 3 days searching, I have to ask:
As a newbie to Access programming (less than 2 years of serious stuff, not just curiosity), is there a way to get the nth day value for the current date?

What I'm asking (in code) is:
Date() = nth Day of the week, nth week of the month.

So for example today would be the 4th Saturday (not LAST, I've seen how to solve that one) of the month.

I want to be able to then make that a variable to calculate say, the 4th Saturday of the month in six months.

Any ideas, please?

 

[isladogs]

Hi and belatedly welcome to AWF.
Also you probably get the prize for taking the longest to make a first post

Fellow AWF member Pat Hartman compiled lots of ueful date functions.
See posts #4 and #5 in this thread https://www.access-programmers.co.uk/forums/showthread.php?t=300480&highlight=Date+functions

Hopefully it will include everything you need

 

[CJ_London]

if the 1st was a saturday, the 4th saturday would be the 22nd

if the 1st was a sunday the 4th saturday would be the 28th

so the 4th saturday has to be between these two dates

you have code to check for the last saturday - if it is greater than the 28th, then it must be the 22nd, so modify accordingly

if day(lastsaturday)>28 then mydate=lastsaturday-7 else mydate=lastsaturday

 

[theDBguy]

Hi. Welcome to the forum! I tried to look in here but didn't find anything that was obvious to me. However, you might want take a look yourself in case you find something that interests you. Good luck!

 

[arnelgp]

'http://www.cpearson.com/excel/datetimevba.htm
Public Function NthDayOfWeek(Y As Integer, m As Integer, _
    n As Integer, DOW As VbDayOfWeek) As Date

    NthDayOfWeek = DateSerial(Y, m, (8 - Weekday(DateSerial(Y, m, 1), _
     (DOW + 1) Mod 8)) + ((n - 1) * 7))

End Function

so you need to get the 4th Saturday 6 month from today:

1. first get the date 6 month from now

dte6monthAhead = dateAdd("m",6,Date)

2. call the function to get the 4th Saturday:

debug.print NthDayOfWeek(Year(dte6monthAhead), Month(dte6monthAhead), _
    4, vbSaturday)

 

[Grey Shaman]

Thanks for the info, but this doesn't actually address the need. In code, Date () returns today's date. What I'm looking for Date()= 3rd Monday of the July (as an example).

 

[Grey Shaman]

Hi. Welcome to the forum! I tried to look in here but didn't find anything that was obvious to me. However, you might want take a look yourself in case you find something that interests you. Good luck!

Lots of good stuff, but not applicable. Most of them are the REVERSE, as in, what date is the nth day of the month?

 

[theDBguy]

Lots of good stuff, but not applicable. Most of them are the REVERSE, as in, what date is the nth day of the month?

Hi. Yes, I didn't think what you were looking for would fit in any of them, but in your original post, you also said this:

I want to be able to then make that a variable to calculate say, the 4th Saturday of the month in six months.

So, maybe that's where the "reverse" method would/could come in? Just a thought...

 

[CJ_London]

I think we are all struggling to understand what it is you actually require

I think what you are saying is

'today is Tuesday 30 July which is the 3rd day of the 5th week of July and I want the 3rd day of the 5th week 6 months hence (Jan 2020) which would be 28th January'

if this is the case, you need to clarify

1. when does a week start? 1st of month? or the Sunday? or Monday? of the week when the 1st occurs. e.g. 1st July is a Monday, 1st August is a Thursday
2. what if x months hence there is not a 5th week? February for example
3. what if x months hence is a bank holiday?

there may be more but the exercise is fruitless without having clear rules about what you are trying to achieve

 

[isladogs]

Gary
Did you actually look at the link I gave in post #2?
That has many date functions supplied by Pat Hartman.
Here is a screenshot of one tab which I think covers what you want

Modify the base date and the weekday and you'll get e.g. the 3rd Monday six months from now

 

[arnelgp]

actually mr.isladog, on the first screenshot, it is saying that the 3rd Monday of july is 16?
my calendar shows its 15.

if you will use the function I provided, it returns the correct date july 15:

' 3rd Monday of july 2019
debug.print debug.print NthDayOfWeek(2019, 7, _
    3, vbMonday)

 

[arnelgp]

Public Function NthDayOfWeek(y As Integer, m As Integer, _
    n As Integer, DOW As VbDayOfWeek) As Date

    NthDayOfWeek = DateSerial(y, m, (8 - Weekday(DateSerial(y, m, 1), _
     (DOW + 1) Mod 8)) + ((n - 1) * 7))

End Function


Public Function TodayIsNthDay(dt As Date) As String
Dim Counter1 As VbDayOfWeek, Counter2 As Integer
Dim resultDate As Date
Dim bolFound As Boolean
For Counter1 = vbSunday To vbSaturday
    For Counter2 = 1 To 5
        resultDate = NthDayOfWeek(Year(dt), month(dt), Counter2, Counter1)
        If resultDate = dt Then
            bolFound = True
            Exit For
        End If
    Next
    If bolFound Then
        Exit For
    End If
Next
TodayIsNthDay = Counter1 & _
               Choose(Counter1, "st", "nd", "rd", "th", "th", "th", "th") & " " & _
               "day of the week, " & _
               Counter2 & _
               Choose(Counter2, "st", "nd", "rd", "th", "th") & " " & _
               Format(resultDate, "dddd") & " of " & _
                Format(resultDate, "mmmm") & ", " & Year(resultDate)
End Function

test:

debug.print TodayIsNthDay(date)

result:

4th day of the week, 5th Wednesday of July, 2019

 

[isladogs]

In fact the first screenshot in Pat's app is showing all the Tuesdays in July 2019 as the text on the form states. So the dates are correct. My comment wasn't as clear as it should have been

 

[Gasman]

Hmm,
My (what appears to be an old version) version shows the correct dates?

As does the latest version I downloaded from that link ?

actually mr.isladog, on the first screenshot, it is saying that the 3rd Monday of july is 16?
my calendar shows its 15.

if you will use the function I provided, it returns the correct date july 15:

' 3rd Monday of july 2019
debug.print debug.print NthDayOfWeek(2019, 7, _
    3, vbMonday)

 

[arnelgp]

mr.gasma, that one is correct.
we are seeking for each Monday's and not the following day.

 

[isladogs]

The new version of Pat's app is also correct. I had selected Tuesday on the combo then altered to Monday and forgot to update it. My fault!

For info, this has been crossposted with answers at https://www.utteraccess.com/forum/index.php?showtopic=2054585&hl=. I noticed that yesterday but couldn't report it as this site was down for several hours.

 

[CJ_London]

we haven't fully answered the question

want to be able to then make that a variable to calculate say, the 4th Saturday of the month in six months.

and what is being calculated is the nth dayofweek which assumes a week starts on the 1st of each month. This may or may not be what the OP wants - from the other forum, it looks like that is the case but it is not how the question is written.

It is unfortunate we are using July 2019 as an example since the 1st was a Monday i.e. the start of a week.

 

[isladogs]

Actually the second screenshot I showed (the one I didn't mess up) did provide an answer for the OP. However judging by the response at UA, I think they are happy with the solution given there. It would however be polite if the OP returned here to confirm whether that is the case

 

[jdraw]

Interesting, similar post on UA yesterday with responses.

 

[isladogs]

Yes. It’s cross posted.


Sent from my iPhone using Tapatalk