How to find out the start date of the week

[dibbo]

just to give you a summary of what i'm trying to achieve -
a user selects a date range of 10/10/2004 to 10/11/2004. now the data returned needs to be grouped by 'week', but I don't want to group 10-16, 17-24 etc. I want to find out what day the 10th is e.g Thursday and then group Thurs til Sunday(which would be the 13th), then forward 14-20, 21-27. Basically grouping Mon-Sun, Mon-Sun...

I've looked at DATEPART but not sure how to find out the date of Monday in a week, from a given date.
If i could do that i'd then use:
DateAdd("ww", 1, Date) to increment the weeks.

Hope that makes sense.
TIA

Dave.

 

[dibbo]

sorted it..

While Weekday(givenDate) <> 1 ' While not Sunday
givenDate = DateAdd("d", 1, vRetVal) ' Go forward 1 day
Wend

 

[bjd4jc]

Or you could do:

dateadd("d", -(weekdaygivenDate, vbsunday))+1, givenDate)

 

[BobRodes]

Check out the CDOW function. It returns the day of the week from a date, as a string value.

Bob

 

[BobRodes]

Ooooops....my bad. Not a vb function, sorry. I thought there was one, but I can't find it. You might try using the format function.

format(date, "ddd")

will return "Tue" if today is Tuesday.

Bob