Duane Hookom's Calendar Style Schedule

[brownti via AccessMonster.com]

I have adapted the above report to suite my needs, however cant figure one
thing out. Since my calendar spans several months, i would like to try and
divide the months somehow. I have tried adding it to the query for a
'MonthOf' field, but cant get that to work, it looks right in the query, but
when i add a heading for 'MonthOf' and add it, they all go to January. Has
any one done this that could help? Thanks!

 

[Duane Hookom]

Your main report's record source would need to include the Month as well as
the week of. Add a table with the values 1 - 12 ie:
tblMonths
==============
MthNum (values 1 - 12)

Add tblMonths to the report's record source and set criteria under the
MthNum like
Between Month(WeekOf) AND Month(WeekOf+6). This should create two records
for each [WeekOf] that crosses months. Use the MthNum to group by in the
report.

Duane Hookom
MS Access MVP

 

[brownti via AccessMonster.com]

The criteria you listed isnt working. it puts quotes around WeekOf which
creates a data type mismatch. i tried using the weekof equation in its place
but that creates way to many records. any ideas?

Your main report's record source would need to include the Month as well as
the week of. Add a table with the values 1 - 12 ie:
tblMonths
==============
MthNum (values 1 - 12)

Add tblMonths to the report's record source and set criteria under the
MthNum like
Between Month(WeekOf) AND Month(WeekOf+6). This should create two records
for each [WeekOf] that crosses months. Use the MthNum to group by in the
report.

Duane Hookom
MS Access MVP

I have adapted the above report to suite my needs, however cant figure one
thing out. Since my calendar spans several months, i would like to try

[quoted text clipped - 5 lines]

Has
any one done this that could help? Thanks!

 

[duanehookom]

I need to know the SQL view of your main report. It should be grouped
by and use WHERE for the criteria.

Duane Hookom
MS Access MVP

The criteria you listed isnt working.  it puts quotes around WeekOf which
creates a data type mismatch.  i tried using the weekof equation in itsplace
but that creates way to many records.  any ideas?

Your main report's record source would need to include the Month as wellas
the week of. Add a table with the values 1 - 12 ie:
tblMonths
==============
MthNum  (values 1 - 12)

Add tblMonths to the report's record source and set criteria under the
MthNum like
Between Month(WeekOf) AND Month(WeekOf+6). This should create two records
for each [WeekOf] that crosses months. Use the MthNum to group by in the
report.

Duane Hookom
MSAccessMVP

I have adapted the above report to suite my needs, however cant figure one
thing out.  Since mycalendarspans several months, i would like to try

[quoted text clipped - 5 lines]

Has
any one done this that could help?  Thanks!

 

[brownti via AccessMonster.com]

Here is the SQL that i am trying to use

SELECT DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1 AS
WeekOf, tblMonths.MthNum
FROM qryMillworkCalendarSetup, tblMonths
GROUP BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1,
tblMonths.MthNum
HAVING (((tblMonths.MthNum) Between Month("WeekOf") And Month("WeekOf"+6)))
ORDER BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1;

I need to know the SQL view of your main report. It should be grouped
by and use WHERE for the criteria.

Duane Hookom
MS Access MVP

The criteria you listed isnt working.  it puts quotes around WeekOf which
creates a data type mismatch.  i tried using the weekof equation in its place

[quoted text clipped - 25 lines]

- Show quoted text -

 

[Duane Hookom]

Try something like:

SELECT DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1 AS
WeekOf, tblMonths.MthNum
FROM qryMillworkCalendarSetup, tblMonths
WHERE (((tblMonths.MthNum) Between
Month(DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1) And
Month(DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+7)))
GROUP BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1,
tblMonths.MthNum
ORDER BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1;

--
Duane Hookom
Microsoft Access MVP

Here is the SQL that i am trying to use

SELECT DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1 AS
WeekOf, tblMonths.MthNum
FROM qryMillworkCalendarSetup, tblMonths
GROUP BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1,
tblMonths.MthNum
HAVING (((tblMonths.MthNum) Between Month("WeekOf") And Month("WeekOf"+6)))
ORDER BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1;

I need to know the SQL view of your main report. It should be grouped
by and use WHERE for the criteria.

Duane Hookom
MS Access MVP

The criteria you listed isnt working. it puts quotes around WeekOf which
creates a data type mismatch. i tried using the weekof equation in its place

[quoted text clipped - 25 lines]

- Show quoted text -

 

[brownti via AccessMonster.com]

That creates a WeekOf for every value in tblMonths. So the first two records
are correct:

WeekOf MthNum
12/14/2008 12
12/21/2008 12

But then the remaining are incorrect:
WeekOf MthNum
12/28/2008 1
12/28/2008 2
12/28/2008 3
12/28/2008 4
12/28/2008 5
12/28/2008 6
12/28/2008 7
12/28/2008 8
12/28/2008 9
12/28/2008 10
12/28/2008 11
12/28/2008 12

Any ideas?

Try something like:

SELECT DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1 AS
WeekOf, tblMonths.MthNum
FROM qryMillworkCalendarSetup, tblMonths
WHERE (((tblMonths.MthNum) Between
Month(DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1) And
Month(DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+7)))
GROUP BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1,
tblMonths.MthNum
ORDER BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1;

Here is the SQL that i am trying to use

[quoted text clipped - 19 lines]

 

[Duane Hookom]

Please post the full SQL view of your report's record source.
--
Duane Hookom
Microsoft Access MVP

That creates a WeekOf for every value in tblMonths. So the first two records
are correct:

WeekOf MthNum
12/14/2008 12
12/21/2008 12

But then the remaining are incorrect:
WeekOf MthNum
12/28/2008 1
12/28/2008 2
12/28/2008 3
12/28/2008 4
12/28/2008 5
12/28/2008 6
12/28/2008 7
12/28/2008 8
12/28/2008 9
12/28/2008 10
12/28/2008 11
12/28/2008 12

Any ideas?

Try something like:

SELECT DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1 AS
WeekOf, tblMonths.MthNum
FROM qryMillworkCalendarSetup, tblMonths
WHERE (((tblMonths.MthNum) Between
Month(DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1) And
Month(DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+7)))
GROUP BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1,
tblMonths.MthNum
ORDER BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1;

Here is the SQL that i am trying to use

[quoted text clipped - 19 lines]

- Show quoted text -

 

[brownti via AccessMonster.com]

Here is the SQL that produces the original calendar prior to adding anything
with the month. My month table is called tblMonths and has one column MthNum
which has a records from 1-12 in it.

SELECT DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1 AS
WeekOf
FROM qryMillworkCalendarSetup
GROUP BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1
ORDER BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1;

Please post the full SQL view of your report's record source.

That creates a WeekOf for every value in tblMonths. So the first two records
are correct:

[quoted text clipped - 37 lines]

 

[Duane Hookom]

That doesn't help me much. I was hoping you would post the SQL "That creates
a WeekOf for every value in tblMonths"

--
Duane Hookom
Microsoft Access MVP

Here is the SQL that produces the original calendar prior to adding anything
with the month. My month table is called tblMonths and has one column MthNum
which has a records from 1-12 in it.

SELECT DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1 AS
WeekOf
FROM qryMillworkCalendarSetup
GROUP BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1
ORDER BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1;

Please post the full SQL view of your report's record source.

That creates a WeekOf for every value in tblMonths. So the first two records
are correct:

[quoted text clipped - 37 lines]

- Show quoted text -

 

[brownti via AccessMonster.com]

The SQL that you had posted is what creates the all the records:

SELECT DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1 AS
WeekOf, tblMonths.MthNum
FROM qryMillworkCalendarSetup, tblMonths
WHERE (((tblMonths.MthNum) Between Month(DateAdd("d",-Weekday(
[MillworkDelivery]),[MillworkDelivery])+1) And Month(DateAdd("d",-Weekday(
[MillworkDelivery]),[MillworkDelivery])+7)))
GROUP BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1,
tblMonths.MthNum
ORDER BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1;

That doesn't help me much. I was hoping you would post the SQL "That creates
a WeekOf for every value in tblMonths"

Here is the SQL that produces the original calendar prior to adding anything
with the month. My month table is called tblMonths and has one column MthNum

[quoted text clipped - 12 lines]

 

[Duane Hookom]

Try put
Month(DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1)
and
Month(DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+7))
into column/field expression at the top of the grid to view the values
returned.


--
Duane Hookom
Microsoft Access MVP

The SQL that you had posted is what creates the all the records:

SELECT DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1 AS
WeekOf, tblMonths.MthNum
FROM qryMillworkCalendarSetup, tblMonths
WHERE (((tblMonths.MthNum) Between Month(DateAdd("d",-Weekday(
[MillworkDelivery]),[MillworkDelivery])+1) And Month(DateAdd("d",-Weekday(
[MillworkDelivery]),[MillworkDelivery])+7)))
GROUP BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1,
tblMonths.MthNum
ORDER BY DateAdd("d",-Weekday([MillworkDelivery]),[MillworkDelivery])+1;

That doesn't help me much. I was hoping you would post the SQL "That creates
a WeekOf for every value in tblMonths"

Here is the SQL that produces the original calendar prior to adding anything
with the month. My month table is called tblMonths and has one column MthNum

[quoted text clipped - 12 lines]

- Show quoted text -