We help IT Professionals succeed at work.

WeekEnd Date Updater

hello,

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?

Thanks

Josh
Comment
Watch Question

Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Post a few exact scenarios, and the expected output for each...
CERTIFIED EXPERT

Commented:
Do you mean just Sunday or also Saturday?
CERTIFIED EXPERT
Top Expert 2010
Commented:
Should be easy to do...


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

Open in new window

CERTIFIED EXPERT

Commented:
Update mytable set datefield = datefield + iif(weekday(datefield)=1,0, 8-weekday(daefield))
CERTIFIED EXPERT
Top Expert 2010

Commented:
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

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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.