Week of the month

I'm sure this has been asked before but I can't find any postings.

I have a table that has weekly inspections for multiple buildings. What
I need to do is break these down by the week of the month of my
choosing. Let me explain; I can get my week numbers no problem. I can
get the date from the week numbers no problem. What I haven't been able
to do is put things together and make a simple function that will allow
my users to select from a listbox the month and a query breaks the
output down into each week of that month. Example is we just finished
Oct, so I want my user to open a form and select the month of October,
2006 and submit with a button. Then the query will run getting all
weeks in that month off the table and outputting them to a form or
report that will display by date each week broken down for that month.
The first day of our week always starts on Monday no matter if there is
a day from the last month on monday, such as November started on Wed of
this year. Our weeks start on Monday and end on Sunday. Hope this is
enough information to go by. It seems simple, but it has me stumped.

Thanks in advance
Jim

try the following query, as

SELECT MyTable.theDate, DatePart("ww",[theDate],2) AS theWeek
FROM MyTable
WHERE Month([theDate]-DatePart("w",[theDate],2)+1)=10 AND
Year([theDate]-DatePart("w",[theDate],2)+1)=2006;

replace "MyTable" with the correct name of the table, and "theDate" with the
correct name of the date field in the table, of course. the hard-coded
criteria "10" and "2006" for month and year, will return dates between
10/2/2006 and 11/5/2006. you can replace the criteria with references to
controls on a form, so your user can choose month and year to suit.

hth

"Jim" <jmorriz@Comcast.netwrote in message
news:1162665612.368374.32660@h48g2000cwc.googlegro ups.com...

Quote:

I'm sure this has been asked before but I can't find any postings.
>
I have a table that has weekly inspections for multiple buildings. What
I need to do is break these down by the week of the month of my
choosing. Let me explain; I can get my week numbers no problem. I can
get the date from the week numbers no problem. What I haven't been able
to do is put things together and make a simple function that will allow
my users to select from a listbox the month and a query breaks the
output down into each week of that month. Example is we just finished
Oct, so I want my user to open a form and select the month of October,
2006 and submit with a button. Then the query will run getting all
weeks in that month off the table and outputting them to a form or
report that will display by date each week broken down for that month.
The first day of our week always starts on Monday no matter if there is
a day from the last month on monday, such as November started on Wed of
this year. Our weeks start on Monday and end on Sunday. Hope this is
enough information to go by. It seems simple, but it has me stumped.
>
Thanks in advance
Jim
>

Jim wrote:

Quote:

I'm sure this has been asked before but I can't find any postings.
>
I have a table that has weekly inspections for multiple buildings. What
I need to do is break these down by the week of the month of my
choosing. Let me explain; I can get my week numbers no problem. I can
get the date from the week numbers no problem. What I haven't been able
to do is put things together and make a simple function that will allow
my users to select from a listbox the month and a query breaks the
output down into each week of that month. Example is we just finished
Oct, so I want my user to open a form and select the month of October,
2006 and submit with a button. Then the query will run getting all
weeks in that month off the table and outputting them to a form or
report that will display by date each week broken down for that month.
The first day of our week always starts on Monday no matter if there is
a day from the last month on monday, such as November started on Wed of
this year. Our weeks start on Monday and end on Sunday. Hope this is
enough information to go by. It seems simple, but it has me stumped.
>
Thanks in advance
Jim

I addressed something like that a couple of times. See:

http://groups.google.com/group/micro...6e69eb76a63f76

James A. Fortune
CDMAPoster@FortuneJames.com

Thank you for your reply it worked very well. I still have to seperate
these records so I can display them on a report or form. Hopefully I'm
on the right track saying I'll build another query from this one that
will seperate each week's values. Otherwise I'll use another function
on the form or report itself to pull the individual building scores.
(Dlookup or Select)

Thank you again
Jim

tina wrote:

Quote:

try the following query, as
>
SELECT MyTable.theDate, DatePart("ww",[theDate],2) AS theWeek
FROM MyTable
WHERE Month([theDate]-DatePart("w",[theDate],2)+1)=10 AND
Year([theDate]-DatePart("w",[theDate],2)+1)=2006;
>
replace "MyTable" with the correct name of the table, and "theDate" with the
correct name of the date field in the table, of course. the hard-coded
criteria "10" and "2006" for month and year, will return dates between
10/2/2006 and 11/5/2006. you can replace the criteria with references to
controls on a form, so your user can choose month and year to suit.
>
hth
>
>
"Jim" <jmorriz@Comcast.netwrote in message
news:1162665612.368374.32660@h48g2000cwc.googlegro ups.com...

Quote:

I'm sure this has been asked before but I can't find any postings.

I have a table that has weekly inspections for multiple buildings. What
I need to do is break these down by the week of the month of my
choosing. Let me explain; I can get my week numbers no problem. I can
get the date from the week numbers no problem. What I haven't been able
to do is put things together and make a simple function that will allow
my users to select from a listbox the month and a query breaks the
output down into each week of that month. Example is we just finished
Oct, so I want my user to open a form and select the month of October,
2006 and submit with a button. Then the query will run getting all
weeks in that month off the table and outputting them to a form or
report that will display by date each week broken down for that month.
The first day of our week always starts on Monday no matter if there is
a day from the last month on monday, such as November started on Wed of
this year. Our weeks start on Monday and end on Sunday. Hope this is
enough information to go by. It seems simple, but it has me stumped.

Thanks in advance
Jim

Thank you for your reply. I looked at the different links you provided
and even tested a few functions. The original author of your post
sounds like he needed just what I need but with all the different table
names, I will have to llok through the code and adapt it to my
database. The reply Tina gave got me one step closer, unless someone
has an easier way to explain or deminstrate I'll continue with her
method.

Thank you again
Jim

CDMAPoster@FortuneJames.com wrote:

Quote:

Jim wrote:

Quote:

I'm sure this has been asked before but I can't find any postings.

I have a table that has weekly inspections for multiple buildings. What
I need to do is break these down by the week of the month of my
choosing. Let me explain; I can get my week numbers no problem. I can
get the date from the week numbers no problem. What I haven't been able
to do is put things together and make a simple function that will allow
my users to select from a listbox the month and a query breaks the
output down into each week of that month. Example is we just finished
Oct, so I want my user to open a form and select the month of October
2006 and submit with a button. Then the query will run getting all
weeks in that month off the table and outputting them to a form or
report that will display by date each week broken down for that month.
The first day of our week always starts on Monday no matter if there is
a day from the last month on monday, such as November started on Wed of
this year. Our weeks start on Monday and end on Sunday. Hope this is
enough information to go by. It seems simple, but it has me stumped.

Thanks in advance
Jim

>
I addressed something like that a couple of times. See:
>
http://groups.google.com/group/micro...6e69eb76a63f76
>
James A. Fortune
CDMAPoster@FortuneJames.com

if you want a *total* of the records you're pulling for, for each week that
is returned by the query, then try taking the query i gave you and turning
it into a Totals query. just open the query in Design view and click the
Totals button on the toolbar (looks like a fancy E, or an M on its' left
side). group on the week field in the design grid. if you've never used a
Totals query, read up on it in Access Help.

if you want the individual records in the report, but separated into weekly
groups, then base your report on the original query design i posted, and use
the Sorting and Grouping dialog box to group the records by the week field.
again, you can read up on Sorting and Grouping in Access Help. if you need
totals of each group of records here, use calculated textbox controls in the
group footer section.

hth

"Jim" <jmorriz@Comcast.netwrote in message
news:1162700615.421193.146120@e3g2000cwe.googlegro ups.com...

Quote:

Thank you for your reply it worked very well. I still have to seperate
these records so I can display them on a report or form. Hopefully I'm
on the right track saying I'll build another query from this one that
will seperate each week's values. Otherwise I'll use another function
on the form or report itself to pull the individual building scores.
(Dlookup or Select)
>
Thank you again
Jim
>
tina wrote:

Quote:

try the following query, as

SELECT MyTable.theDate, DatePart("ww",[theDate],2) AS theWeek
FROM MyTable
WHERE Month([theDate]-DatePart("w",[theDate],2)+1)=10 AND
Year([theDate]-DatePart("w",[theDate],2)+1)=2006;

replace "MyTable" with the correct name of the table, and "theDate" with

the

Quote:

correct name of the date field in the table, of course. the hard-coded
criteria "10" and "2006" for month and year, will return dates between
10/2/2006 and 11/5/2006. you can replace the criteria with references to
controls on a form, so your user can choose month and year to suit.

hth

"Jim" <jmorriz@Comcast.netwrote in message
news:1162665612.368374.32660@h48g2000cwc.googlegro ups.com...

Quote:

I'm sure this has been asked before but I can't find any postings.
>
I have a table that has weekly inspections for multiple buildings.

What

Quote:

I need to do is break these down by the week of the month of my
choosing. Let me explain; I can get my week numbers no problem. I can
get the date from the week numbers no problem. What I haven't been

able

Quote:

to do is put things together and make a simple function that will

allow

Quote:

my users to select from a listbox the month and a query breaks the
output down into each week of that month. Example is we just finished
Oct, so I want my user to open a form and select the month of October,
2006 and submit with a button. Then the query will run getting all
weeks in that month off the table and outputting them to a form or
report that will display by date each week broken down for that month.
The first day of our week always starts on Monday no matter if there

is

Quote:

a day from the last month on monday, such as November started on Wed

of

Quote:

this year. Our weeks start on Monday and end on Sunday. Hope this is
enough information to go by. It seems simple, but it has me stumped.
>
Thanks in advance
Jim
>

>

Thank you again, your second suggestion is what I need to use. I have
to separate each group into weeks and display them by the week. Ex:

Week1 Week2 Week3 Week4
4.5 4.7 4.9 4.4
4.3 4.6 4.8 4.2
Etc..

Quote:

>From here I will be ranking each week and adding points to each

participant based on their position in ranking. I think I can do most
of this from a form using VBA and Recordset to save results in a table.
Your query worked great and got me back to my form design. Thank you.

Jim

tina wrote:

Quote:

if you want a *total* of the records you're pulling for, for each week that
is returned by the query, then try taking the query i gave you and turning
it into a Totals query. just open the query in Design view and click the
Totals button on the toolbar (looks like a fancy E, or an M on its' left
side). group on the week field in the design grid. if you've never used a
Totals query, read up on it in Access Help.
>
if you want the individual records in the report, but separated into weekly
groups, then base your report on the original query design i posted, and use
the Sorting and Grouping dialog box to group the records by the week field.
again, you can read up on Sorting and Grouping in Access Help. if you need
totals of each group of records here, use calculated textbox controls in the
group footer section.
>
hth
>
>
"Jim" <jmorriz@Comcast.netwrote in message
news:1162700615.421193.146120@e3g2000cwe.googlegro ups.com...

Quote:

Thank you for your reply it worked very well. I still have to seperate
these records so I can display them on a report or form. Hopefully I'm
on the right track saying I'll build another query from this one that
will seperate each week's values. Otherwise I'll use another function
on the form or report itself to pull the individual building scores.
(Dlookup or Select)

Thank you again
Jim

tina wrote:

Quote:

try the following query, as
>
SELECT MyTable.theDate, DatePart("ww",[theDate],2) AS theWeek
FROM MyTable
WHERE Month([theDate]-DatePart("w",[theDate],2)+1)=10 AND
Year([theDate]-DatePart("w",[theDate],2)+1)=2006;
>
replace "MyTable" with the correct name of the table, and "theDate" with

the

Quote:

correct name of the date field in the table, of course. the hard-coded
criteria "10" and "2006" for month and year, will return dates between
10/2/2006 and 11/5/2006. you can replace the criteria with references to
controls on a form, so your user can choose month and year to suit.
>
hth
>
>
"Jim" <jmorriz@Comcast.netwrote in message
news:1162665612.368374.32660@h48g2000cwc.googlegro ups.com...
I'm sure this has been asked before but I can't find any postings.

I have a table that has weekly inspections for multiple buildings.

What

Quote:

I need to do is break these down by the week of the month of my
choosing. Let me explain; I can get my week numbers no problem. I can
get the date from the week numbers no problem. What I haven't been

able

Quote:

to do is put things together and make a simple function that will

allow

Quote:

my users to select from a listbox the month and a query breaks the
output down into each week of that month. Example is we just finished
Oct, so I want my user to open a form and select the month of October,
2006 and submit with a button. Then the query will run getting all
weeks in that month off the table and outputting them to a form or
report that will display by date each week broken down for that month.
The first day of our week always starts on Monday no matter if there

is

Quote:

a day from the last month on monday, such as November started on Wed

of

Quote:

this year. Our weeks start on Monday and end on Sunday. Hope this is
enough information to go by. It seems simple, but it has me stumped.

Thanks in advance
Jim

keep in mind that to move the weeks from a normalized format, as

Week 1
Week 2
Week 3
Week 4

to a flat file format, as

Week 1 Week 2 Week 3 Week 4

you can utilize a Crosstab query as the base for your report. and again, you
can read up on Crosstab queries in Access Help.

hth

"Jim" <jmorriz@Comcast.netwrote in message
news:1162766329.770042.314940@i42g2000cwa.googlegr oups.com...

Quote:

Thank you again, your second suggestion is what I need to use. I have
to separate each group into weeks and display them by the week. Ex:
>
Week1 Week2 Week3 Week4
4.5 4.7 4.9 4.4
4.3 4.6 4.8 4.2
Etc..
>

Quote:

From here I will be ranking each week and adding points to each

participant based on their position in ranking. I think I can do most
of this from a form using VBA and Recordset to save results in a table.
Your query worked great and got me back to my form design. Thank you.
>
Jim
>
>
>
tina wrote:

Quote:

if you want a *total* of the records you're pulling for, for each week

that

Quote:

is returned by the query, then try taking the query i gave you and

turning

Quote:

it into a Totals query. just open the query in Design view and click the
Totals button on the toolbar (looks like a fancy E, or an M on its' left
side). group on the week field in the design grid. if you've never used

a

Quote:

Totals query, read up on it in Access Help.

if you want the individual records in the report, but separated into

weekly

Quote:

groups, then base your report on the original query design i posted, and

use

Quote:

the Sorting and Grouping dialog box to group the records by the week

field.

Quote:

again, you can read up on Sorting and Grouping in Access Help. if you

need

Quote:

totals of each group of records here, use calculated textbox controls in

the

Quote:

group footer section.

hth

"Jim" <jmorriz@Comcast.netwrote in message
news:1162700615.421193.146120@e3g2000cwe.googlegro ups.com...

Quote:

Thank you for your reply it worked very well. I still have to seperate
these records so I can display them on a report or form. Hopefully I'm
on the right track saying I'll build another query from this one that
will seperate each week's values. Otherwise I'll use another function
on the form or report itself to pull the individual building scores.
(Dlookup or Select)
>
Thank you again
Jim
>
tina wrote:
try the following query, as

SELECT MyTable.theDate, DatePart("ww",[theDate],2) AS theWeek
FROM MyTable
WHERE Month([theDate]-DatePart("w",[theDate],2)+1)=10 AND
Year([theDate]-DatePart("w",[theDate],2)+1)=2006;

replace "MyTable" with the correct name of the table, and "theDate"

with

Quote:

the

Quote:

correct name of the date field in the table, of course. the

hard-coded

Quote:

criteria "10" and "2006" for month and year, will return dates

between

Quote:

10/2/2006 and 11/5/2006. you can replace the criteria with

references to

Quote:

controls on a form, so your user can choose month and year to suit.

hth

"Jim" <jmorriz@Comcast.netwrote in message
news:1162665612.368374.32660@h48g2000cwc.googlegro ups.com...
I'm sure this has been asked before but I can't find any postings.
>
I have a table that has weekly inspections for multiple buildings.

What

Quote:

I need to do is break these down by the week of the month of my
choosing. Let me explain; I can get my week numbers no problem. I

can

Quote:

get the date from the week numbers no problem. What I haven't been

able

Quote:

to do is put things together and make a simple function that will

allow

Quote:

my users to select from a listbox the month and a query breaks the
output down into each week of that month. Example is we just

finished

Quote:

Oct, so I want my user to open a form and select the month of

October,

Quote:

2006 and submit with a button. Then the query will run getting all
weeks in that month off the table and outputting them to a form or
report that will display by date each week broken down for that

month.

Quote:

The first day of our week always starts on Monday no matter if

there

Quote:

is

Quote:

a day from the last month on monday, such as November started on

Wed

Quote:

of

Quote:

this year. Our weeks start on Monday and end on Sunday. Hope this

is

Quote:

enough information to go by. It seems simple, but it has me

stumped.

Quote:

>
Thanks in advance
Jim
>
>

>

Jim wrote:

Quote:

Thank you for your reply. I looked at the different links you provided
and even tested a few functions. The original author of your post
sounds like he needed just what I need but with all the different table
names, I will have to llok through the code and adapt it to my
database. The reply Tina gave got me one step closer, unless someone
has an easier way to explain or deminstrate I'll continue with her
method.
>
Thank you again
Jim

I looked at Microsoft's documentation about the DatePart ww bug and
decided that the bug is relatively rare. If you aren't going to be
affected by it (google this group for ww bug) then using DatePart is
going to be the simplest way to get week numbers. I like what I've
done so far but don't feel pressed to simplify it or to address the
situation that causes the problem with DatePart.

James A. Fortune
CDMAPoster@FortuneJames.com

I'm celebrating the return to political gridlock.

This worked great. I was able to use the crosstab query as my record
source on my form. Then I used the Switch function to establish the
points according to the ranking. I was going to use the IIF function
but it limits you to 15 variables and I needed more. Now I can write
all this data to a scores table and keep moving forward. The only
obstacle I see left is in the crosstab query. Since I have the column
heading broken down to the week I need the form to pick up current
month instead of week 42, 43, etc. I have to come up with a variable to
my record source that will take the current week. This is where I think
I'll need to go back to a dlookup or select function on the record
source for these fields. I'm almost there with this.

Thanks

tina wrote:

Quote:

keep in mind that to move the weeks from a normalized format, as
>
Week 1
Week 2
Week 3
Week 4
>
to a flat file format, as
>
Week 1 Week 2 Week 3 Week 4
>
you can utilize a Crosstab query as the base for your report. and again, you
can read up on Crosstab queries in Access Help.
>
hth
>
>
"Jim" <jmorriz@Comcast.netwrote in message
news:1162766329.770042.314940@i42g2000cwa.googlegr oups.com...

Quote:

Thank you again, your second suggestion is what I need to use. I have
to separate each group into weeks and display them by the week. Ex:

Week1 Week2 Week3 Week4
4.5 4.7 4.9 4.4
4.3 4.6 4.8 4.2
Etc..

Quote:

>From here I will be ranking each week and adding points to each

participant based on their position in ranking. I think I can do most
of this from a form using VBA and Recordset to save results in a table.
Your query worked great and got me back to my form design. Thank you.

Jim

tina wrote:

Quote:

if you want a *total* of the records you're pulling for, for each week

that

Quote:

is returned by the query, then try taking the query i gave you and

turning

Quote:

it into a Totals query. just open the query in Design view and click the
Totals button on the toolbar (looks like a fancy E, or an M on its' left
side). group on the week field in the design grid. if you've never used

a

Quote:

Totals query, read up on it in Access Help.
>
if you want the individual records in the report, but separated into

weekly

Quote:

groups, then base your report on the original query design i posted, and

use

Quote:

the Sorting and Grouping dialog box to group the records by the week

field.

Quote:

again, you can read up on Sorting and Grouping in Access Help. if you

need

Quote:

totals of each group of records here, use calculated textbox controls in

the

Quote:

group footer section.
>
hth
>
>
"Jim" <jmorriz@Comcast.netwrote in message
news:1162700615.421193.146120@e3g2000cwe.googlegro ups.com...
Thank you for your reply it worked very well. I still have to seperate
these records so I can display them on a report or form. Hopefully I'm
on the right track saying I'll build another query from this one that
will seperate each week's values. Otherwise I'll use another function
on the form or report itself to pull the individual building scores.
(Dlookup or Select)

Thank you again
Jim

tina wrote:
try the following query, as
>
SELECT MyTable.theDate, DatePart("ww",[theDate],2) AS theWeek
FROM MyTable
WHERE Month([theDate]-DatePart("w",[theDate],2)+1)=10 AND
Year([theDate]-DatePart("w",[theDate],2)+1)=2006;
>
replace "MyTable" with the correct name of the table, and "theDate"

with

Quote:

the
correct name of the date field in the table, of course. the

hard-coded

Quote:

criteria "10" and "2006" for month and year, will return dates

between

Quote:

10/2/2006 and 11/5/2006. you can replace the criteria with

references to

Quote:

controls on a form, so your user can choose month and year to suit.
>
hth
>
>
"Jim" <jmorriz@Comcast.netwrote in message
news:1162665612.368374.32660@h48g2000cwc.googlegro ups.com...
I'm sure this has been asked before but I can't find any postings.

I have a table that has weekly inspections for multiple buildings.
What
I need to do is break these down by the week of the month of my
choosing. Let me explain; I can get my week numbers no problem. I

can

Quote:

get the date from the week numbers no problem. What I haven't been
able
to do is put things together and make a simple function that will
allow
my users to select from a listbox the month and a query breaks the
output down into each week of that month. Example is we just

finished

Quote:

Oct, so I want my user to open a form and select the month of

October,

Quote:

2006 and submit with a button. Then the query will run getting all
weeks in that month off the table and outputting them to a form or
report that will display by date each week broken down for that

month.

Quote:

The first day of our week always starts on Monday no matter if

there

Quote:

is
a day from the last month on monday, such as November started on

Wed

Quote:

of
this year. Our weeks start on Monday and end on Sunday. Hope this

is

Quote:

enough information to go by. It seems simple, but it has me

stumped.

Quote:

Thanks in advance
Jim

you're welcome. keep plugging away, i'm sure you'll get it done. :)

"Jim" <jmorriz@Comcast.netwrote in message
news:1163122742.113381.59070@h48g2000cwc.googlegro ups.com...

Quote:

This worked great. I was able to use the crosstab query as my record
source on my form. Then I used the Switch function to establish the
points according to the ranking. I was going to use the IIF function
but it limits you to 15 variables and I needed more. Now I can write
all this data to a scores table and keep moving forward. The only
obstacle I see left is in the crosstab query. Since I have the column
heading broken down to the week I need the form to pick up current
month instead of week 42, 43, etc. I have to come up with a variable to
my record source that will take the current week. This is where I think
I'll need to go back to a dlookup or select function on the record
source for these fields. I'm almost there with this.
>
>
Thanks
>
tina wrote:

Quote:

keep in mind that to move the weeks from a normalized format, as

Week 1
Week 2
Week 3
Week 4

to a flat file format, as

Week 1 Week 2 Week 3 Week 4

you can utilize a Crosstab query as the base for your report. and again,

you

Quote:

can read up on Crosstab queries in Access Help.

hth

"Jim" <jmorriz@Comcast.netwrote in message
news:1162766329.770042.314940@i42g2000cwa.googlegr oups.com...

Quote:

Thank you again, your second suggestion is what I need to use. I have
to separate each group into weeks and display them by the week. Ex:
>
Week1 Week2 Week3 Week4
4.5 4.7 4.9 4.4
4.3 4.6 4.8 4.2
Etc..
>
From here I will be ranking each week and adding points to each
participant based on their position in ranking. I think I can do most
of this from a form using VBA and Recordset to save results in a

table.

Quote:

Your query worked great and got me back to my form design. Thank you.
>
Jim
>
>
>
tina wrote:
if you want a *total* of the records you're pulling for, for each

week

Quote:

that

Quote:

is returned by the query, then try taking the query i gave you and

turning

Quote:

it into a Totals query. just open the query in Design view and click

the

Quote:

Totals button on the toolbar (looks like a fancy E, or an M on its'

left

Quote:

side). group on the week field in the design grid. if you've never

used

Quote:

a

Quote:

Totals query, read up on it in Access Help.

if you want the individual records in the report, but separated into

weekly

Quote:

groups, then base your report on the original query design i posted,

and

Quote:

use

Quote:

the Sorting and Grouping dialog box to group the records by the week

field.

Quote:

again, you can read up on Sorting and Grouping in Access Help. if

you

Quote:

need

Quote:

totals of each group of records here, use calculated textbox

controls in

Quote:

the

Quote:

group footer section.

hth

"Jim" <jmorriz@Comcast.netwrote in message
news:1162700615.421193.146120@e3g2000cwe.googlegro ups.com...
Thank you for your reply it worked very well. I still have to

seperate

Quote:

these records so I can display them on a report or form. Hopefully

I'm

Quote:

on the right track saying I'll build another query from this one

that

Quote:

will seperate each week's values. Otherwise I'll use another

function

Quote:

on the form or report itself to pull the individual building

scores.

Quote:

(Dlookup or Select)
>
Thank you again
Jim
>
tina wrote:
try the following query, as

SELECT MyTable.theDate, DatePart("ww",[theDate],2) AS theWeek
FROM MyTable
WHERE Month([theDate]-DatePart("w",[theDate],2)+1)=10 AND
Year([theDate]-DatePart("w",[theDate],2)+1)=2006;

replace "MyTable" with the correct name of the table, and

"theDate"

Quote:

with

Quote:

the
correct name of the date field in the table, of course. the

hard-coded

Quote:

criteria "10" and "2006" for month and year, will return dates

between

Quote:

10/2/2006 and 11/5/2006. you can replace the criteria with

references to

Quote:

controls on a form, so your user can choose month and year to

suit.

Quote:

hth

"Jim" <jmorriz@Comcast.netwrote in message
news:1162665612.368374.32660@h48g2000cwc.googlegro ups.com...
I'm sure this has been asked before but I can't find any

postings.

Quote:

>
I have a table that has weekly inspections for multiple

buildings.

Quote:

What
I need to do is break these down by the week of the month of

my

Quote:

choosing. Let me explain; I can get my week numbers no

problem. I

Quote:

can

Quote:

get the date from the week numbers no problem. What I haven't

been

Quote:

able
to do is put things together and make a simple function that

will

Quote:

allow
my users to select from a listbox the month and a query breaks

the

Quote:

output down into each week of that month. Example is we just

finished

Quote:

Oct, so I want my user to open a form and select the month of

October,

Quote:

2006 and submit with a button. Then the query will run getting

all

Quote:

weeks in that month off the table and outputting them to a

form or

Quote:

report that will display by date each week broken down for

that

Quote:

month.

Quote:

The first day of our week always starts on Monday no matter if

there

Quote:

is
a day from the last month on monday, such as November started

on

Quote:

Wed

Quote:

of
this year. Our weeks start on Monday and end on Sunday. Hope

this

Quote:

is

Quote:

enough information to go by. It seems simple, but it has me

stumped.

Quote:

>
Thanks in advance
Jim
>
>
>

>

Week of the month

re: Week of the month

re: Week of the month

re: Week of the month

re: Week of the month

re: Week of the month

re: Week of the month

re: Week of the month

re: Week of the month

re: Week of the month

re: Week of the month

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about