Solved

Find the date of this week's Sunday

Posted on 2008-10-16
2
449 Views
Last Modified: 2013-11-27
I am trying to find the date of this week's Sunday.  I know this is the first day of the week but i dont know how to find that date programmitcally.   So for instance.. today is thursday 9/16/2008.  I want this field to show sunday october 9/12/2008 till next week when i would want it to show 9/19/2008
0
Comment
Question by:brandonjel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 22735512
you  can use a function to do that, place this codes in a regular module

Function GetSundayDate(CurrentDate) As Date
   If VarType(CurrentDate) <> 7 Then

        GetSundayDate = Null
   Else

    Select Case Weekday(CurrentDate)
        Case 1
            GetSundayDate = CurrentDate
        Case Else
            GetSundayDate = DateAdd("d", -Weekday(CurrentDate) + 1, CurrentDate)
    End Select
   End If
   
End Function
1
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
ID: 22735755
Hello brandonjel,

No need for a UDF; native functions can get it readily.

SELECT SomeDate, DateValue(Format(SomeDate, "d mmm yyyy")) - Weekday(SomeDate) + 1 AS SundayDate
FROM SomeTable

Regards,

Patrick
1

Featured Post

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

691 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