How do I find next Wednesday falls in a new month?

[rushdib]

Hello,
I am looking for a logic to find whether the next Wednesday falls in a new month or remains in current month.
(ex. if today is 30th of November 2002, the next Wednesday is 4th of December 2002 - new month. If today is 22nd of November 2002, the next Wednesday is 27th of November 2002 - current month). Can someone please help me?

Thanks in advance,

Rushdi

 

[TLady]

OK, let's try to find Wednesday first

Function InitalizeReportParameters(DateIn As Date) As Date

If IsMissing(DateIn) Or IsEmpty(DateIn) Or IsNull(DateIn) Then DateIn = Date

MostRecentWednesday = DateIn - WeekDay(DateIn, Thursday) 'You've got yourself Most Recent Wednesday
' Comments : Returns the week ending date (Wednesday) of the supplied date
' Parameters: datDateIn - date to check the week's Wednesday ' Returns : week's Wednesday '

End Function

SO RESULT WILL BE - 10/16/2002

Get variables from DateIN as
SuppliedDate = Month(MostRecentWednesday ) 'You'll get 10 -month you comparing to
ActualDate = Month(Date) 'You'll get Actual Month number

Then go if SuppliedDate = ActualDate then MsgBox "This Is Wednesday of this Month"
Else MsgBox "This is some OTHER Wednesday"

'This DateIn function is very poerfull and can be used to determine any date

 

[TLady]

Sorry, my bad MostRecentWednesday = DateIn - WeekDay(DateIn, VBThursday)

 

[crpjaviman]

I don't want to sound stupid, but wouldn't it be easier to just add 7 days to the starting Wednesday and do a Format function to find out the month that it is in?

For example:

Dim startw, endingw As Date
Dim wmonth As String

startw = Me![StartWeds]
endingw = startw + 7
wmonth = Format(endingw, "mmmm&quot

Me![EndWeds] = endingw
Me![Month] = wmonth

I know that this is very simple. I also think that it basically does the job for the info that was provided.

Oh, by the way, the simple as it looks, it also works on a form with 3 textboxes.

Thanks,
crpjaviman

 

[MichaelRed]

Hmmmmmmmmmmmm,

I made some alterations to TLady's submission to suit my understanding of the question. I read the request as a SIMPLE (boolean):

Is the NEXT Wednesday in the Same MONTH as the (Wednesday) Date Supplied?

I (obviously) changed (notably SHORENED) the variable names and added a couple. Primarily because while I do not enjoy typing, I do use "Option Explicit", and thus NEED the declarations.

I also incorporated the entire process into the procedure, prefering to NOT rely on the User (or other programmer) for the calculation(s) beyond the "End Function" statement in the post.

Of course there needs to be SOME checking that the date supplied IS actually a "Wednesday" for the inquiry to make any sense. This, in turn implies an actual date be available to the routine. TLady's attempt to check this fails, as the arg is typed as DATE, so will ALWAYS have a value (even if it is ZERO), so the FIRST change is to cast the input arg as a variant - which can actually be missing I went a step further, and changed the check to simply require the arg to be a date.

This, of course, raises the issue of what should be done when / if the arg is NOT a wednesday or ANY valid date. I simply followed TLady's lead and - for no date used the current date, and for dates which are not Wednesdays, used the precvious Wednesday. All of which ammount to quite nicely avoiding error checking and the need to construct an (elaborate) schema for the return value.

The remainder of the changes generally are the adaptations to incorporate all of the logic / computations within the procedure - and to simply return the status (boolean true false) to the calling process.

The following is (as ALWAYS) just a snippet, lacking error checking and even useful commentary, but it does return the boolean (flag) operator which I read as the expectation.

Function basWedInMo(Optional DateIn As Variant) As Boolean

    'Michael Red    10/19/02    Tek-Tips thread181-383626
    'Adapted from TLady Submission

    'Sample Usage
    '? basWedInMo
    'True

    '? basWedInMo(#10/28/02#)
    'True

    '? basWedInMo(#10/30/2002#)
    'False

    Dim LastWed As Date
    Dim NxtWed As Date
    Dim MoSuplWed As Integer
    Dim MoNxtWed As Integer

    If (Not IsDate(DateIn)) Then
        DateIn = Date
    End If

    'Most Recent Wednesday
    If (Weekday(DateIn) <> vbWednesday) Then
        LastWed = DateAdd(&quot;d&quot;, (vbWednesday - Weekday(DateIn) - 7), DateIn)
     Else
        LastWed = DateIn
    End If
    NxtWed = DateAdd(&quot;ww&quot;, 1, LastWed)

    'Get variables from DateIN as
    MoSuplWed = Month(LastWed)      'Compareable month #
    MoNxtWed = Month(NxtWed)        'Actual Month number

    basWedInMo = (MoSuplWed = MoNxtWed)

End Function

crpjaviman,

Your 'procedure' looks simple, in part because it is incomplete. As a formal procedure, it lacks some nicieties (declaration and end statements), and a complete (missing type) declaration for startw. It also relies on being in a form module, thus avoiding the declaration of some variables. And, as noted above, it does NOT check the given date is a Wednesday, or make any adjustment if it is not.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[raskew]

You might try this from the debug window:

mydte = #11/22/02#
? iif(month(NextNDay(mydte,vbWednesday))= month(mydte), True, False)
True

mydte = #11/30/02#
? iif(month(NextNDay(mydte,vbWednesday))= month(mydte), True, False)
False

…with function NextNDay() being

Function NextNDay(ByVal pDay As Date, wday As Integer)
NextNDay = [pDay] - WeekDay([pDay]) + wday + IIf(WeekDay([pDay]) >= wday, 7, 0)
End Function

 

[TLady]

Red,
great alteration job !
Much better then I did
Experience is everything ... (sigh)

 

[raskew]

Michael-

Your solution is absolutely simplistic!
What if 'day' is something other 'Wednesday'?
Please post your generic solution!

Bob

 

[MichaelRed]

raskew?

Which / What "Generalization" ? I do not recall (or find in a brief review of the thread) any reference to 'genric soloution'. The thread request is (as I read it) quite specific and a brief review notes that respondents to date have followed the requests. My procedure is simply an adaptation of what TLady had already posted -with the primary goal of encapeulating the process in a single procedure-, not an attempt to provide an original procedure or improve (e.g. generalize) the soloution. I am not at all sure that there is much point to generalizing this, and not completly sure that I understand what real use there would be to such a generalization.

If I understand WHAT you are asking, the change would be somewhat trivial, as one could simply intrduce another arg for the day of hte week and replace vbWednesday with the new arg. This, however, would (logically) imply and require revising the names of a number of variables and the procedure name (chage "Wensday" to 'WeekDay' throughout), or, for the more common (expected) use, simply change vbWednesday to what ever day of the week is of interest. Of course, this last is based on my guess as to the intended use, which is a business which bases its' "Month" on the last Wednesday (a MONTH ends on the Last Wed of the calendar, and the next month starts on the next day (Thursday) regardless of the month that day falls in). So, for SOME business, changing the VBWednesday to their ending business day is sufficient although -to be consistient and logical- the references to 'Wednesday' would also need to be changed to suit.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[raskew]

Michael-

Is your therapist on vacation this week? If so,
consider calling 911. Sounds like you're
about to implode!

Bob