Solved

Find the date of this week's Sunday

Posted on 2008-10-16
2
435 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
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query question 12 32
SQL query and VBA 5 45
SetProperty Foreground Colour 5 14
Upgrading from Office 2013 to Windows 10: API call to 'kernel32' 5 18
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

856 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