Output a date range

[Tim]

When entering data on a form, I would like to put an event in with a start
and end date. Then when I print this information in a report, I would like
to have a record printed for each date within the date range on the form. Is
this possible? If so, how?

Thanks

 

[Marshall Barton]

When entering data on a form, I would like to put an event in with a start
and end date. Then when I print this information in a report, I would like
to have a record printed for each date within the date range on the form. Is
this possible? If so, how?

You want to print nonexistent data in a report, right?

This should have nothing to do with the form, only the
report.

Assuming the start and end date fields have a value in every
record, then this can do what I think you want.

First, you need a table (named Numbers) with one field
(named Num). Populate the table with record values from 0
up to more than the maximum range you will ever have.

Then use this kind of query for the report's record source:

SELECT Numbers.Num, table.*
FROM table, Numbers
WHERE Numbers.Num <= DateDiff("d", startdate, enddate)

The report detail's date text box can then use the
expression:
=DateAdd("d", Num, startdate)

There's probably more to it than that, but until you tell us
more details about what else you need, I wouldn't want to
guess what it might be.

 

[Tim]

Thanks for the note, Marshall. I tried this but it did not work. I have a
form that a person uses to enter the date range for the report. The SQL view
of the report record source is:

SELECT tbl_training_event_tracker.StartDate, Numbers.Num,
tbl_training_event_tracker.StartTime, tbl_training_event_tracker.EndTime,
tbl_event.EventName, tbl_unit.UnitName, tbl_uniform.Uniform,
tbl_training_event_tracker.TrainerName, tbl_training_event_tracker.Location,
tbl_training_event_tracker.References,
tbl_training_event_tracker.PersonnelNeedTrng,
tbl_training_event_tracker.EndDate
FROM Numbers, tbl_unit INNER JOIN ((tbl_event INNER JOIN tbl_uniform ON
tbl_event.Uniform = tbl_uniform.UniformID) INNER JOIN
tbl_training_event_tracker ON tbl_event.EventID =
tbl_training_event_tracker.EventID) ON tbl_unit.UnitID =
tbl_training_event_tracker.UnitID
WHERE (((tbl_training_event_tracker.StartDate) Between
[Forms]![frm_training_lookup]![TrngStartDate] And
[Forms]![frm_training_lookup]![TrngEndDate]) AND
((Numbers.Num)<=DateDiff("d","StartDate","EndDate")))
ORDER BY tbl_training_event_tracker.StartDate,
tbl_training_event_tracker.StartTime;

Then I run the report from the form and a MsgBox pops up asking to Enter
Parameter Value for =DateAdd("d", Num, startdate).

Please help and thanks for the help.
Tim

 

[Duane Hookom]

A quick glance shows quotes where there should be []s
WHERE (((tbl_training_event_tracker.StartDate) Between
[Forms]![frm_training_lookup]![TrngStartDate] And
[Forms]![frm_training_lookup]![TrngEndDate]) AND
((Numbers.Num)<=DateDiff("d",[StartDate],[EndDate])))
ORDER BY tbl_training_event_tracker.StartDate,
tbl_training_event_tracker.StartTime;

--
Duane Hookom
Microsoft Access MVP

Thanks for the note, Marshall. I tried this but it did not work. I have a
form that a person uses to enter the date range for the report. The SQL view
of the report record source is:

SELECT tbl_training_event_tracker.StartDate, Numbers.Num,
tbl_training_event_tracker.StartTime, tbl_training_event_tracker.EndTime,
tbl_event.EventName, tbl_unit.UnitName, tbl_uniform.Uniform,
tbl_training_event_tracker.TrainerName, tbl_training_event_tracker.Location,
tbl_training_event_tracker.References,
tbl_training_event_tracker.PersonnelNeedTrng,
tbl_training_event_tracker.EndDate
FROM Numbers, tbl_unit INNER JOIN ((tbl_event INNER JOIN tbl_uniform ON
tbl_event.Uniform = tbl_uniform.UniformID) INNER JOIN
tbl_training_event_tracker ON tbl_event.EventID =
tbl_training_event_tracker.EventID) ON tbl_unit.UnitID =
tbl_training_event_tracker.UnitID
WHERE (((tbl_training_event_tracker.StartDate) Between
[Forms]![frm_training_lookup]![TrngStartDate] And
[Forms]![frm_training_lookup]![TrngEndDate]) AND
((Numbers.Num)<=DateDiff("d","StartDate","EndDate")))
ORDER BY tbl_training_event_tracker.StartDate,
tbl_training_event_tracker.StartTime;

Then I run the report from the form and a MsgBox pops up asking to Enter
Parameter Value for =DateAdd("d", Num, startdate).

Please help and thanks for the help.
Tim

You want to print nonexistent data in a report, right?

This should have nothing to do with the form, only the
report.

Assuming the start and end date fields have a value in every
record, then this can do what I think you want.

First, you need a table (named Numbers) with one field
(named Num). Populate the table with record values from 0
up to more than the maximum range you will ever have.

Then use this kind of query for the report's record source:

SELECT Numbers.Num, table.*
FROM table, Numbers
WHERE Numbers.Num <= DateDiff("d", startdate, enddate)

The report detail's date text box can then use the
expression:
=DateAdd("d", Num, startdate)

There's probably more to it than that, but until you tell us
more details about what else you need, I wouldn't want to
guess what it might be.

 

[Tim]

Thank you to all for the help. The report works beautifully.

A quick glance shows quotes where there should be []s
WHERE (((tbl_training_event_tracker.StartDate) Between
[Forms]![frm_training_lookup]![TrngStartDate] And
[Forms]![frm_training_lookup]![TrngEndDate]) AND
((Numbers.Num)<=DateDiff("d",[StartDate],[EndDate])))
ORDER BY tbl_training_event_tracker.StartDate,
tbl_training_event_tracker.StartTime;

--
Duane Hookom
Microsoft Access MVP

Thanks for the note, Marshall. I tried this but it did not work. I have a
form that a person uses to enter the date range for the report. The SQL view
of the report record source is:

SELECT tbl_training_event_tracker.StartDate, Numbers.Num,
tbl_training_event_tracker.StartTime, tbl_training_event_tracker.EndTime,
tbl_event.EventName, tbl_unit.UnitName, tbl_uniform.Uniform,
tbl_training_event_tracker.TrainerName, tbl_training_event_tracker.Location,
tbl_training_event_tracker.References,
tbl_training_event_tracker.PersonnelNeedTrng,
tbl_training_event_tracker.EndDate
FROM Numbers, tbl_unit INNER JOIN ((tbl_event INNER JOIN tbl_uniform ON
tbl_event.Uniform = tbl_uniform.UniformID) INNER JOIN
tbl_training_event_tracker ON tbl_event.EventID =
tbl_training_event_tracker.EventID) ON tbl_unit.UnitID =
tbl_training_event_tracker.UnitID
WHERE (((tbl_training_event_tracker.StartDate) Between
[Forms]![frm_training_lookup]![TrngStartDate] And
[Forms]![frm_training_lookup]![TrngEndDate]) AND
((Numbers.Num)<=DateDiff("d","StartDate","EndDate")))
ORDER BY tbl_training_event_tracker.StartDate,
tbl_training_event_tracker.StartTime;

Then I run the report from the form and a MsgBox pops up asking to Enter
Parameter Value for =DateAdd("d", Num, startdate).

Please help and thanks for the help.
Tim

Tim wrote:

When entering data on a form, I would like to put an event in with a start
and end date. Then when I print this information in a report, I would like
to have a record printed for each date within the date range on the form. Is
this possible? If so, how?


You want to print nonexistent data in a report, right?

This should have nothing to do with the form, only the
report.

Assuming the start and end date fields have a value in every
record, then this can do what I think you want.

First, you need a table (named Numbers) with one field
(named Num). Populate the table with record values from 0
up to more than the maximum range you will ever have.

Then use this kind of query for the report's record source:

SELECT Numbers.Num, table.*
FROM table, Numbers
WHERE Numbers.Num <= DateDiff("d", startdate, enddate)

The report detail's date text box can then use the
expression:
=DateAdd("d", Num, startdate)

There's probably more to it than that, but until you tell us
more details about what else you need, I wouldn't want to
guess what it might be.

 

[Tim]

One last question, in the report this groups the no existent data with the
existent data and I would like the report to sort by StartDate and StartTime.
Thanks again for all the help.

Thank you to all for the help. The report works beautifully.

A quick glance shows quotes where there should be []s
WHERE (((tbl_training_event_tracker.StartDate) Between
[Forms]![frm_training_lookup]![TrngStartDate] And
[Forms]![frm_training_lookup]![TrngEndDate]) AND
((Numbers.Num)<=DateDiff("d",[StartDate],[EndDate])))
ORDER BY tbl_training_event_tracker.StartDate,
tbl_training_event_tracker.StartTime;

--
Duane Hookom
Microsoft Access MVP

Thanks for the note, Marshall. I tried this but it did not work. I have a
form that a person uses to enter the date range for the report. The SQL view
of the report record source is:

SELECT tbl_training_event_tracker.StartDate, Numbers.Num,
tbl_training_event_tracker.StartTime, tbl_training_event_tracker.EndTime,
tbl_event.EventName, tbl_unit.UnitName, tbl_uniform.Uniform,
tbl_training_event_tracker.TrainerName, tbl_training_event_tracker.Location,
tbl_training_event_tracker.References,
tbl_training_event_tracker.PersonnelNeedTrng,
tbl_training_event_tracker.EndDate
FROM Numbers, tbl_unit INNER JOIN ((tbl_event INNER JOIN tbl_uniform ON
tbl_event.Uniform = tbl_uniform.UniformID) INNER JOIN
tbl_training_event_tracker ON tbl_event.EventID =
tbl_training_event_tracker.EventID) ON tbl_unit.UnitID =
tbl_training_event_tracker.UnitID
WHERE (((tbl_training_event_tracker.StartDate) Between
[Forms]![frm_training_lookup]![TrngStartDate] And
[Forms]![frm_training_lookup]![TrngEndDate]) AND
((Numbers.Num)<=DateDiff("d","StartDate","EndDate")))
ORDER BY tbl_training_event_tracker.StartDate,
tbl_training_event_tracker.StartTime;

Then I run the report from the form and a MsgBox pops up asking to Enter
Parameter Value for =DateAdd("d", Num, startdate).

Please help and thanks for the help.
Tim

:

Tim wrote:

When entering data on a form, I would like to put an event in with a start
and end date. Then when I print this information in a report, I would like
to have a record printed for each date within the date range on the form. Is
this possible? If so, how?


You want to print nonexistent data in a report, right?

This should have nothing to do with the form, only the
report.

Assuming the start and end date fields have a value in every
record, then this can do what I think you want.

First, you need a table (named Numbers) with one field
(named Num). Populate the table with record values from 0
up to more than the maximum range you will ever have.

Then use this kind of query for the report's record source:

SELECT Numbers.Num, table.*
FROM table, Numbers
WHERE Numbers.Num <= DateDiff("d", startdate, enddate)

The report detail's date text box can then use the
expression:
=DateAdd("d", Num, startdate)

There's probably more to it than that, but until you tell us
more details about what else you need, I wouldn't want to
guess what it might be.

 

[Duane Hookom]

Sorting a report is generally done by setting the fields/expressions in the
sorting and grouping dialog.

--
Duane Hookom
Microsoft Access MVP

One last question, in the report this groups the no existent data with the
existent data and I would like the report to sort by StartDate and StartTime.
Thanks again for all the help.

Thank you to all for the help. The report works beautifully.

A quick glance shows quotes where there should be []s
WHERE (((tbl_training_event_tracker.StartDate) Between
[Forms]![frm_training_lookup]![TrngStartDate] And
[Forms]![frm_training_lookup]![TrngEndDate]) AND
((Numbers.Num)<=DateDiff("d",[StartDate],[EndDate])))
ORDER BY tbl_training_event_tracker.StartDate,
tbl_training_event_tracker.StartTime;

--
Duane Hookom
Microsoft Access MVP


:

Thanks for the note, Marshall. I tried this but it did not work. I have a
form that a person uses to enter the date range for the report. The SQL view
of the report record source is:

SELECT tbl_training_event_tracker.StartDate, Numbers.Num,
tbl_training_event_tracker.StartTime, tbl_training_event_tracker.EndTime,
tbl_event.EventName, tbl_unit.UnitName, tbl_uniform.Uniform,
tbl_training_event_tracker.TrainerName, tbl_training_event_tracker.Location,
tbl_training_event_tracker.References,
tbl_training_event_tracker.PersonnelNeedTrng,
tbl_training_event_tracker.EndDate
FROM Numbers, tbl_unit INNER JOIN ((tbl_event INNER JOIN tbl_uniform ON
tbl_event.Uniform = tbl_uniform.UniformID) INNER JOIN
tbl_training_event_tracker ON tbl_event.EventID =
tbl_training_event_tracker.EventID) ON tbl_unit.UnitID =
tbl_training_event_tracker.UnitID
WHERE (((tbl_training_event_tracker.StartDate) Between
[Forms]![frm_training_lookup]![TrngStartDate] And
[Forms]![frm_training_lookup]![TrngEndDate]) AND
((Numbers.Num)<=DateDiff("d","StartDate","EndDate")))
ORDER BY tbl_training_event_tracker.StartDate,
tbl_training_event_tracker.StartTime;

Then I run the report from the form and a MsgBox pops up asking to Enter
Parameter Value for =DateAdd("d", Num, startdate).

Please help and thanks for the help.
Tim

:

Tim wrote:

When entering data on a form, I would like to put an event in with a start
and end date. Then when I print this information in a report, I would like
to have a record printed for each date within the date range on the form. Is
this possible? If so, how?


You want to print nonexistent data in a report, right?

This should have nothing to do with the form, only the
report.

Assuming the start and end date fields have a value in every
record, then this can do what I think you want.

First, you need a table (named Numbers) with one field
(named Num). Populate the table with record values from 0
up to more than the maximum range you will ever have.

Then use this kind of query for the report's record source:

SELECT Numbers.Num, table.*
FROM table, Numbers
WHERE Numbers.Num <= DateDiff("d", startdate, enddate)

The report detail's date text box can then use the
expression:
=DateAdd("d", Num, startdate)

There's probably more to it than that, but until you tell us
more details about what else you need, I wouldn't want to
guess what it might be.