asked on

Calculate to Next Sunday

I need to put a label in my access application that simply shows me the date for the following Sunday. For example if today is Friday 3rd then it would show that the next date of the next sunday is 5th. But when we get to Monday 6th I need that label to automatically show the following sunday which would be the 12th and so fourth. Basically I have a rent charging application and the charges are calculated weekly on a sunday, and I need the label to show the next charge date

Any help would be greatly appreciated.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ihotdesk

ASKER

sorry to be a pain but I am new at Access and have basically inherited this application do I have to set the first line the datepart as a variable in the form before I can use the second line to display the info I need in a text box

SOLUTION

p912s

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ihotdesk

ASKER

thanks guys thats exactly what I was looking for :)

p912s

You're welcome...

bobHacker

Hi,

This is a simple function I did one to get Saturday as a weekending date

Public Function getWeekEndingDate() As Date
Dim dayOfWeek As Integer
Dim dteWeekEndingDate As Date

dayOfWeek = Weekday(Date)

'test
'dayOfWeek = 7

Select Case dayOfWeek
Case 1
dteWeekEndingDate = DateAdd("d", 6, Date)
Case 2
dteWeekEndingDate = DateAdd("d", 5, Date)
Case 3
dteWeekEndingDate = DateAdd("d", 4, Date)
Case 4
dteWeekEndingDate = DateAdd("d", 3, Date)
Case 5
dteWeekEndingDate = DateAdd("d", 2, Date)
Case 6
dteWeekEndingDate = DateAdd("d", 1, Date)
Case 7
dteWeekEndingDate = Date
Case Else
'error message... or
'dteWeekEndingDate = Date
End Select

'return
getWeekEndingDate = dteWeekEndingDate

End Function