linking two fields in two subreports

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Hi

I have created a calendar type report (many thanks to Duane Hookom's
reference report). The issue here is if the scheduled job is 2 days, how do
I extend the field from day1 to day2 (like it does in outlook calendar) in
the calendar if it is from a different sub report.

Hope you all understand what I'm trying to achieve here.

Many thanks in advance
Richard
 
What I have done in the past is create a table with all dates (tblAllDates
and field TheDate). Assume a tblEvents with start and end dates and times.
Consider starting with a query that create separate records for each date.
Something like:
SELECT tblEvents.StartDate, tblEvents.StartTime, tblEvents.EndDate,
tblEvents.EndTime, tblAllDates.TheDate,
IIf([TheDate]>[StartDate],0,[StartTime]) AS RptStartTime,
IIf([TheDate]<[EndDate],0.99999,[EndTime]) AS RptEndTime
FROM tblAllDates, tblEvents
WHERE (((tblAllDates.TheDate) Between [StartDate] And [EndDate]));
 
Hi Duane

Thank you for taking time to reply and the tip. Should I put the query in
the subform.

What I have now is a table with all dates (full year) on the main form, then
use your "Weekof" (+1, +2 etc) field as headers for subforms.

Will what you suggest connect the subforms together? I will try it out in
the meantime.

Thanks again

Richard

Duane Hookom said:
What I have done in the past is create a table with all dates (tblAllDates
and field TheDate). Assume a tblEvents with start and end dates and times.
Consider starting with a query that create separate records for each date.
Something like:
SELECT tblEvents.StartDate, tblEvents.StartTime, tblEvents.EndDate,
tblEvents.EndTime, tblAllDates.TheDate,
IIf([TheDate]>[StartDate],0,[StartTime]) AS RptStartTime,
IIf([TheDate]<[EndDate],0.99999,[EndTime]) AS RptEndTime
FROM tblAllDates, tblEvents
WHERE (((tblAllDates.TheDate) Between [StartDate] And [EndDate]));


--
Duane Hookom
MS Access MVP


Richard said:
Hi

I have created a calendar type report (many thanks to Duane Hookom's
reference report). The issue here is if the scheduled job is 2 days, how
do
I extend the field from day1 to day2 (like it does in outlook calendar) in
the calendar if it is from a different sub report.

Hope you all understand what I'm trying to achieve here.

Many thanks in advance
Richard
Click to expand...
Click to expand...
 
You should not have a recordsource of the main report that has all dates. It
should only contain one date per week (either Sunday or Monday).

--
Duane Hookom
MS Access MVP


Richard said:
Hi Duane

Thank you for taking time to reply and the tip. Should I put the query in
the subform.

What I have now is a table with all dates (full year) on the main form,
then
use your "Weekof" (+1, +2 etc) field as headers for subforms.

Will what you suggest connect the subforms together? I will try it out in
the meantime.

Thanks again

Richard

Duane Hookom said:
What I have done in the past is create a table with all dates
(tblAllDates
and field TheDate). Assume a tblEvents with start and end dates and
times.
Consider starting with a query that create separate records for each
date.
Something like:
SELECT tblEvents.StartDate, tblEvents.StartTime, tblEvents.EndDate,
tblEvents.EndTime, tblAllDates.TheDate,
IIf([TheDate]>[StartDate],0,[StartTime]) AS RptStartTime,
IIf([TheDate]<[EndDate],0.99999,[EndTime]) AS RptEndTime
FROM tblAllDates, tblEvents
WHERE (((tblAllDates.TheDate) Between [StartDate] And [EndDate]));


--
Duane Hookom
MS Access MVP


Richard said:
Hi

I have created a calendar type report (many thanks to Duane Hookom's
reference report). The issue here is if the scheduled job is 2 days,
how
do
I extend the field from day1 to day2 (like it does in outlook calendar) in
the calendar if it is from a different sub report.

Hope you all understand what I'm trying to achieve here.

Many thanks in advance
Richard
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane

If I remove the all date table from the recordsource and put in the
scheduled event table, I won't get a full calendar, ie. only the "weekof" of
scheduled events shows in the report. With the full dates table, I will get
a full calendar, from Jan 1 to Dec 31.

Or how will I able to set a table with only one date per week using code?

Regards
Richard

Duane Hookom said:
You should not have a recordsource of the main report that has all dates. It
should only contain one date per week (either Sunday or Monday).

--
Duane Hookom
MS Access MVP


Richard said:
Hi Duane

Thank you for taking time to reply and the tip. Should I put the query in
the subform.

What I have now is a table with all dates (full year) on the main form,
then
use your "Weekof" (+1, +2 etc) field as headers for subforms.

Will what you suggest connect the subforms together? I will try it out in
the meantime.

Thanks again

Richard

Duane Hookom said:
What I have done in the past is create a table with all dates
(tblAllDates
and field TheDate). Assume a tblEvents with start and end dates and
times.
Consider starting with a query that create separate records for each
date.
Something like:
SELECT tblEvents.StartDate, tblEvents.StartTime, tblEvents.EndDate,
tblEvents.EndTime, tblAllDates.TheDate,
IIf([TheDate]>[StartDate],0,[StartTime]) AS RptStartTime,
IIf([TheDate]<[EndDate],0.99999,[EndTime]) AS RptEndTime
FROM tblAllDates, tblEvents
WHERE (((tblAllDates.TheDate) Between [StartDate] And [EndDate]));


--
Duane Hookom
MS Access MVP


Hi

I have created a calendar type report (many thanks to Duane Hookom's
reference report). The issue here is if the scheduled job is 2 days,
how
do
I extend the field from day1 to day2 (like it does in outlook
Click to expand...
calendar)
in
the calendar if it is from a different sub report.

Hope you all understand what I'm trying to achieve here.

Many thanks in advance
Richard
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane

I managed to make the scheduled event name appear on the 2nd day subform. ie
Monday and Tuesday will show the name of the 2-day event. But will we be
able to make it show only one field where the textbox from Mon stretches to
Tuesday with only one name. (like in Outlook Calendar). Hope you understand.

Thanks agani
Richard


Richard said:
Hi Duane

If I remove the all date table from the recordsource and put in the
scheduled event table, I won't get a full calendar, ie. only the "weekof" of
scheduled events shows in the report. With the full dates table, I will get
a full calendar, from Jan 1 to Dec 31.

Or how will I able to set a table with only one date per week using code?

Regards
Richard

Duane Hookom said:
You should not have a recordsource of the main report that has all
Click to expand...
dates.
It
should only contain one date per week (either Sunday or Monday).

--
Duane Hookom
MS Access MVP


Richard said:
Hi Duane

Thank you for taking time to reply and the tip. Should I put the query in
the subform.

What I have now is a table with all dates (full year) on the main form,
then
use your "Weekof" (+1, +2 etc) field as headers for subforms.

Will what you suggest connect the subforms together? I will try it out in
the meantime.

Thanks again

Richard

What I have done in the past is create a table with all dates
(tblAllDates
and field TheDate). Assume a tblEvents with start and end dates and
times.
Consider starting with a query that create separate records for each
date.
Something like:
SELECT tblEvents.StartDate, tblEvents.StartTime, tblEvents.EndDate,
tblEvents.EndTime, tblAllDates.TheDate,
IIf([TheDate]>[StartDate],0,[StartTime]) AS RptStartTime,
IIf([TheDate]<[EndDate],0.99999,[EndTime]) AS RptEndTime
FROM tblAllDates, tblEvents
WHERE (((tblAllDates.TheDate) Between [StartDate] And [EndDate]));


--
Duane Hookom
MS Access MVP


Hi

I have created a calendar type report (many thanks to Duane Hookom's
reference report). The issue here is if the scheduled job is 2 days,
how
do
I extend the field from day1 to day2 (like it does in outlook calendar)
in
the calendar if it is from a different sub report.

Hope you all understand what I'm trying to achieve here.

Many thanks in advance
Richard
Click to expand...
Click to expand...
Click to expand...
 
If we are talking about the same report, the record source of the main
report only contains one record per week. There are 5 or 7 subreports that
link to the other days of the week.

--
Duane Hookom
MS Access MVP
--

Richard said:
Hi Duane

If I remove the all date table from the recordsource and put in the
scheduled event table, I won't get a full calendar, ie. only the "weekof"
of
scheduled events shows in the report. With the full dates table, I will
get
a full calendar, from Jan 1 to Dec 31.

Or how will I able to set a table with only one date per week using code?

Regards
Richard

Duane Hookom said:
You should not have a recordsource of the main report that has all dates. It
should only contain one date per week (either Sunday or Monday).

--
Duane Hookom
MS Access MVP


Richard said:
Hi Duane

Thank you for taking time to reply and the tip. Should I put the query in
the subform.

What I have now is a table with all dates (full year) on the main form,
then
use your "Weekof" (+1, +2 etc) field as headers for subforms.

Will what you suggest connect the subforms together? I will try it out in
the meantime.

Thanks again

Richard

What I have done in the past is create a table with all dates
(tblAllDates
and field TheDate). Assume a tblEvents with start and end dates and
times.
Consider starting with a query that create separate records for each
date.
Something like:
SELECT tblEvents.StartDate, tblEvents.StartTime, tblEvents.EndDate,
tblEvents.EndTime, tblAllDates.TheDate,
IIf([TheDate]>[StartDate],0,[StartTime]) AS RptStartTime,
IIf([TheDate]<[EndDate],0.99999,[EndTime]) AS RptEndTime
FROM tblAllDates, tblEvents
WHERE (((tblAllDates.TheDate) Between [StartDate] And [EndDate]));


--
Duane Hookom
MS Access MVP


Hi

I have created a calendar type report (many thanks to Duane Hookom's
reference report). The issue here is if the scheduled job is 2 days,
how
do
I extend the field from day1 to day2 (like it does in outlook calendar)
in
the calendar if it is from a different sub report.

Hope you all understand what I'm trying to achieve here.

Many thanks in advance
Richard
Click to expand...
Click to expand...
Click to expand...
 
Back
Top