You have a date and you want to calculate what day of the week that day is, and then return the date range (Sun-Sat) that dates falls in-between. the function for doing this is WeekDay(). This function returns a number representing the day of the week for a certain date; 1 means Sunday, 7 means Saturday, and so on. It is an INTEGER value. The following code will work for returning the entire week for a given date:
Dim stDate As Date Dim x As Integer, stBegDate As Date, stEndDate As Date, y As Integer ' stBegDate is the 1st date of the week (Sunday), stEndDate is the last date of the week (Saturday) stDate is the current date; in this example, it's being extracted from a text box named txtDate stDate = Me.txtDate x = WeekDay(stDate) - 1 y = 7 - WeekDay(stDate) stBegDate = stDate - x ' To get the 1st day of the week, you need to back up to day of the week#1 ' if you are 5, back up 4 places, if you are at 6, back up 5 places, etc. ' that is why 1 unit was subtracted from x stEndDate = stDate + y ' For the last day of the week, your goal is to jump to day of the week #7 ' If you are at 5, you need to jump 2 spaces |