Report with date range selection

[Marv Trott]

Hi,

I need to create an Access 2003 Summary Report with the following specs.

1) User selected Date Range for the report.
2) The Reports displays totals for 15 categories with three to ten items in
each category.
3) All data is contained in three databases.

I created crosstab queries for two category and placed them in the report.
They work fine if the date range is entered in the query.

What overall design approach is required to allow the user to enter a date
range for the entire report? Also what specific pitfalls might there be?

This is strictly a volunteer project for a 501C non-profit organization.

Thanks for your help

Marv Trott

 

[Allen Browne]

Create a form with two text boxes named (say) StartDate and EndDate.
Set their Format property to Short Date, so Access won't accept invalid
dates.
Save the form as (say) GetDates.

In your query, in the Criteria row under your date field, enter:
Between [Forms].[GetDates].[StartDate] And [Forms].[GetDates].[EndDate]

Especially in a crosstab query, you need to declare these as parameters. In
query design view, choose Parameters from the Query menu. Access opens a
dialog. Enter two lines:
[Forms].[GetDates].[StartDate] Date/Time
[Forms].[GetDates].[EndDate] Date/Time

Provided the form is open and the dates are entered, the query should now
read its parameters from the form instead of asking you for them again.

 

[Marv Trott]

Allen,

Thanks for the directions. I let you know how I do.

Regards,

Marv

Create a form with two text boxes named (say) StartDate and EndDate.
Set their Format property to Short Date, so Access won't accept invalid
dates.
Save the form as (say) GetDates.

In your query, in the Criteria row under your date field, enter:
Between [Forms].[GetDates].[StartDate] And [Forms].[GetDates].[EndDate]

Especially in a crosstab query, you need to declare these as parameters. In
query design view, choose Parameters from the Query menu. Access opens a
dialog. Enter two lines:
[Forms].[GetDates].[StartDate] Date/Time
[Forms].[GetDates].[EndDate] Date/Time

Provided the form is open and the dates are entered, the query should now
read its parameters from the form instead of asking you for them again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I need to create an Access 2003 Summary Report with the following specs.

1) User selected Date Range for the report.
2) The Reports displays totals for 15 categories with three to ten items
in
each category.
3) All data is contained in three databases.

I created crosstab queries for two category and placed them in the report.
They work fine if the date range is entered in the query.

What overall design approach is required to allow the user to enter a date
range for the entire report? Also what specific pitfalls might there be?

This is strictly a volunteer project for a 501C non-profit organization.

Thanks for your help

Marv Trott

 

[Marv Trott]

Allen,

A problem occured when I ran the Form. It appears that I do not understand
how to assign the Record Source for the form. I created a table called
tblGetDates and set this table as the Record Source for the form. I set the
Control Source for the startdate and enddate to the field names in
tblGetDates. I can display both dates the Report but I cannot display the
data from the crosstab query ctqRace. I did not add the Between[enter
StartDate....] as a condition in the ctqRace until I can at least display
the data from two or more cross tab queries on the Report for a fixed date.

I must be missing a several very important issues.

Again thanks for the guidance.

Marv

Allen,

Thanks for the directions. I let you know how I do.

Regards,

Marv

Create a form with two text boxes named (say) StartDate and EndDate.
Set their Format property to Short Date, so Access won't accept invalid
dates.
Save the form as (say) GetDates.

In your query, in the Criteria row under your date field, enter:
Between [Forms].[GetDates].[StartDate] And [Forms].[GetDates].[EndDate]

Especially in a crosstab query, you need to declare these as parameters. In
query design view, choose Parameters from the Query menu. Access opens a
dialog. Enter two lines:
[Forms].[GetDates].[StartDate] Date/Time
[Forms].[GetDates].[EndDate] Date/Time

Provided the form is open and the dates are entered, the query should now
read its parameters from the form instead of asking you for them again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I need to create an Access 2003 Summary Report with the following specs.

1) User selected Date Range for the report.
2) The Reports displays totals for 15 categories with three to ten items
in
each category.
3) All data is contained in three databases.

I created crosstab queries for two category and placed them in the report.
They work fine if the date range is entered in the query.

What overall design approach is required to allow the user to enter a date
range for the entire report? Also what specific pitfalls might there be?

This is strictly a volunteer project for a 501C non-profit organization.

Thanks for your help

Marv Trott

 

[Allen Browne]

Hi Marv

The idea was to use an unbound form.
It does not need a RecordSource, and you don't need a tblGetDates.

You should be fine adding the Criteria to the crosstab as long as you
declare the parameters also.

BTW, since the 2 text boxes on the form are unbound, you need to stop the
user from entering invalid dates. You can do that by setting the Format
property of the text boxes to Short Date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

A problem occured when I ran the Form. It appears that I do not understand
how to assign the Record Source for the form. I created a table called
tblGetDates and set this table as the Record Source for the form. I set
the
Control Source for the startdate and enddate to the field names in
tblGetDates. I can display both dates the Report but I cannot display the
data from the crosstab query ctqRace. I did not add the Between[enter
StartDate....] as a condition in the ctqRace until I can at least display
the data from two or more cross tab queries on the Report for a fixed
date.

I must be missing a several very important issues.

Again thanks for the guidance.

Marv

Allen,

Thanks for the directions. I let you know how I do.

Regards,

Marv

Create a form with two text boxes named (say) StartDate and EndDate.
Set their Format property to Short Date, so Access won't accept invalid
dates.
Save the form as (say) GetDates.

In your query, in the Criteria row under your date field, enter:
Between [Forms].[GetDates].[StartDate] And [Forms].[GetDates].[EndDate]

Especially in a crosstab query, you need to declare these as
parameters. In
query design view, choose Parameters from the Query menu. Access opens
a
dialog. Enter two lines:
[Forms].[GetDates].[StartDate] Date/Time
[Forms].[GetDates].[EndDate] Date/Time

Provided the form is open and the dates are entered, the query should now
read its parameters from the form instead of asking you for them again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I need to create an Access 2003 Summary Report with the following specs.

1) User selected Date Range for the report.
2) The Reports displays totals for 15 categories with three to ten items
in
each category.
3) All data is contained in three databases.

I created crosstab queries for two category and placed them in the report.
They work fine if the date range is entered in the query.

What overall design approach is required to allow the user to enter a date
range for the entire report? Also what specific pitfalls might there be?

This is strictly a volunteer project for a 501C non-profit organization.

Thanks for your help

Marv Trott

 

[Marv Trott]

Hi Allen,

I fixed the GetDates Form but I need more help.

On the Report design screen.

1) How do I determine what should be used for the Record Source for the
report?

2) There will be data from many crosstable queries that needs to be
displayed in the report. What is an example of code to be entered into the
Control Source for each of text boxes on the report so the data in the
selected crosstab query for the selected data range will appear on the
report?

Thanks,

Marv

Hi Marv

The idea was to use an unbound form.
It does not need a RecordSource, and you don't need a tblGetDates.

You should be fine adding the Criteria to the crosstab as long as you
declare the parameters also.

BTW, since the 2 text boxes on the form are unbound, you need to stop the
user from entering invalid dates. You can do that by setting the Format
property of the text boxes to Short Date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

A problem occured when I ran the Form. It appears that I do not understand
how to assign the Record Source for the form. I created a table called
tblGetDates and set this table as the Record Source for the form. I set
the
Control Source for the startdate and enddate to the field names in
tblGetDates. I can display both dates the Report but I cannot display the
data from the crosstab query ctqRace. I did not add the Between[enter
StartDate....] as a condition in the ctqRace until I can at least display
the data from two or more cross tab queries on the Report for a fixed
date.

I must be missing a several very important issues.

Again thanks for the guidance.

Marv

Allen,

Thanks for the directions. I let you know how I do.

Regards,

Marv

Create a form with two text boxes named (say) StartDate and EndDate.
Set their Format property to Short Date, so Access won't accept invalid
dates.
Save the form as (say) GetDates.

In your query, in the Criteria row under your date field, enter:
Between [Forms].[GetDates].[StartDate] And
[Forms].[GetDates].[EndDate]

Especially in a crosstab query, you need to declare these as
parameters.
In
query design view, choose Parameters from the Query menu. Access opens
a
dialog. Enter two lines:
[Forms].[GetDates].[StartDate] Date/Time
[Forms].[GetDates].[EndDate] Date/Time

Provided the form is open and the dates are entered, the query should now
read its parameters from the form instead of asking you for them again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I need to create an Access 2003 Summary Report with the following
specs.

1) User selected Date Range for the report.
2) The Reports displays totals for 15 categories with three to ten items
in
each category.
3) All data is contained in three databases.

I created crosstab queries for two category and placed them in the
report.
They work fine if the date range is entered in the query.

What overall design approach is required to allow the user to enter a
date
range for the entire report? Also what specific pitfalls might

there
be?

This is strictly a volunteer project for a 501C non-profit organization.

Thanks for your help

Marv Trott

 

[Allen Browne]

The RecordSource of your report will be whichever query provides the data
for it.

Many crosstab(?) queries supply the data for one report? A report can have
only one RecordSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I fixed the GetDates Form but I need more help.

On the Report design screen.

1) How do I determine what should be used for the Record Source for the
report?

2) There will be data from many crosstable queries that needs to be
displayed in the report. What is an example of code to be entered into the
Control Source for each of text boxes on the report so the data in the
selected crosstab query for the selected data range will appear on the
report?

Thanks,

Marv

Hi Marv

The idea was to use an unbound form.
It does not need a RecordSource, and you don't need a tblGetDates.

You should be fine adding the Criteria to the crosstab as long as you
declare the parameters also.

BTW, since the 2 text boxes on the form are unbound, you need to stop the
user from entering invalid dates. You can do that by setting the Format
property of the text boxes to Short Date.

Allen,

A problem occured when I ran the Form. It appears that I do not understand
how to assign the Record Source for the form. I created a table called
tblGetDates and set this table as the Record Source for the form. I set
the
Control Source for the startdate and enddate to the field names in
tblGetDates. I can display both dates the Report but I cannot display the
data from the crosstab query ctqRace. I did not add the Between[enter
StartDate....] as a condition in the ctqRace until I can at least display
the data from two or more cross tab queries on the Report for a fixed
date.

I must be missing a several very important issues.

Again thanks for the guidance.

Marv

Allen,

Thanks for the directions. I let you know how I do.

Regards,

Marv

Create a form with two text boxes named (say) StartDate and EndDate.
Set their Format property to Short Date, so Access won't accept invalid
dates.
Save the form as (say) GetDates.

In your query, in the Criteria row under your date field, enter:
Between [Forms].[GetDates].[StartDate] And
[Forms].[GetDates].[EndDate]

Especially in a crosstab query, you need to declare these as
parameters.
In
query design view, choose Parameters from the Query menu. Access opens
a
dialog. Enter two lines:
[Forms].[GetDates].[StartDate] Date/Time
[Forms].[GetDates].[EndDate] Date/Time

Provided the form is open and the dates are entered, the query
should
now
read its parameters from the form instead of asking you for them again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I need to create an Access 2003 Summary Report with the following
specs.

1) User selected Date Range for the report.
2) The Reports displays totals for 15 categories with three to ten
items
in
each category.
3) All data is contained in three databases.

I created crosstab queries for two category and placed them in the
report.
They work fine if the date range is entered in the query.

What overall design approach is required to allow the user to
enter a
date
range for the entire report? Also what specific pitfalls might there
be?

This is strictly a volunteer project for a 501C non-profit
organization.

Thanks for your help

Marv Trott

 

[Marv Trott]

Allen,

I guess I have been attempting to solve the report problem the wrong way.

If I understand where we are now it appears that I need to write one query
that will produce a series of numbers which represents the number of times
each of three to six different numbered entries occur in each of ten
different text fields that are taken from two databases for a selected date
range. Then use this query as the record source for the report.

Example of data to be used in the Report

For the date range from 09/01/04 to 09/30/04
i.e.; Field 1 data from individual records 2,2,2 4,4 7,7,7: Result
displayed in report Self Employed 3 Home base Business 2 Store Front
3
Field 2 data from individual records 1,1,1,1,1 3,3,3,3
6,6,6,6,6: Result displayed in report Newspapers 5 Radio 4 Internet 5
Field 3 .......etc
If this is what need to be done how is this accomplished in one query?

I have two books; Access Office 2003 Bible (Wiley) and Access 2003 (Que) but
I have not been able to find the answers in either.

Thanks for your patience with my questions.

Marv

The RecordSource of your report will be whichever query provides the data
for it.

Many crosstab(?) queries supply the data for one report? A report can have
only one RecordSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I fixed the GetDates Form but I need more help.

On the Report design screen.

1) How do I determine what should be used for the Record Source for the
report?

2) There will be data from many crosstable queries that needs to be
displayed in the report. What is an example of code to be entered into the
Control Source for each of text boxes on the report so the data in the
selected crosstab query for the selected data range will appear on the
report?

Thanks,

Marv

Hi Marv

The idea was to use an unbound form.
It does not need a RecordSource, and you don't need a tblGetDates.

You should be fine adding the Criteria to the crosstab as long as you
declare the parameters also.

BTW, since the 2 text boxes on the form are unbound, you need to stop the
user from entering invalid dates. You can do that by setting the Format
property of the text boxes to Short Date.


Allen,

A problem occured when I ran the Form. It appears that I do not understand
how to assign the Record Source for the form. I created a table called
tblGetDates and set this table as the Record Source for the form. I set
the
Control Source for the startdate and enddate to the field names in
tblGetDates. I can display both dates the Report but I cannot display the
data from the crosstab query ctqRace. I did not add the Between[enter
StartDate....] as a condition in the ctqRace until I can at least display
the data from two or more cross tab queries on the Report for a fixed
date.

I must be missing a several very important issues.

Again thanks for the guidance.

Marv

Allen,

Thanks for the directions. I let you know how I do.

Regards,

Marv

Create a form with two text boxes named (say) StartDate and EndDate.
Set their Format property to Short Date, so Access won't accept invalid
dates.
Save the form as (say) GetDates.

In your query, in the Criteria row under your date field, enter:
Between [Forms].[GetDates].[StartDate] And
[Forms].[GetDates].[EndDate]

Especially in a crosstab query, you need to declare these as
parameters.
In
query design view, choose Parameters from the Query menu. Access opens
a
dialog. Enter two lines:
[Forms].[GetDates].[StartDate] Date/Time
[Forms].[GetDates].[EndDate] Date/Time

Provided the form is open and the dates are entered, the query
should
now
read its parameters from the form instead of asking you for them again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I need to create an Access 2003 Summary Report with the following
specs.

1) User selected Date Range for the report.
2) The Reports displays totals for 15 categories with three to ten
items
in
each category.
3) All data is contained in three databases.

I created crosstab queries for two category and placed them in the
report.
They work fine if the date range is entered in the query.

What overall design approach is required to allow the user to
enter a
date
range for the entire report? Also what specific pitfalls might there
be?

This is strictly a volunteer project for a 501C non-profit
organization.

Thanks for your help

Marv Trott

 

[Allen Browne]

Yes, you do need one query.

Not sure why you want different fields like that though.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I guess I have been attempting to solve the report problem the wrong way.

If I understand where we are now it appears that I need to write one query
that will produce a series of numbers which represents the number of times
each of three to six different numbered entries occur in each of ten
different text fields that are taken from two databases for a selected
date
range. Then use this query as the record source for the report.

Example of data to be used in the Report

For the date range from 09/01/04 to 09/30/04
i.e.; Field 1 data from individual records 2,2,2 4,4 7,7,7: Result
displayed in report Self Employed 3 Home base Business 2 Store
Front
3
Field 2 data from individual records 1,1,1,1,1 3,3,3,3
6,6,6,6,6: Result displayed in report Newspapers 5 Radio 4 Internet 5
Field 3 .......etc
If this is what need to be done how is this accomplished in one query?

I have two books; Access Office 2003 Bible (Wiley) and Access 2003 (Que)
but
I have not been able to find the answers in either.

Thanks for your patience with my questions.

Marv

The RecordSource of your report will be whichever query provides the data
for it.

Many crosstab(?) queries supply the data for one report? A report can
have
only one RecordSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I fixed the GetDates Form but I need more help.

On the Report design screen.

1) How do I determine what should be used for the Record Source for the
report?

2) There will be data from many crosstable queries that needs to be
displayed in the report. What is an example of code to be entered into the
Control Source for each of text boxes on the report so the data in the
selected crosstab query for the selected data range will appear on the
report?

Thanks,

Marv

Hi Marv

The idea was to use an unbound form.
It does not need a RecordSource, and you don't need a tblGetDates.

You should be fine adding the Criteria to the crosstab as long as you
declare the parameters also.

BTW, since the 2 text boxes on the form are unbound, you need to stop the
user from entering invalid dates. You can do that by setting the
Format
property of the text boxes to Short Date.


Allen,

A problem occured when I ran the Form. It appears that I do not
understand
how to assign the Record Source for the form. I created a table called
tblGetDates and set this table as the Record Source for the form. I set
the
Control Source for the startdate and enddate to the field names in
tblGetDates. I can display both dates the Report but I cannot
display
the
data from the crosstab query ctqRace. I did not add the
Between[enter
StartDate....] as a condition in the ctqRace until I can at least
display
the data from two or more cross tab queries on the Report for a fixed
date.

I must be missing a several very important issues.

Again thanks for the guidance.

Marv

Allen,

Thanks for the directions. I let you know how I do.

Regards,

Marv

Create a form with two text boxes named (say) StartDate and EndDate.
Set their Format property to Short Date, so Access won't accept
invalid
dates.
Save the form as (say) GetDates.

In your query, in the Criteria row under your date field, enter:
Between [Forms].[GetDates].[StartDate] And
[Forms].[GetDates].[EndDate]

Especially in a crosstab query, you need to declare these as
parameters.
In
query design view, choose Parameters from the Query menu. Access
opens
a
dialog. Enter two lines:
[Forms].[GetDates].[StartDate] Date/Time
[Forms].[GetDates].[EndDate] Date/Time

Provided the form is open and the dates are entered, the query
should
now
read its parameters from the form instead of asking you for them
again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I need to create an Access 2003 Summary Report with the following
specs.

1) User selected Date Range for the report.
2) The Reports displays totals for 15 categories with three to ten
items
in
each category.
3) All data is contained in three databases.

I created crosstab queries for two category and placed them in the
report.
They work fine if the date range is entered in the query.

What overall design approach is required to allow the user to
enter
a
date
range for the entire report? Also what specific pitfalls might
there
be?

This is strictly a volunteer project for a 501C non-profit
organization.

Thanks for your help

Marv Trott

 

[Marv Trott]

Allen,

With the information you have given me I believe that I have determined that
the only controls that can be used in a report have to come from the query
attached to the report so I will have write VB code to summarize the data I
am looking for and place it in the query. Then when I use the Form you
suggested for the date range selection I will be able to display all of the
consolidated information in one report.

Thanks for your help

Regards,

Marv

Yes, you do need one query.

Not sure why you want different fields like that though.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I guess I have been attempting to solve the report problem the wrong way.

If I understand where we are now it appears that I need to write one query
that will produce a series of numbers which represents the number of times
each of three to six different numbered entries occur in each of ten
different text fields that are taken from two databases for a selected
date
range. Then use this query as the record source for the report.

Example of data to be used in the Report

For the date range from 09/01/04 to 09/30/04
i.e.; Field 1 data from individual records 2,2,2 4,4 7,7,7: Result
displayed in report Self Employed 3 Home base Business 2 Store
Front
3
Field 2 data from individual records 1,1,1,1,1 3,3,3,3
6,6,6,6,6: Result displayed in report Newspapers 5 Radio 4 Internet 5
Field 3 .......etc
If this is what need to be done how is this accomplished in one query?

I have two books; Access Office 2003 Bible (Wiley) and Access 2003 (Que)
but
I have not been able to find the answers in either.

Thanks for your patience with my questions.

Marv

The RecordSource of your report will be whichever query provides the data
for it.

Many crosstab(?) queries supply the data for one report? A report can
have
only one RecordSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I fixed the GetDates Form but I need more help.

On the Report design screen.

1) How do I determine what should be used for the Record Source for the
report?

2) There will be data from many crosstable queries that needs to be
displayed in the report. What is an example of code to be entered

into
the

Control Source for each of text boxes on the report so the data in the
selected crosstab query for the selected data range will appear on the
report?

Thanks,

Marv

Hi Marv

The idea was to use an unbound form.
It does not need a RecordSource, and you don't need a tblGetDates.

You should be fine adding the Criteria to the crosstab as long as you
declare the parameters also.

BTW, since the 2 text boxes on the form are unbound, you need to

stop
the

user from entering invalid dates. You can do that by setting the
Format
property of the text boxes to Short Date.


Allen,

A problem occured when I ran the Form. It appears that I do not
understand
how to assign the Record Source for the form. I created a table called
tblGetDates and set this table as the Record Source for the form.

I
set

the
Control Source for the startdate and enddate to the field names in
tblGetDates. I can display both dates the Report but I cannot
display
the
data from the crosstab query ctqRace. I did not add the
Between[enter
StartDate....] as a condition in the ctqRace until I can at least
display
the data from two or more cross tab queries on the Report for a fixed
date.

I must be missing a several very important issues.

Again thanks for the guidance.

Marv

Allen,

Thanks for the directions. I let you know how I do.

Regards,

Marv

Create a form with two text boxes named (say) StartDate and EndDate.
Set their Format property to Short Date, so Access won't accept
invalid
dates.
Save the form as (say) GetDates.

In your query, in the Criteria row under your date field, enter:
Between [Forms].[GetDates].[StartDate] And
[Forms].[GetDates].[EndDate]

Especially in a crosstab query, you need to declare these as
parameters.
In
query design view, choose Parameters from the Query menu. Access
opens
a
dialog. Enter two lines:
[Forms].[GetDates].[StartDate] Date/Time
[Forms].[GetDates].[EndDate] Date/Time

Provided the form is open and the dates are entered, the query
should
now
read its parameters from the form instead of asking you for them
again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I need to create an Access 2003 Summary Report with the following
specs.

1) User selected Date Range for the report.
2) The Reports displays totals for 15 categories with three

to
ten

items
in
each category.
3) All data is contained in three databases.

I created crosstab queries for two category and placed them

in
the

report.
They work fine if the date range is entered in the query.

What overall design approach is required to allow the user to
enter
a
date
range for the entire report? Also what specific pitfalls might
there
be?

This is strictly a volunteer project for a 501C non-profit
organization.

Thanks for your help

Marv Trott