Updating DATES

[Cillies]

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

[Rick Brandt]

Re: Updating DATES

"Cillies" <lee@prologic.i e> wrote in message
news:ba262325.0 406240520.5c0c4 0c7@posting.goo gle.com...[color=blue]
> 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[/color]

I'm trying to think of a good reason you would need to do this. Why use a
stored date at all? You could just use an expression that calculates the
current date on the fly couldn't you?

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Salad]

Re: Updating DATES

Cillies wrote:
[color=blue]
> 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[/color]

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(dat e(),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(dat e(),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.