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

G

Grey Shaman

New member
Local time
Today, 06:23
Joined
Aug 26, 2011
Messages
10
Okay. So, first of all, thanks to all of you for the input to my question.
It was never my intention to ignore anyone who is trying to help me, but in the RL, things get complicated sometimes...

I have tried out the different suggestions and yes I was able to get to follow most of the programming logic related to the different answers.

Which in turn brought up another question...
Since the number of days between months changes, depending on which months are used in the calculation, if I use Pat Harman's solution (which uses multiples of seven as the core to calculate, what formula would i use for 4, 5, or 6 months from the date?

I am the one who also posted on utteraccess and yes, both forums were helpful in giving me insight. I wanted to give you guys thanks yesterday, but for some reason, my browser timed out and then I couldn't reconnect until today.
 
G

Grey Shaman

New member
Local time
Today, 06:23
Joined
Aug 26, 2011
Messages
10
To give a better understanding of my issue,
the user has to give a date 3, 4, 5, 6 sometimes 7 months in advance of today's date.
A simple DateAdd didnot work beyond 2 months.... so I started to really look at what was needed.

After all the input for the last 2 days ( and one sleepless night, I was able to realise that I need to understand more about Datediff, DatePart and DateADD
I have done Excel VBA and EOMONTH and EDATE really make things easy there. If I knew enough, I would have been able to translate those functions in to Access VBA.

So this is why I need the help of as many experts as possible.

Again, thanks for all the help so far.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 03:23
Joined
Oct 29, 2018
Messages
21,454
Grey Shaman said:
To give a better understanding of my issue,
the user has to give a date 3, 4, 5, 6 sometimes 7 months in advance of today's date.
A simple DateAdd didnot work beyond 2 months.... so I started to really look at what was needed.

After all the input for the last 2 days ( and one sleepless night, I was able to realise that I need to understand more about Datediff, DatePart and DateADD
I have done Excel VBA and EOMONTH and EDATE really make things easy there. If I knew enough, I would have been able to translate those functions in to Access VBA.

So this is why I need the help of as many experts as possible.

Again, thanks for all the help so far.
Click to expand...
Hi. Just wanted to make sure I understand the requirement... If today is Wednesday, July 31, 2019 and you want to know the 3rd Saturday, 4 months from today, which date would that be: November 16, 2019 or December 21, 2019? Thanks!
 
G

Grey Shaman

New member
Local time
Today, 06:23
Joined
Aug 26, 2011
Messages
10
I got a sample code from Mr. Dale Fye on utter access that gave me more insight for me to be able to to do what I need.

Once I have compiled my adaption, I will post and let you guys give input.
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 06:23
Joined
Jan 23, 2006
Messages
15,379
Seems there is some uncertainty with your requirement.
It also seems your post from yesterday
I forgot to add, YES! this is exactly what i need.
Thank you very much may have been a little premature.

Can you give a couple of explicit examples of what you need? Using some sample data.
 
G

Grey Shaman

New member
Local time
Today, 06:23
Joined
Aug 26, 2011
Messages
10
To theDBGuy,
The example is as follows:
If today is Wednesday, July 31, 2019, it is ALSO the 5th or last Wednesday of July.
The user would need the last Wednesday in 4, 5, or 6 months.


If today was Wednesday, July 24, 2019 it would ALSO be the 4th (but NOT LAST) Wednesday of July.
The user would need the 4th Wednesday in 4, 5, or 6 months.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 03:23
Joined
Oct 29, 2018
Messages
21,454
Grey Shaman said:
To theDBGuy,
The example is as follows:
If today is Wednesday, July 31, 2019, it is ALSO the 5th or last Wednesday of July.
The user would need the last Wednesday in 4, 5, or 6 months.


If today was Wednesday, July 24, 2019 it would ALSO be the 4th (but NOT LAST) Wednesday of July.
The user would need the 4th Wednesday in 4, 5, or 6 months.
Click to expand...
Hi. Thanks for the clarification. So, I guess you're saying whatever "weekday" is the initial date, you would want to find the same "weekday" plus the same week interval (1st, 2nd, etc.) for the future date. Correct?


If so, let me ask the same question again. Today is the 5th or last Wednesday of July. To find 5th or last Wednesday four (4) months from today, would it be November 27, 2019 or December 25, 2019? The reason I ask this is because 4 months from today's date is November 30, 2019. So, if you wanted November 27, 2019, then it would be less than 4 months from the initial date; however, it would be in the same month as the 4th month. Hope it makes sense...
 
G

Grey Shaman

New member
Local time
Today, 06:23
Joined
Aug 26, 2011
Messages
10
Yes... the difficulty here for me is understanding how to compute where it's not a straight forward 4 weeks per month, depending on week day and what day the 1st of the month is in the starting month... at least I think that's how the logic goes, right?
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 06:23
Joined
Jan 23, 2006
Messages
15,379
???As confused as theDBGuy ---Today Jul 31 is the 5th Wednesday of this month.
4 months on is November, but it has only 4 weeks so couldn't be the 5th Wednesday.
I think we need a clear English statement of your requirement.
 
G

Grey Shaman

New member
Local time
Today, 06:23
Joined
Aug 26, 2011
Messages
10
To jdraw,
Yes November has only 4 weeks, but the 4th week is ALSO the last week, the qualifier here is the last week and not the 4th week.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 03:23
Joined
Oct 29, 2018
Messages
21,454
Grey Shaman said:
To jdraw,
Yes November has only 4 weeks, but the 4th week is ALSO the last week, the qualifier here is the last week and not the 4th week.
Click to expand...
Hi. How about my question? The 4th Wed of November is not quite 4 months from today (a few days short). I guess you're saying it doesn't matter? Thanks.
 
Last edited:
C

CJ_London

Super Moderator
Staff member
Local time
Today, 11:23
Joined
Feb 19, 2013
Messages
16,607
I'm going to drop out - I asked these questions for clarification way back and none has been provided that makes sense to me. It's all 'ah yes well in that case its something else'.

A clear definition of the purpose would help understanding. And are we talking about the nth occurrence of that day, the nth day of the nth week? when does a week start? etc. And if the purpose is scheduling - what if that mystical day in n months ahead is a bank holiday or some other day when the schedule would not apply. Some clear examples would have helped. The last example would potentially end up doubling/tripling/quadrupling the events for the the 4th Wednesday/last Wednesday of November - is that an acceptable outcome?

Coupled with cross posting I do not want to waste my time any further guessing at the requirement. Sorry Grey, good luck with your project, but I don't believe I can make an effective contribution without a clear definition of requirement.
 
G

Grey Shaman

New member
Local time
Today, 06:23
Joined
Aug 26, 2011
Messages
10
Okay, here's my tweak, a composite of all the knowledge and help from all of you guys.
I've quoted my sources in code, but it's really thanks to everyone in the post thread(s)

This I made into a module:
Code: 
Public Function nthDay(nDATE As Date, nWoM As Integer, nDoW As Integer) As Variant

'  Appreciation to Dave Fye at UtterAccess.com
'  for supplying me with the base code, which I have tweaked.
'     nDATE: Compiled Date
'     nWoM: Week of the Initial/Starting Month (1, 2, 3, 4 or 5)
'     nDoW: firstdayofweek Constants (vbSunday=1, vbMonday=2, etc.)

    Dim intLoop As Integer
    Dim intWoM As Integer
    Dim dtDOM As Date
    
    For intLoop = 1 To Day(DateSerial(Year(nDATE), Month(nDATE) + 1, 0))
    
        dtDOM = DateSerial(Year(nDATE), Month(nDATE), intLoop)
        If Weekday(dtDOM, 1) = nDoW Then
        
            intWoM = intWoM + 1
            If intWoM = nWoM Then
                nthDay = dtDOM
                Exit Function
            End If
            
        End If
        
    Next
    
End Function

Public Function dtPart(sDate As Date) As Integer

    dtPart = DatePart("w", sDate)
    
End Function

Public Function WkoMnth(sDate As Date) As Integer

'  Acknowledgement to RJD of UtterAccess.com for calculation
'  (the CHOOSE function part is not used)
    WkoMnth = (Int((Day(sDate) - 1) / 7) + 1)
    
End Function

Public Function dstDate(dtType As String, dtSpan As Integer, sDate As Date) As Date

    dstDate = DateAdd(dtType, dtSpan, sDate)
    
End Function
I then placed the following as a command button in the user form:
Code: 
[Com05] = nthDay((dstDate(["m"], [NoM], [gDate])), (WkoMnth([gDate])), (dtPart([gDate])))
where square brackets are used to highlight input and fields on the form.

Again, it was the combined questions and queries of everyone that helped me to get to this point, So, I wish to thank everyone for their help.
:):)
If anyone is willing to suggest any refining, much appreciated.;)
 
You must log in or register to reply here.

Similar threads

Gasman
nth Day of month
Replies
4
Views
673
ebs17
E
E
Solved IIF function and dates
Replies
11
Views
396
eshai
E
L
Solved Problem calculating correct age from birth to death
Replies
11
Views
320
GPGeorge
GPGeorge
S
Date difference Month wise
Replies
7
Views
549
Josef P.
J
conception_native_0123
50's and 60's the whole month of Feb
2
Replies
23
Views
711
Cotswold
Cotswold

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom