WeekEnd Date Updater

Posted on 2012-08-15
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
    Post a few exact scenarios, and the expected output for each...
    LVL 29

    Expert Comment

    Do you mean just Sunday or also Saturday?
    LVL 92

    Accepted Solution

    Should be easy to do...

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

    Open in new window

    LVL 77

    Expert Comment

    Update mytable set datefield = datefield + iif(weekday(datefield)=1,0, 8-weekday(daefield))
    LVL 92

    Expert Comment

    by:Patrick Matthews
    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

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

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now