help with calculating dates.

[jtaltendahl1]

i'm trying to create a query which looks at the field dateEntered and then returns the previous sunday date of that week.

ex.

dateEntered returnedSundayDate

09/26/2006 09/24/2006

are there built in access functions to figure this out in a query?

please help.

 

[ejstefl]

Try this:

=DateEntered - Datepart("w", DateEntered) +1

 

[boblarson]

Here's a function you can use:

Public Function PrevSunday(dteDate As Date) As Date
    Dim strDay As String
    Dim intInterval As Integer
    strDay = Format(dteDate, "dddd")
    
    Select Case strDay
        Case "Sunday"
            intInterval = -7
        Case "Monday"
            intInterval = -1
        Case "Tuesday"
            intInterval = -2
        Case "Wednesday"
            intInterval = -3
        Case "Thursday"
            intInterval = -4
        Case "Friday"
            intInterval = -5
        Case "Saturday"
            intInterval = -6
    End Select

         PrevSunday = DateAdd("d", intInterval, dteDate)
    
End Function

<<I shortened from my original example>>