By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,516 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,516 IT Pros & Developers. It's quick & easy.

Updating DATES

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Cillies" <le*@prologic.ie> wrote in message
news:ba**************************@posting.google.c om...
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


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
Nov 13 '05 #2

P: n/a
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.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.