Set column value to the following Sunday of a certain date

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have a column that contains a date. Another column
needs to always be the following sunday of that date. Is
there a way in code to be able set the date to be the
following sunday.

Thanks,
John
 
Hi John,

Try using the Switch function along with WeekDay and DateAdd functions as
below:

SELECT Orders.OrderDate,
Switch(Weekday([ORDERDATE])=1,DateAdd("d",[ORDERDATE],7),
Weekday([ORDERDATE])=2,DateAdd("d",[ORDERDATE],6),
Weekday([ORDERDATE])=3,DateAdd("d",[ORDERDATE],5),
Weekday([ORDERDATE])=4,DateAdd("d",[ORDERDATE],4),
Weekday([ORDERDATE])=5,DateAdd("d",[ORDERDATE],3),
Weekday([ORDERDATE])=6,DateAdd("d",[ORDERDATE],2),
Weekday([ORDERDATE])=7,DateAdd("d",[ORDERDATE],1)) AS Expr1
FROM Orders;


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights

--------------------
| Content-Class: urn:content-classes:message
| From: "John" <[email protected]>
| Sender: "John" <[email protected]>
| Subject: Set column value to the following Sunday of a certain date
| Date: Sun, 2 May 2004 11:14:32 -0700
| Lines: 7
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcQwcVEzlEIAkO95QryU+tSg1yR42w==
| Newsgroups: microsoft.public.access.queries
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.queries:199311
| NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
| X-Tomcat-NG: microsoft.public.access.queries
|
| I have a column that contains a date. Another column
| needs to always be the following sunday of that date. Is
| there a way in code to be able set the date to be the
| following sunday.
|
| Thanks,
| John
|
 
Assuming the table is called tblDates with field StartDate, try the
following query:
SELECT tblDates.StartDate, DateAdd("d",8-Weekday([Startdate]),[StartDate])
AS Sunday
FROM tblDates;
 
Back
Top