Identifying the week of the month in a Access Query

Of course weeks don't fit neatly into months. August 2015 starts on a Saturday; is August 1 "Week 1" and August 2 part of "Week 2"? August 30 and 31 are a Sunday and Monday - are they Week 6?

What's the context? How will this number be used?

First, Date is a reserved word in Access and shouldn't be used as an object name. 

Second, as John says you need to define what constitutes a week. Is week 1 the first week that starts with a Sunday, or is the week of the first day or what?

I have done something similar ... designed a staff scheduling program for my wife's work.

They operate on a 4-week shift rotation, and used to just keep doing it out by hand on a monthly calendar.

The example in the picture above shows that Jan 1 - 3, 2015 are days in week 3. Sunday Jan 4 is the first day of "Week 4", and so on. 

My Friend Google came up with a  date functions that I use to determine the week number based on picking a starting date. (#6/13/2004# in her case)

Public Function fGetWeek(TheDate As Date) As Integer
fGetWeek = (DateDiff("ww", #6/13/2004#, TheDate) Mod 4) + 1
End Function

'Author: Kurt A. Fisher <*** Email address is removed for privacy ***> 11/6/97
'No copyright asserted. No warranty for fitness of use is
'expressed or implied.

'Acknowledgements:
'Getz. 1997. "Working with Dates". Chapter 2 in VBA Developer 's Handbook. Sybex.
'MicroSoft KnowledgeBase Articles:
' Q88657 Functions for Calculating and Displaying Date/Time Values (8/29/97)

This is just ONE of many very useful Date Functions in the code that I found.

I can post or send the rest if you like.

Try the following expression:

DatePart("ww",[TheDate])-DatePart("ww",DateSerial(Year([TheDate]),Month([TheDate]),1))+1