Calculating Dates

[Mark Hall]

Our reporting cycle goes from Wednesday to Wednesday an I need to
calculate the next Wednesday after the update was received.

If an upate comes in on say a Tuesday (eg 20 Jan 04) then I want a
field on the form to show Wednesdays date (eg 21 Jan 04).

However, if the update comes in on Thursday (eg 22 Jan 04) then the
field would be updated with the date 28 Jan 04.

Any help much appreciated.

[Allen Browne]

Re: Calculating Dates

The next Wednesday is:
=[UpdateDate] - Weekday([UpdateDate]) + 4

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark Hall" <scampitickler@ aol.com> wrote in message
news:79558298.0 402030316.2b2e8 55@posting.goog le.com...[color=blue]
> Our reporting cycle goes from Wednesday to Wednesday an I need to
> calculate the next Wednesday after the update was received.
>
> If an upate comes in on say a Tuesday (eg 20 Jan 04) then I want a
> field on the form to show Wednesdays date (eg 21 Jan 04).
>
> However, if the update comes in on Thursday (eg 22 Jan 04) then the
> field would be updated with the date 28 Jan 04.
>
> Any help much appreciated.[/color]

[Mark Hall]

Re: Calculating Dates

"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message news:<401f891a$ 0$1739$5a62ac22 @freenews.iinet .net.au>...[color=blue]
> The next Wednesday is:
> =[UpdateDate] - Weekday([UpdateDate]) + 4
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Mark Hall" <scampitickler@ aol.com> wrote in message
> news:79558298.0 402030316.2b2e8 55@posting.goog le.com...[color=green]
> > Our reporting cycle goes from Wednesday to Wednesday an I need to
> > calculate the next Wednesday after the update was received.
> >
> > If an upate comes in on say a Tuesday (eg 20 Jan 04) then I want a
> > field on the form to show Wednesdays date (eg 21 Jan 04).
> >
> > However, if the update comes in on Thursday (eg 22 Jan 04) then the
> > field would be updated with the date 28 Jan 04.
> >
> > Any help much appreciated.[/color][/color]


This hasn't worked. It seems to give the nearest Wednesday so on some
occasions it will give me a date that has already elapsed (last
Wednesday) What I need is the next Wednesday for each case.

Does that make sense

[Allen Browne]

Re: Calculating Dates

Perhaps you need:
=[UpdateDate] - Weekday([UpdateDate], 4) + 8

Note that this example is the following Wed, so if UpdateDate is received on
a Wed it gives the following Wed. If that's not what you want, you can
adjust to suit.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark Hall" <scampitickler@ aol.com> wrote in message
news:79558298.0 402040709.543b7 177@posting.goo gle.com...[color=blue]
> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message[/color]
news:<401f891a$ 0$1739$5a62ac22 @freenews.iinet .net.au>...[color=blue][color=green]
> > The next Wednesday is:
> > =[UpdateDate] - Weekday([UpdateDate]) + 4
> >
> >
> > "Mark Hall" <scampitickler@ aol.com> wrote in message
> > news:79558298.0 402030316.2b2e8 55@posting.goog le.com...[color=darkred]
> > > Our reporting cycle goes from Wednesday to Wednesday an I need to
> > > calculate the next Wednesday after the update was received.
> > >
> > > If an upate comes in on say a Tuesday (eg 20 Jan 04) then I want a
> > > field on the form to show Wednesdays date (eg 21 Jan 04).
> > >
> > > However, if the update comes in on Thursday (eg 22 Jan 04) then the
> > > field would be updated with the date 28 Jan 04.
> > >
> > > Any help much appreciated.[/color][/color]
>
>
> This hasn't worked. It seems to give the nearest Wednesday so on some
> occasions it will give me a date that has already elapsed (last
> Wednesday) What I need is the next Wednesday for each case.
>
> Does that make sense[/color]

[Mike Sherrill]

Re: Calculating Dates

On Tue, 3 Feb 2004 19:42:07 +0800, "Allen Browne"
<AllenBrowne@Se eSig.Invalid> wrote:
[color=blue]
>The next Wednesday is:
> =[UpdateDate] - Weekday([UpdateDate]) + 4[/color]

BZZT. But thank you for playing. :)


--
Mike Sherrill
Information Management Systems

[Mike Sherrill]

Re: Calculating Dates

On 3 Feb 2004 03:16:17 -0800, scampitickler@a ol.com (Mark Hall) wrote:
[color=blue]
>Our reporting cycle goes from Wednesday to Wednesday an I need to
>calculate the next Wednesday after the update was received.
>
>If an upate comes in on say a Tuesday (eg 20 Jan 04) then I want a
>field on the form to show Wednesdays date (eg 21 Jan 04).
>
>However, if the update comes in on Thursday (eg 22 Jan 04) then the
>field would be updated with the date 28 Jan 04.[/color]

The important question is what happens if it comes in on Wednesday,
04-Feb-2004? Here's a verbose but self-explanatory function that
returns the date of the next Wednesday, regardless of the user's
regional settings.

Public Function Next_Wednesday( ByVal anyDate As Date) As Date

Dim Days_To_Next_We d As Integer
Select Case (WeekDay(anyDat e, vbSunday))
Case Is = vbWednesday
Days_To_Next_We d = 7 'or 0, app-dependent
Case Is = vbThursday
Days_To_Next_We d = 6
Case Is = vbFriday
Days_To_Next_We d = 5
Case Is = vbSaturday
Days_To_Next_We d = 4
Case Is = vbSunday
Days_To_Next_We d = 3
Case Is = vbMonday
Days_To_Next_We d = 2
Case Is = vbTuesday
Days_To_Next_We d = 1
End Select

Next_Wednesday = anyDate + Days_To_Next_We d

End Function

--
Mike Sherrill
Information Management Systems