WeekEnd Date Updater

Posted on 2012-08-15
Medium Priority
Last Modified: 2012-08-16

Does anybody know of a way to search for all dates in a specific column in an Access 2010 database and if they aren't a week end date (Sunday) then replace the value with the next sunday's date instead?

I'm guessing it will be a hybrid of SQL and VBA to do this?


Question by:bedsingar
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38297156
Post a few exact scenarios, and the expected output for each...
LVL 29

Expert Comment

ID: 38297235
Do you mean just Sunday or also Saturday?
LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 38297239
Should be easy to do...

UPDATE SomeTable
SET SomeColumn = SomeColumn + 7 - Weekday(SomeColumn, 2)
WHERE Weekday(SomeColumn, 2) <> 7

Open in new window

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 77

Expert Comment

ID: 38297241
Update mytable set datefield = datefield + iif(weekday(datefield)=1,0, 8-weekday(daefield))
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38297243
My suggestion above assumes that you take Monday as the start day of your week and Sunday as the last day of the week, and that you want to advance any non-Sunday date to the next Sunday that will occur.

Author Closing Comment

ID: 38299574
Thank you all for your contributions, this solution has achieved my goal :)

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question