|
|
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!
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200812/1
|
|
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" <u31540[ at ]uwe> wrote in message
news:8f25c164ff495[ at ]uwe...
[Quoted Text] >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! > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200812/1>
|
|
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?
Duane Hookom wrote:
[Quoted Text] >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!
--
Message posted via http://www.accessmonster.com
|
|
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
On Dec 29, 7:39 am, "brownti via AccessMonster.com" <u31540[ at ]uwe>
wrote:
[Quoted Text] > 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? > > > > > > Duane Hookom wrote: > >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 > >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! > > -- > Message posted via http://www.accessmonster.com- Hide quoted text - > > - Show quoted text -
|
|
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;
duanehookom[ at ]gmail.com wrote:
[Quoted Text] >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
>
>On Dec 29, 7:39Â am, "brownti via AccessMonster.com" <u31540[ at ]uwe>
>wrote:
>> 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 -
--
Message posted via http://www.accessmonster.com
|
|
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
"brownti via AccessMonster.com" wrote:
[Quoted Text] > 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; > > > duanehookom[ at ]gmail.com wrote: > >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 > > > >On Dec 29, 7:39 am, "brownti via AccessMonster.com" <u31540[ at ]uwe> > >wrote: > >> 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 - > > -- > Message posted via http://www.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?
Duane Hookom wrote:
[Quoted Text] >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 -
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200812/1
|
|
Please post the full SQL view of your report's record source.
--
Duane Hookom
Microsoft Access MVP
"brownti via AccessMonster.com" wrote:
[Quoted Text] > 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? > > > Duane Hookom wrote: > >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 - > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200812/1> > |
|
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;
Duane Hookom wrote:
[Quoted Text] >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 -
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200812/1
|
|
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
"brownti via AccessMonster.com" wrote:
[Quoted Text] > 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; > > > Duane Hookom wrote: > >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 - > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200812/1> > |
|
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;
Duane Hookom wrote:
[Quoted Text] >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 -
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200812/1
|
|
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
"brownti via AccessMonster.com" wrote:
[Quoted Text] > 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; > > > Duane Hookom wrote: > >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 - > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200812/1> > |
|
|