Cillies wrote:
Hi,
I have created a simple update query, that updates the date everyday.
i.e. any date before today's date will be updated to todays date. I
have it running on an autoexec. macro
But I want to be able to update it every 7 days, but at the same time
take into consideration the weekends. I just want to use the 5 working
days of the week (Mon-Fri)and not the weekends (Sat, Sun). for
example, if today is Tuesday and I have 37 date entries for monday I
want to update those 37 entries to the following wednesday (7 working
days later).
Hope someone can help
Look at the weekday() function in help. Today is Thursday, June 24.
? date()
6/24/2004
? weekday(date(),vbSunday)
5
? weekday(date(),vbMonday)
4
The default for WeekDay is vbSunday so you need to determine which day
is going to be the starting day in your calculations. If today was
Monday and you used VbMonday the number would be 1.
You know that any weekday (Mon-Thu) you would add 8 days. If today's
date is Friday, I you'd add 10. So you could do something like
Expr1 : dateAdd("d", IIF(Weekday(date(),vbMonday) _
< 5 ,8,10),Date())
This says, if the weekday is less than Friday (using Monday as starting
point), add 8 days else add 10 days.
Caveat: If this is an autoexec macro, and you open this database on the
weekend, it will fire. So you may want to add another IIF() to see if
it is Friday and add 10 days otherwise add 0.
Expr1 : dateAdd("d", IIF(Weekday(date(),vbMonday) _
< 5 ,8,IIF(Weekday(date(),vbMonday) = 5,10,0)),Date())
or something like that. Next, you may need to make some adjustments for
holidays and you are on your own on that.
