Duane H's calendar sample

[jkatj]

I've been building a calendar report using Duane H's sample calendar database
file from http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

I think I've figured out everything I need for my calendar but one last very
important thing. Duane's got Master/Child link fields set up linking each
subreport's "SchedDate" field to the main report's "txtDay1" through
"txtDay5" fields. This seems to be the trick to get each entry to show up
only for one day rather than repeating across each day of the week (which is
what I have happening now). However, I can't find anywhere in the
report/queries/Visual Basic code/etc. where "txtDay1" through "txtDay5" are
defined. Also, when I click on the "..." button next to the Link Mater Fields
Property, I get a message that the expression I entered "refers to an object
that is closed or doesn't exist."

Does anybody know how to tackle this final hurdle for putting together a
calendar? I'm excited to have gotten as close as I am to a final calendar
being quite new to Access, but I'd really like to put the final touch on.

Thanks in advance for any help!

 

[Duane Hookom]

Look at the very top of the detail section. There are 5 text boxes that
display values based on the "WeekOf" field from the report's record source.

 

[jkatj]

Duane, thank you! I should have thought that the "txt" was short for text box
given the other abbreviations in your sample, I just didn't know that a text
box could be used as a field like that.

Now that I've got each entry only showing on its own day of the week, I've
noticed another problem. Ain't that just the way it always goes?

I'm not sure what information is relevant, so forgive me if I give too much
or too little background here. Unlike your sample, which is grouped/sorted by
doctor and then by WeekOf, my report only has one sorting/grouping: Due Date.
My SUBreport seems to work fine with entries showing up in the right place
and the right number of times. However, in my main report, I'm getting some
oddities. For weeks with only one project due, everything seems fine. But for
weeks with multiple projects due, the weeks of entries are repeating. For
example, a week with 3 projects due repeats 3 times (presumably once for each
project). Also, for these weeks that repeat, they are grouped by time due
(all projects due at 5pm Monday or 5pm any other day come up together in a
repeating row; all projects with a 9am time come up on a separate row).
That's hard to explain, so here's an example of how a 4-project week would
appear:

3/20 5pm Client 1 Booklet 3/22 5pm Client
2 Banner
5pm Client
3 Booklet


3/21 9am Client 4 Cover


3/20 5pm Client 1 Booklet 3/22 5pm Client
2 Banner
5pm Client
3 Booklet


3/20 5pm Client 1 Booklet 3/22 5pm Client
2 Banner
5pm Client
3 Booklet

I've tried every change I can think of in sorting/grouping (including
removing all sorting/grouping) at all levels: query, subreport, and report.
Nothing makes a difference to this clumping and repeating.

Also, there are three weeks that come up on the report as blank even though
there should be an entry. I can't guess why (1) the week shows up if it
thinks there is nothing for it and (2) an entry which does appear in the
query and subreport is empty/invisible on the main report.

On an unrelated note, I'm also hoping to find a way to make all weeks show
up whether there is a project due that week or not.

Any suggestions for me on any of these issues?

 

[Duane Hookom]

Your main report needs to have a WeekOf column/field that doesn't include a
time element. Your date field might have a time portion so you need to strip
the time using DateValue([YourDateTimeField])

 

[jkatj]

Duane, I really appreciate you taking the time to help me. I've seen
responses from you to a lot of people with calendar questions. I think it's
incredibly generous of you to not only offer your sample database, but to
answer questions as well!

Getting my date and time separated into different fields cleared up the
problem of the duplicate weeks appearing with all the 5pm items grouped and
then all the 9am items grouped. However, the issue of any week with multiple
project due dates repeating multiple times remains. So if there are 5
projects due in a week, that complete week repeats 5 times in a row. I
thought perhaps having a date field on the subreport might have been
triggering the duplicates somehow, but getting rid of it didn't fix the
repetition. It's so frustrating being this close to having the calendar work,
but not be able to work out this last bit! Do you have any other suggestions?

Your main report needs to have a WeekOf column/field that doesn't include a
time element. Your date field might have a time portion so you need to strip
the time using DateValue([YourDateTimeField])

--
Duane Hookom
MS Access MVP
--

Duane, thank you! I should have thought that the "txt" was short for text
box
given the other abbreviations in your sample, I just didn't know that a
text
box could be used as a field like that.

Now that I've got each entry only showing on its own day of the week, I've
noticed another problem. Ain't that just the way it always goes?

I'm not sure what information is relevant, so forgive me if I give too
much
or too little background here. Unlike your sample, which is grouped/sorted
by
doctor and then by WeekOf, my report only has one sorting/grouping: Due
Date.
My SUBreport seems to work fine with entries showing up in the right place
and the right number of times. However, in my main report, I'm getting
some
oddities. For weeks with only one project due, everything seems fine. But
for
weeks with multiple projects due, the weeks of entries are repeating. For
example, a week with 3 projects due repeats 3 times (presumably once for
each
project). Also, for these weeks that repeat, they are grouped by time due
(all projects due at 5pm Monday or 5pm any other day come up together in a
repeating row; all projects with a 9am time come up on a separate row).
That's hard to explain, so here's an example of how a 4-project week would
appear:

3/20 5pm Client 1 Booklet 3/22 5pm
Client
2 Banner
5pm
Client
3 Booklet


3/21 9am Client 4 Cover


3/20 5pm Client 1 Booklet 3/22 5pm
Client
2 Banner
5pm
Client
3 Booklet


3/20 5pm Client 1 Booklet 3/22 5pm
Client
2 Banner
5pm
Client
3 Booklet

I've tried every change I can think of in sorting/grouping (including
removing all sorting/grouping) at all levels: query, subreport, and
report.
Nothing makes a difference to this clumping and repeating.

Also, there are three weeks that come up on the report as blank even
though
there should be an entry. I can't guess why (1) the week shows up if it
thinks there is nothing for it and (2) an entry which does appear in the
query and subreport is empty/invisible on the main report.

On an unrelated note, I'm also hoping to find a way to make all weeks show
up whether there is a project due that week or not.

Any suggestions for me on any of these issues?

 

[Duane Hookom]

What does the record source of your main report look like? Can you provide
some sample fields and records? I expect you have too much detail in the
record source.

--
Duane Hookom
MS Access MVP
--

Duane, I really appreciate you taking the time to help me. I've seen
responses from you to a lot of people with calendar questions. I think
it's
incredibly generous of you to not only offer your sample database, but to
answer questions as well!

Getting my date and time separated into different fields cleared up the
problem of the duplicate weeks appearing with all the 5pm items grouped
and
then all the 9am items grouped. However, the issue of any week with
multiple
project due dates repeating multiple times remains. So if there are 5
projects due in a week, that complete week repeats 5 times in a row. I
thought perhaps having a date field on the subreport might have been
triggering the duplicates somehow, but getting rid of it didn't fix the
repetition. It's so frustrating being this close to having the calendar
work,
but not be able to work out this last bit! Do you have any other
suggestions?

Your main report needs to have a WeekOf column/field that doesn't include
a
time element. Your date field might have a time portion so you need to
strip
the time using DateValue([YourDateTimeField])

--
Duane Hookom
MS Access MVP
--

Duane, thank you! I should have thought that the "txt" was short for
text
box
given the other abbreviations in your sample, I just didn't know that a
text
box could be used as a field like that.

Now that I've got each entry only showing on its own day of the week,
I've
noticed another problem. Ain't that just the way it always goes?

I'm not sure what information is relevant, so forgive me if I give too
much
or too little background here. Unlike your sample, which is
grouped/sorted
by
doctor and then by WeekOf, my report only has one sorting/grouping: Due
Date.
My SUBreport seems to work fine with entries showing up in the right
place
and the right number of times. However, in my main report, I'm getting
some
oddities. For weeks with only one project due, everything seems fine.
But
for
weeks with multiple projects due, the weeks of entries are repeating.
For
example, a week with 3 projects due repeats 3 times (presumably once
for
each
project). Also, for these weeks that repeat, they are grouped by time
due
(all projects due at 5pm Monday or 5pm any other day come up together
in a
repeating row; all projects with a 9am time come up on a separate row).
That's hard to explain, so here's an example of how a 4-project week
would
appear:

3/20 5pm Client 1 Booklet 3/22 5pm
Client
2 Banner
5pm
Client
3 Booklet


3/21 9am Client 4 Cover


3/20 5pm Client 1 Booklet 3/22 5pm
Client
2 Banner
5pm
Client
3 Booklet


3/20 5pm Client 1 Booklet 3/22 5pm
Client
2 Banner
5pm
Client
3 Booklet

I've tried every change I can think of in sorting/grouping (including
removing all sorting/grouping) at all levels: query, subreport, and
report.
Nothing makes a difference to this clumping and repeating.

Also, there are three weeks that come up on the report as blank even
though
there should be an entry. I can't guess why (1) the week shows up if it
thinks there is nothing for it and (2) an entry which does appear in
the
query and subreport is empty/invisible on the main report.

On an unrelated note, I'm also hoping to find a way to make all weeks
show
up whether there is a project due that week or not.

Any suggestions for me on any of these issues?

 

[jkatj]

The record source is a query with the following fields (the CompletionDate
field is set to not show; it filters for Null records):
Date -- Time -- WeekOf -- ClientName -- ProjectType -- Employee --
CompletionDate

Sample data would be:
2/3/2006 -- 3:00 PM -- 1/30/2006 -- John Doe Company -- Presentation -- Smith
2/7/2006 -- 12:00 PM -- 2/6/2006 -- Acme Inc. -- Brochure -- Johnson
2/16/2006 -- 12:00 PM -- 2/13/2006 -- John Q. Public Products -- Booklet --
Smith

What does the record source of your main report look like? Can you provide
some sample fields and records? I expect you have too much detail in the
record source.

--
Duane Hookom
MS Access MVP
--

Duane, I really appreciate you taking the time to help me. I've seen
responses from you to a lot of people with calendar questions. I think
it's
incredibly generous of you to not only offer your sample database, but to
answer questions as well!

Getting my date and time separated into different fields cleared up the
problem of the duplicate weeks appearing with all the 5pm items grouped
and
then all the 9am items grouped. However, the issue of any week with
multiple
project due dates repeating multiple times remains. So if there are 5
projects due in a week, that complete week repeats 5 times in a row. I
thought perhaps having a date field on the subreport might have been
triggering the duplicates somehow, but getting rid of it didn't fix the
repetition. It's so frustrating being this close to having the calendar
work,
but not be able to work out this last bit! Do you have any other
suggestions?

Your main report needs to have a WeekOf column/field that doesn't include
a
time element. Your date field might have a time portion so you need to
strip
the time using DateValue([YourDateTimeField])

--
Duane Hookom
MS Access MVP
--

Duane, thank you! I should have thought that the "txt" was short for
text
box
given the other abbreviations in your sample, I just didn't know that a
text
box could be used as a field like that.

Now that I've got each entry only showing on its own day of the week,
I've
noticed another problem. Ain't that just the way it always goes?

I'm not sure what information is relevant, so forgive me if I give too
much
or too little background here. Unlike your sample, which is
grouped/sorted
by
doctor and then by WeekOf, my report only has one sorting/grouping: Due
Date.
My SUBreport seems to work fine with entries showing up in the right
place
and the right number of times. However, in my main report, I'm getting
some
oddities. For weeks with only one project due, everything seems fine.
But
for
weeks with multiple projects due, the weeks of entries are repeating.
For
example, a week with 3 projects due repeats 3 times (presumably once
for
each
project). Also, for these weeks that repeat, they are grouped by time
due
(all projects due at 5pm Monday or 5pm any other day come up together
in a
repeating row; all projects with a 9am time come up on a separate row).
That's hard to explain, so here's an example of how a 4-project week
would
appear:

3/20 5pm Client 1 Booklet 3/22 5pm
Client
2 Banner
5pm
Client
3 Booklet


3/21 9am Client 4 Cover


3/20 5pm Client 1 Booklet 3/22 5pm
Client
2 Banner
5pm
Client
3 Booklet


3/20 5pm Client 1 Booklet 3/22 5pm
Client
2 Banner
5pm
Client
3 Booklet

I've tried every change I can think of in sorting/grouping (including
removing all sorting/grouping) at all levels: query, subreport, and
report.
Nothing makes a difference to this clumping and repeating.

Also, there are three weeks that come up on the report as blank even
though
there should be an entry. I can't guess why (1) the week shows up if it
thinks there is nothing for it and (2) an entry which does appear in
the
query and subreport is empty/invisible on the main report.

On an unrelated note, I'm also hoping to find a way to make all weeks
show
up whether there is a project due that week or not.

Any suggestions for me on any of these issues?

 

[Duane Hookom]

The main report's record source should be a group by that only shows
Sundays. The record source of the example uses:
DateAdd("d",-Weekday([SchedDate]),[schedDate])+1 AS WeekOf
This converts all dates to a Sunday. I included the Doctor field because I
wanted a separate calender page for each doctor.

You need to convert your dates and get rid of the times etc from your main
report's record source.

--
Duane Hookom
MS Access MVP
--

The record source is a query with the following fields (the CompletionDate
field is set to not show; it filters for Null records):
Date -- Time -- WeekOf -- ClientName -- ProjectType -- Employee --
CompletionDate

Sample data would be:
2/3/2006 -- 3:00 PM -- 1/30/2006 -- John Doe Company -- Presentation --
Smith
2/7/2006 -- 12:00 PM -- 2/6/2006 -- Acme Inc. -- Brochure -- Johnson
2/16/2006 -- 12:00 PM -- 2/13/2006 -- John Q. Public Products --
Booklet --
Smith

What does the record source of your main report look like? Can you
provide
some sample fields and records? I expect you have too much detail in the
record source.

--
Duane Hookom
MS Access MVP
--

Duane, I really appreciate you taking the time to help me. I've seen
responses from you to a lot of people with calendar questions. I think
it's
incredibly generous of you to not only offer your sample database, but
to
answer questions as well!

Getting my date and time separated into different fields cleared up the
problem of the duplicate weeks appearing with all the 5pm items grouped
and
then all the 9am items grouped. However, the issue of any week with
multiple
project due dates repeating multiple times remains. So if there are 5
projects due in a week, that complete week repeats 5 times in a row. I
thought perhaps having a date field on the subreport might have been
triggering the duplicates somehow, but getting rid of it didn't fix the
repetition. It's so frustrating being this close to having the calendar
work,
but not be able to work out this last bit! Do you have any other
suggestions?


:

Your main report needs to have a WeekOf column/field that doesn't
include
a
time element. Your date field might have a time portion so you need to
strip
the time using DateValue([YourDateTimeField])

--
Duane Hookom
MS Access MVP
--

Duane, thank you! I should have thought that the "txt" was short for
text
box
given the other abbreviations in your sample, I just didn't know
that a
text
box could be used as a field like that.

Now that I've got each entry only showing on its own day of the
week,
I've
noticed another problem. Ain't that just the way it always goes?

I'm not sure what information is relevant, so forgive me if I give
too
much
or too little background here. Unlike your sample, which is
grouped/sorted
by
doctor and then by WeekOf, my report only has one sorting/grouping:
Due
Date.
My SUBreport seems to work fine with entries showing up in the right
place
and the right number of times. However, in my main report, I'm
getting
some
oddities. For weeks with only one project due, everything seems
fine.
But
for
weeks with multiple projects due, the weeks of entries are
repeating.
For
example, a week with 3 projects due repeats 3 times (presumably once
for
each
project). Also, for these weeks that repeat, they are grouped by
time
due
(all projects due at 5pm Monday or 5pm any other day come up
together
in a
repeating row; all projects with a 9am time come up on a separate
row).
That's hard to explain, so here's an example of how a 4-project week
would
appear:

3/20 5pm Client 1 Booklet 3/22 5pm
Client
2 Banner
5pm
Client
3 Booklet


3/21 9am Client 4 Cover


3/20 5pm Client 1 Booklet 3/22 5pm
Client
2 Banner
5pm
Client
3 Booklet


3/20 5pm Client 1 Booklet 3/22 5pm
Client
2 Banner
5pm
Client
3 Booklet

I've tried every change I can think of in sorting/grouping
(including
removing all sorting/grouping) at all levels: query, subreport, and
report.
Nothing makes a difference to this clumping and repeating.

Also, there are three weeks that come up on the report as blank even
though
there should be an entry. I can't guess why (1) the week shows up if
it
thinks there is nothing for it and (2) an entry which does appear in
the
query and subreport is empty/invisible on the main report.

On an unrelated note, I'm also hoping to find a way to make all
weeks
show
up whether there is a project due that week or not.

Any suggestions for me on any of these issues?

 

[jkatj]

I was running into some problems when I tried to strip my report's query down
to just WeekOf, so I've started from scratch.

Like your sample, my new subreport is based on the main data table rather
than a query, and I've only included Time and ClientName as fields in the
subreport's Detail section to keep things equal to what your example shows.

I've set my main report's recordsource as:
SELECT DateAdd("d",-Weekday([Date]),[Date])+1 AS WeekOf FROM [Project
Tracking];
And the Date field WeekOf is based on is strictly a date, no time.

If I "group by" WeekOf like in your sample database, I get the error "Data
type mismatch in your criteria expression" when trying to open the Print
Preview window. Likewise if I try to sort by WeekOf. However, that error
doesn't appear when opening Print Preview with no sorting or grouping on, but
I do have the same problem as before -- each week shows up over and over
again.

Maybe there's just something inherently wrong with my database making it not
function well in calendar form? I'm a novice at Access, so that's entirely
possible. Anyway, thanks again for all your help.

 

[Duane Hookom]

Is your [Date] field an actual date/time field or is it text? When viewed in
datasheet view, is it left or right aligned? Is your [Date] field ever Null?

--
Duane Hookom
MS Access MVP
--

I was running into some problems when I tried to strip my report's query
down
to just WeekOf, so I've started from scratch.

Like your sample, my new subreport is based on the main data table rather
than a query, and I've only included Time and ClientName as fields in the
subreport's Detail section to keep things equal to what your example
shows.

I've set my main report's recordsource as:
SELECT DateAdd("d",-Weekday([Date]),[Date])+1 AS WeekOf FROM [Project
Tracking];
And the Date field WeekOf is based on is strictly a date, no time.

If I "group by" WeekOf like in your sample database, I get the error "Data
type mismatch in your criteria expression" when trying to open the Print
Preview window. Likewise if I try to sort by WeekOf. However, that error
doesn't appear when opening Print Preview with no sorting or grouping on,
but
I do have the same problem as before -- each week shows up over and over
again.

Maybe there's just something inherently wrong with my database making it
not
function well in calendar form? I'm a novice at Access, so that's entirely
possible. Anyway, thanks again for all your help.

The main report's record source should be a group by that only shows
Sundays. The record source of the example uses:
DateAdd("d",-Weekday([SchedDate]),[schedDate])+1 AS WeekOf
This converts all dates to a Sunday. I included the Doctor field because
I
wanted a separate calender page for each doctor.

You need to convert your dates and get rid of the times etc from your
main
report's record source.

 

[jkatj]

There were a couple of records with no date in the Date field, and correcting
that seems to have fixed the problems I was having. Thank you so much for
taking the time to help me with this project.

Is your [Date] field an actual date/time field or is it text? When viewed in
datasheet view, is it left or right aligned? Is your [Date] field ever Null?

--
Duane Hookom
MS Access MVP
--

I was running into some problems when I tried to strip my report's query
down
to just WeekOf, so I've started from scratch.

Like your sample, my new subreport is based on the main data table rather
than a query, and I've only included Time and ClientName as fields in the
subreport's Detail section to keep things equal to what your example
shows.

I've set my main report's recordsource as:
SELECT DateAdd("d",-Weekday([Date]),[Date])+1 AS WeekOf FROM [Project
Tracking];
And the Date field WeekOf is based on is strictly a date, no time.

If I "group by" WeekOf like in your sample database, I get the error "Data
type mismatch in your criteria expression" when trying to open the Print
Preview window. Likewise if I try to sort by WeekOf. However, that error
doesn't appear when opening Print Preview with no sorting or grouping on,
but
I do have the same problem as before -- each week shows up over and over
again.

Maybe there's just something inherently wrong with my database making it
not
function well in calendar form? I'm a novice at Access, so that's entirely
possible. Anyway, thanks again for all your help.

The main report's record source should be a group by that only shows
Sundays. The record source of the example uses:
DateAdd("d",-Weekday([SchedDate]),[schedDate])+1 AS WeekOf
This converts all dates to a Sunday. I included the Doctor field because
I
wanted a separate calender page for each doctor.

You need to convert your dates and get rid of the times etc from your
main
report's record source.

 

[Duane Hookom]

Glad to be able to assist.

--
Duane Hookom
MS Access MVP
--

There were a couple of records with no date in the Date field, and
correcting
that seems to have fixed the problems I was having. Thank you so much for
taking the time to help me with this project.

Is your [Date] field an actual date/time field or is it text? When viewed
in
datasheet view, is it left or right aligned? Is your [Date] field ever
Null?

--
Duane Hookom
MS Access MVP
--

I was running into some problems when I tried to strip my report's query
down
to just WeekOf, so I've started from scratch.

Like your sample, my new subreport is based on the main data table
rather
than a query, and I've only included Time and ClientName as fields in
the
subreport's Detail section to keep things equal to what your example
shows.

I've set my main report's recordsource as:
SELECT DateAdd("d",-Weekday([Date]),[Date])+1 AS WeekOf FROM [Project
Tracking];
And the Date field WeekOf is based on is strictly a date, no time.

If I "group by" WeekOf like in your sample database, I get the error
"Data
type mismatch in your criteria expression" when trying to open the
Print
Preview window. Likewise if I try to sort by WeekOf. However, that
error
doesn't appear when opening Print Preview with no sorting or grouping
on,
but
I do have the same problem as before -- each week shows up over and
over
again.

Maybe there's just something inherently wrong with my database making
it
not
function well in calendar form? I'm a novice at Access, so that's
entirely
possible. Anyway, thanks again for all your help.


:

The main report's record source should be a group by that only shows
Sundays. The record source of the example uses:
DateAdd("d",-Weekday([SchedDate]),[schedDate])+1 AS WeekOf
This converts all dates to a Sunday. I included the Doctor field
because
I
wanted a separate calender page for each doctor.

You need to convert your dates and get rid of the times etc from your
main
report's record source.