Date - calculate the next Wednesday

[DavidCon]

Hi All,

I'm trying to calculate the next Wednesday on the field [due] i.e. today being 21/02/17, the next Wednesday will be 27/12/2017.

I have a working formula in excel which I'm trying to convert to access.

The excel formula is

=IF(WEEKDAY(AE2662)<=4,AE2662+4-WEEKDAY(AE2662),AE2662+11-WEEKDAY(AE2662))

and this is where I am with trying to imitate this in Access

NextWed: IIf(Weekday([due]<=4),[due]+4)-Weekday([due]),[due]+11-weekday[due]))

I'm getting the expression you entered contains invalid syntax or you need to enclose your text with quotes at the false part of the IIF statement but cannot figure out where I am going wrong.

Any help or suggestions would be much appreciated

 

[DavidCon]

Just in case anyone needs this: this works:


NextWed: IIf(Weekday([due]<=4),([due]+4)-Weekday([due]),([due]+11-Weekday([due])))

 

[isladogs]

There is an error in your function - bracket wrongly placed
As a result, when run today (21/12/2017) your function gives 20/12/2017

It should read (note red bracket position)

IIf(Weekday([Due][COLOR="Red"])[/COLOR] <= 4, [Due] + 4 - Weekday([Due]), [Due] + 11 - Weekday([Due]))

This gives the correct answer - 27/12/2017

NOTE: I've also removed unnecessary extra brackets so its easier to read

 

[Gasman]

Just in case anyone needs this: this works:


NextWed: IIf(Weekday([due]<=4),([due]+4)-Weekday([due]),([due]+11-Weekday([due])))

I don't have my code to hand, as it is in work, but I did something like this, although I wanted Mondays so used vbMonday ?, however it appears just changing the startday of the week, gets you the next reuired day which happens to be the startday?

DateAdd("d", 8 - Weekday(due, vbWednesday), due)

HTH

 

[Pat Hartman]

Here's an example with lots of useful date functions.

 

[arnelgp]

you're right Gasman.
in fact it can be extended to
a generic function.
Returns the Next whatever
Weekday you want:

Public Function NextWeekDay(d As Date, intDayOfWeek As VbDayOfWeek) As Date
    NextWeekDay = DateAdd("d", 8 - Weekday(d, intDayOfWeek), d)
End Function

 

[DavidCon]

Thanks all for the input.