update date query by working week

[Cillies]

Hi,

I have created a simple update query, that update the date everyday. i.e. any datebefore 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). 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

 

[scriverb]

Here is an updated query that should work for you when run each day:

UPDATE [i][red]yourtablename[/red][/i] as A SET A.[[i][red]yourdatefield[/red][/i]] = DateAdd("w",7,Date())
WHERE A.[[i][red]yourdatefield[/red][/i]]<Date();

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Cillies]

Thanks for the quick reply, but that code only works over seven days irrespective of weekends.

You see what I mean is, I need the update to work without including weekends (Sat & Sunday) so in effect the update would really need to be based on a calander. So when it is updating it will take into account that a particular day maybe a saturday and thus not include that day in the count, so over the couse of seven days begining wednesday it would count as follows:

wednesday, thurs, fri, mon, tues, wed,thur.

?? would I need to build in a calander into the database for this to be possible.

kindest regards

 

[scriverb]

Give me an example of a date Mon - Sun where a 7 day add of days without worrying about the weekends would given you a 5 working day addition.

Example: Monday start date + 5 working days is Monday
This example can also be reached by adding 7 total days.

This holds true with all days of the week except if the start date is the weekend where you don't want to land on a weekend date. But, logic has it that we wouldn't have a start date of a weekend, correct?

Please clear this up and I can modify the code if necessary but I just need to see the logic that you want.


[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Cillies]

Sorry it took so long for me to get back as have been out on site for the past while.

What I need is to have seven full working days and not 5.

So in effect if the DB was being updated today any records of anydate that fall before today's date would be updated to seven full working days time. If I add seven days to todays date I would only be getting five full working days.

 

[OhioSteve]

Your update query is doing a calculation and storing the result. One of the most important rules in DB design is NOT to store calculated values. If possible, you should avoid breaking that rule.

But if you must do this, then add nine to the date. That will return a date seven working days from today. I am assuming that you will disregard holidays.

 

[OhioSteve]

PS:
This is how to avoid doing the update query, and incorporate holidays.

Create a lookup table called seven. It will have two fields, "a" and "b". Both will be date/time fields. Each value in "b" will be seven working days after the value in "a". You can initially populate it using Excel. Then go back and adjust the values to account for holidays.

Now when you want a value seven working days after a certain date, you can use the lookup table.

 

[scriverb]

OhioSteve: Adding 9 days in most cases will give you 7 working days but given a Thursday or Friday start date adding 9 will give you a new Saturday or Sunday date. This is not good as it is not a working day. I assume from his postings that he want to jump that Saturday or Sunday finish date to the next Monday.

Something like the following I believe will give the correct date:

UPDATE yourtablename as A SET A.[[red][i]yourdatefield[/i][/red]] = Switch(DatePart("d",Date())=5,DateAdd("d",11,Date()), DatePart("d",Date())=6,DateAdd("d",11,Date()), True, DateAdd("d",9,Date())) 
WHERE A.[[red][i]yourdatefield[/i][/red]]<Date();

I think this will work as requested.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

I used the wrong parameter in the above posting when referring to the Datepart function. Meant to use "w" for day of week.

UPDATE yourtablename as A SET A.[yourdatefield] = Switch(DatePart("[b][red]w[/red][/b]",Date())=5,DateAdd("d",11,Date()), DatePart("[b][red]w[/red][/b]",Date())=6,DateAdd("d",11,Date()), True, DateAdd("d",9,Date()))
WHERE A.[yourdatefield]<Date();

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[OhioSteve]

Scriverb,


Well, I may have made a mistake. Let me do some thinking aloud. If its Thursday at midnight, and we start counting, two working days will pass and then the weekend will start. After the weekend, Monday, Tuesday, Wednesday, Thursday, and Friday will pass. Now it is midnight on Saturday morning, and nine days have passed, and seven of them were working days. So the nine day system will work if you want to know when seven days have passed. However, it may yield a weekend date. If the programmer does not want any weekend dates, then yes, he needs a more complex function. It should be something like~ newDate:iif(weekday([myDate])=5,[myDate]+11,iif(weekday([myDate])=6,[myDate]+10,[myDate]+9)

However, I still feel that the best solution is a lookup table. That way, it is much easier to cope with holidays, and you can incorporate the table into your relational structure. You could make the table using Excel. Just do this

1)Enter the first three sequential dates in A1:A3, and drag down to generate the other dates.
2)Use a function to populate the second column. The function should be like the iif above, but using commands excel understands.
3)Use edit/paste special/values to paste the values into columns C and D.
4)Delete columns A and B.
5) Change the holidays manually.
6) Save the spreadsheet.
7) Use the clipboard to bring the table into Access.

I think we both agree that the use of update queries should be minimized.

 

[PHV]

Take a look at this formula:
SET theDate = theDate + 9 - 2 * (Format(theDate + 9, "w", 2) > 5)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mygmat123]

I would just you VBA with an If statement. But you can also use the autoscheduler, and schedule it for week days.
For example.

dim mydate as date

mydate=date()

If format(mydate,"ddd")="Sun" or Format(mydate,"ddd"="Sat" then
msgbox("weekend or Holiday")
else
'run code
end if

But then you still have those holidays. We can make a table full of ONLY holidays and use a dlookup for comparing.

If mydate=dlookup("datefieldname", "table","datefieldname="& mydate") then
msgbox("Holiday")
else
'runcode
end if