Problem w/date parameter report

[Dcbrown428]

My database is set up to track the traffic that visit the model homes in
several new housing developments. Daily I keep track of how many are
potential home buyers, how many came with a realtor, how many contracts were
written, etc. I have 6 communities that I am tracking. I have set up
individual reports for each community to keep track of the traffic totals
based on a date parameter and these work great. I also needed a report that
would list (by community) the total for each community for a date range
parameter and then the total of all the communities. The only way I have
figured out to make this work is to create a subreport for each community
(again based on date range parameter) and insert then into the page detail
portion of the report. I also created a subreport to consolidate all the
totals for that date range and inserted that into the report footer. When I
pull this combined report (with all the subreports in it) I have to enter the
date range 7 times! That's bad enough, but when I go to print it I have to
do the same thing again. If there's a better way to set up this report, I'm
open to suggestions, but keep in mind that I'm probably a novice at best so
keep it simple! If I haven't provided enough detail please let me know what
else would be helpful. Thanks!

 

[Marshall Barton]

My database is set up to track the traffic that visit the model homes in
several new housing developments. Daily I keep track of how many are
potential home buyers, how many came with a realtor, how many contracts were
written, etc. I have 6 communities that I am tracking. I have set up
individual reports for each community to keep track of the traffic totals
based on a date parameter and these work great. I also needed a report that
would list (by community) the total for each community for a date range
parameter and then the total of all the communities. The only way I have
figured out to make this work is to create a subreport for each community
(again based on date range parameter) and insert then into the page detail
portion of the report. I also created a subreport to consolidate all the
totals for that date range and inserted that into the report footer. When I
pull this combined report (with all the subreports in it) I have to enter the
date range 7 times! That's bad enough, but when I go to print it I have to
do the same thing again. If there's a better way to set up this report, I'm
open to suggestions, but keep in mind that I'm probably a novice at best so
keep it simple!

Using parameter prompts in a query is really just a quick
and dirty way to get a query (and its form/report) up and
running. A more robust approach is to create a form with a
command button that initiates your report. This way the
form can have text boxes for the date parameters and the
query can refer to them whenever and as many times as needed
without further user interaction. Then change the query
parameters from [Enter start date] to something like:
Forms!nameofform.startdatetextbox

You can use the command button wizard to create the event
procedure to open the report.

 

[Dcbrown428]

I'm having a problem with changing the parameters in my query to make this
form work. I had my query set up using 'Between[startdate] and [enddate]'.
In my form I have two text boxes. One for the start date and one for the end
date. How do I set up both of these in my query?

My database is set up to track the traffic that visit the model homes in
several new housing developments. Daily I keep track of how many are
potential home buyers, how many came with a realtor, how many contracts were
written, etc. I have 6 communities that I am tracking. I have set up
individual reports for each community to keep track of the traffic totals
based on a date parameter and these work great. I also needed a report that
would list (by community) the total for each community for a date range
parameter and then the total of all the communities. The only way I have
figured out to make this work is to create a subreport for each community
(again based on date range parameter) and insert then into the page detail
portion of the report. I also created a subreport to consolidate all the
totals for that date range and inserted that into the report footer. When I
pull this combined report (with all the subreports in it) I have to enter the
date range 7 times! That's bad enough, but when I go to print it I have to
do the same thing again. If there's a better way to set up this report, I'm
open to suggestions, but keep in mind that I'm probably a novice at best so
keep it simple!

Using parameter prompts in a query is really just a quick
and dirty way to get a query (and its form/report) up and
running. A more robust approach is to create a form with a
command button that initiates your report. This way the
form can have text boxes for the date parameters and the
query can refer to them whenever and as many times as needed
without further user interaction. Then change the query
parameters from [Enter start date] to something like:
Forms!nameofform.startdatetextbox

You can use the command button wizard to create the event
procedure to open the report.

 

[Marshall Barton]

The criteria in the query would look like:

Between Forms![name of form].[name ot start text box]
And Forms![name of form].[name ot end text box]

all on one line of course.
--
Marsh
MVP [MS Access]

I'm having a problem with changing the parameters in my query to make this
form work. I had my query set up using 'Between[startdate] and [enddate]'.
In my form I have two text boxes. One for the start date and one for the end
date. How do I set up both of these in my query?

My database is set up to track the traffic that visit the model homes in
several new housing developments. Daily I keep track of how many are
potential home buyers, how many came with a realtor, how many contracts were
written, etc. I have 6 communities that I am tracking. I have set up
individual reports for each community to keep track of the traffic totals
based on a date parameter and these work great. I also needed a report that
would list (by community) the total for each community for a date range
parameter and then the total of all the communities. The only way I have
figured out to make this work is to create a subreport for each community
(again based on date range parameter) and insert then into the page detail
portion of the report. I also created a subreport to consolidate all the
totals for that date range and inserted that into the report footer. When I
pull this combined report (with all the subreports in it) I have to enter the
date range 7 times! That's bad enough, but when I go to print it I have to
do the same thing again. If there's a better way to set up this report, I'm
open to suggestions, but keep in mind that I'm probably a novice at best so
keep it simple!

Using parameter prompts in a query is really just a quick
and dirty way to get a query (and its form/report) up and
running. A more robust approach is to create a form with a
command button that initiates your report. This way the
form can have text boxes for the date parameters and the
query can refer to them whenever and as many times as needed
without further user interaction. Then change the query
parameters from [Enter start date] to something like:
Forms!nameofform.startdatetextbox

You can use the command button wizard to create the event
procedure to open the report.

 

[Dcbrown428]

That works from my query, but I must have something formatted wrong in my
form because if I leave my text box unbound I can enter the dates, but when
the report is opened it has all zero's. At this point I have no doubt that I
should have set my report differently. Is there a way for me to create a
report that will sort by community and give me only the totals for each
column for each community and then a grand total as well for the date range
entered? I created the subreports because I couldn't figure any other way
around it. It works with the subreports, but because each subreport has date
parameters in the query for it, my report requires me to enter the date
parameters multiple times to open the report and again to print it.

The criteria in the query would look like:

Between Forms![name of form].[name ot start text box]
And Forms![name of form].[name ot end text box]

all on one line of course.
--
Marsh
MVP [MS Access]

I'm having a problem with changing the parameters in my query to make this
form work. I had my query set up using 'Between[startdate] and [enddate]'.
In my form I have two text boxes. One for the start date and one for the end
date. How do I set up both of these in my query?

Dcbrown428 wrote:

My database is set up to track the traffic that visit the model homes in
several new housing developments. Daily I keep track of how many are
potential home buyers, how many came with a realtor, how many contracts were
written, etc. I have 6 communities that I am tracking. I have set up
individual reports for each community to keep track of the traffic totals
based on a date parameter and these work great. I also needed a report that
would list (by community) the total for each community for a date range
parameter and then the total of all the communities. The only way I have
figured out to make this work is to create a subreport for each community
(again based on date range parameter) and insert then into the page detail
portion of the report. I also created a subreport to consolidate all the
totals for that date range and inserted that into the report footer. When I
pull this combined report (with all the subreports in it) I have to enter the
date range 7 times! That's bad enough, but when I go to print it I have to
do the same thing again. If there's a better way to set up this report, I'm
open to suggestions, but keep in mind that I'm probably a novice at best so
keep it simple!


Using parameter prompts in a query is really just a quick
and dirty way to get a query (and its form/report) up and
running. A more robust approach is to create a form with a
command button that initiates your report. This way the
form can have text boxes for the date parameters and the
query can refer to them whenever and as many times as needed
without further user interaction. Then change the query
parameters from [Enter start date] to something like:
Forms!nameofform.startdatetextbox

You can use the command button wizard to create the event
procedure to open the report.

 

[Marshall Barton]

I'll need more information about what the report is supposed
to do before I can speculate about how to accomplish it. In
general, most report are very sime to create - - - once you
create the right query for its record source. In this case
maybe all you need is to create a Totals type of query.
E.g.

SELECT Community,
Sum(flda) As totala,
Sum(fldb) As totalb,
. . .
FROM table
WHERE . . .
--
Marsh
MVP [MS Access]

That works from my query, but I must have something formatted wrong in my
form because if I leave my text box unbound I can enter the dates, but when
the report is opened it has all zero's. At this point I have no doubt that I
should have set my report differently. Is there a way for me to create a
report that will sort by community and give me only the totals for each
column for each community and then a grand total as well for the date range
entered? I created the subreports because I couldn't figure any other way
around it. It works with the subreports, but because each subreport has date
parameters in the query for it, my report requires me to enter the date
parameters multiple times to open the report and again to print it.

The criteria in the query would look like:

Between Forms![name of form].[name ot start text box]
And Forms![name of form].[name ot end text box]

all on one line of course.

I'm having a problem with changing the parameters in my query to make this
form work. I had my query set up using 'Between[startdate] and [enddate]'.
In my form I have two text boxes. One for the start date and one for the end
date. How do I set up both of these in my query?

:

Dcbrown428 wrote:

My database is set up to track the traffic that visit the model homes in
several new housing developments. Daily I keep track of how many are
potential home buyers, how many came with a realtor, how many contracts were
written, etc. I have 6 communities that I am tracking. I have set up
individual reports for each community to keep track of the traffic totals
based on a date parameter and these work great. I also needed a report that
would list (by community) the total for each community for a date range
parameter and then the total of all the communities. The only way I have
figured out to make this work is to create a subreport for each community
(again based on date range parameter) and insert then into the page detail
portion of the report. I also created a subreport to consolidate all the
totals for that date range and inserted that into the report footer. When I
pull this combined report (with all the subreports in it) I have to enter the
date range 7 times! That's bad enough, but when I go to print it I have to
do the same thing again. If there's a better way to set up this report, I'm
open to suggestions, but keep in mind that I'm probably a novice at best so
keep it simple!


Using parameter prompts in a query is really just a quick
and dirty way to get a query (and its form/report) up and
running. A more robust approach is to create a form with a
command button that initiates your report. This way the
form can have text boxes for the date parameters and the
query can refer to them whenever and as many times as needed
without further user interaction. Then change the query
parameters from [Enter start date] to something like:
Forms!nameofform.startdatetextbox

You can use the command button wizard to create the event
procedure to open the report.

 

[Dcbrown428]

I apologize for being such a nitwit, but you've lost me. When I create a
report, I use the wizard and create it based on a query. For the report in
question I have created 7 subreports based on 7 queries that give me totals
for each community based on the date criterea. I'm sure there's a way to do
this without all the subreports, but I couldn't figure it out so I resorted
to this method. I get the figures that I'm looking for, but each query has
date parameters and I'm being required to to put in the date ranges 7 times
to open the report and 7 more times to print it. I was using
Between[start]and[end] in each query. Is there a way to set up a query to
sort the criteria and give the totals by date range for each category. For
example: I want to a total of the traffic that visited 6 different
communities from 10/1/06 to 10/31/06. I need to show the total for each
community AND total of all communities combined. I have 5 columns of data
that I want to show the totals of for each community. My report will look
something like this:

Community totals for 10/1/06 - 10/31/06

Bordeaux Traffic w/realtor return lot hold contract
6 3 3 1
0

Blue Ridge Traffic w/realtor return lot hold contract
2 2 0 0
0

Totals 8 5 3 1
0

Hope this helps.

I'll need more information about what the report is supposed
to do before I can speculate about how to accomplish it. In
general, most report are very sime to create - - - once you
create the right query for its record source. In this case
maybe all you need is to create a Totals type of query.
E.g.

SELECT Community,
Sum(flda) As totala,
Sum(fldb) As totalb,
. . .
FROM table
WHERE . . .
--
Marsh
MVP [MS Access]

That works from my query, but I must have something formatted wrong in my
form because if I leave my text box unbound I can enter the dates, but when
the report is opened it has all zero's. At this point I have no doubt that I
should have set my report differently. Is there a way for me to create a
report that will sort by community and give me only the totals for each
column for each community and then a grand total as well for the date range
entered? I created the subreports because I couldn't figure any other way
around it. It works with the subreports, but because each subreport has date
parameters in the query for it, my report requires me to enter the date
parameters multiple times to open the report and again to print it.

The criteria in the query would look like:

Between Forms![name of form].[name ot start text box]
And Forms![name of form].[name ot end text box]

all on one line of course.


Dcbrown428 wrote:

I'm having a problem with changing the parameters in my query to make this
form work. I had my query set up using 'Between[startdate] and [enddate]'.
In my form I have two text boxes. One for the start date and one for the end
date. How do I set up both of these in my query?

:

Dcbrown428 wrote:

My database is set up to track the traffic that visit the model homes in
several new housing developments. Daily I keep track of how many are
potential home buyers, how many came with a realtor, how many contracts were
written, etc. I have 6 communities that I am tracking. I have set up
individual reports for each community to keep track of the traffic totals
based on a date parameter and these work great. I also needed a report that
would list (by community) the total for each community for a date range
parameter and then the total of all the communities. The only way I have
figured out to make this work is to create a subreport for each community
(again based on date range parameter) and insert then into the page detail
portion of the report. I also created a subreport to consolidate all the
totals for that date range and inserted that into the report footer. When I
pull this combined report (with all the subreports in it) I have to enter the
date range 7 times! That's bad enough, but when I go to print it I have to
do the same thing again. If there's a better way to set up this report, I'm
open to suggestions, but keep in mind that I'm probably a novice at best so
keep it simple!


Using parameter prompts in a query is really just a quick
and dirty way to get a query (and its form/report) up and
running. A more robust approach is to create a form with a
command button that initiates your report. This way the
form can have text boxes for the date parameters and the
query can refer to them whenever and as many times as needed
without further user interaction. Then change the query
parameters from [Enter start date] to something like:
Forms!nameofform.startdatetextbox

You can use the command button wizard to create the event
procedure to open the report.

 

[Marshall Barton]

It think it is a matter of you getting the cart before the
horse to start with the report and then try to mangle it to
find the data.

Let me try to say it another way. First create ONE query
that includes all the data needed by the report. This is
usually a fairly simple to do by just adding the related
tables to the query design grid and dragging the related
fields from one table to the other. You should only resort
to subreports when the query runs into a logical conflict.
The report wizard is often inadequate for anything beyond
the simplest reports so don't waste your time trying to
contort its options to meet your needs. Once you get the
query right, the report will probably be so simple that the
wizard might provide a small convenience.

If all that doesn't make any sense to you, post your tables,
their fields and how the tables are related. Then we can
try to help you create the needed query.
--
Marsh
MVP [MS Access]

I apologize for being such a nitwit, but you've lost me. When I create a
report, I use the wizard and create it based on a query. For the report in
question I have created 7 subreports based on 7 queries that give me totals
for each community based on the date criterea. I'm sure there's a way to do
this without all the subreports, but I couldn't figure it out so I resorted
to this method. I get the figures that I'm looking for, but each query has
date parameters and I'm being required to to put in the date ranges 7 times
to open the report and 7 more times to print it. I was using
Between[start]and[end] in each query. Is there a way to set up a query to
sort the criteria and give the totals by date range for each category. For
example: I want to a total of the traffic that visited 6 different
communities from 10/1/06 to 10/31/06. I need to show the total for each
community AND total of all communities combined. I have 5 columns of data
that I want to show the totals of for each community. My report will look
something like this:

Community totals for 10/1/06 - 10/31/06

Bordeaux Traffic w/realtor return lot hold contract
6 3 3 1
0

Blue Ridge Traffic w/realtor return lot hold contract
2 2 0 0
0

Totals 8 5 3 1
0

Hope this helps.

I'll need more information about what the report is supposed
to do before I can speculate about how to accomplish it. In
general, most report are very sime to create - - - once you
create the right query for its record source. In this case
maybe all you need is to create a Totals type of query.
E.g.

SELECT Community,
Sum(flda) As totala,
Sum(fldb) As totalb,
. . .
FROM table
WHERE . . .
--

That works from my query, but I must have something formatted wrong in my
form because if I leave my text box unbound I can enter the dates, but when
the report is opened it has all zero's. At this point I have no doubt that I
should have set my report differently. Is there a way for me to create a
report that will sort by community and give me only the totals for each
column for each community and then a grand total as well for the date range
entered? I created the subreports because I couldn't figure any other way
around it. It works with the subreports, but because each subreport has date
parameters in the query for it, my report requires me to enter the date
parameters multiple times to open the report and again to print it.

:

The criteria in the query would look like:

Between Forms![name of form].[name ot start text box]
And Forms![name of form].[name ot end text box]

all on one line of course.


Dcbrown428 wrote:

I'm having a problem with changing the parameters in my query to make this
form work. I had my query set up using 'Between[startdate] and [enddate]'.
In my form I have two text boxes. One for the start date and one for the end
date. How do I set up both of these in my query?

:

Dcbrown428 wrote:

My database is set up to track the traffic that visit the model homes in
several new housing developments. Daily I keep track of how many are
potential home buyers, how many came with a realtor, how many contracts were
written, etc. I have 6 communities that I am tracking. I have set up
individual reports for each community to keep track of the traffic totals
based on a date parameter and these work great. I also needed a report that
would list (by community) the total for each community for a date range
parameter and then the total of all the communities. The only way I have
figured out to make this work is to create a subreport for each community
(again based on date range parameter) and insert then into the page detail
portion of the report. I also created a subreport to consolidate all the
totals for that date range and inserted that into the report footer. When I
pull this combined report (with all the subreports in it) I have to enter the
date range 7 times! That's bad enough, but when I go to print it I have to
do the same thing again. If there's a better way to set up this report, I'm
open to suggestions, but keep in mind that I'm probably a novice at best so
keep it simple!


Using parameter prompts in a query is really just a quick
and dirty way to get a query (and its form/report) up and
running. A more robust approach is to create a form with a
command button that initiates your report. This way the
form can have text boxes for the date parameters and the
query can refer to them whenever and as many times as needed
without further user interaction. Then change the query
parameters from [Enter start date] to something like:
Forms!nameofform.startdatetextbox

You can use the command button wizard to create the event
procedure to open the report.

 

[Dcbrown428]

I think I need to have separate tables for each community to show it all
separately in my query. I'm going to brainstorm this weekend and see if I
can restructure this database and make it work properly. If I fail, I'll be
posting again Monday! Thank you so much for your patience and assistance!

It think it is a matter of you getting the cart before the
horse to start with the report and then try to mangle it to
find the data.

Let me try to say it another way. First create ONE query
that includes all the data needed by the report. This is
usually a fairly simple to do by just adding the related
tables to the query design grid and dragging the related
fields from one table to the other. You should only resort
to subreports when the query runs into a logical conflict.
The report wizard is often inadequate for anything beyond
the simplest reports so don't waste your time trying to
contort its options to meet your needs. Once you get the
query right, the report will probably be so simple that the
wizard might provide a small convenience.

If all that doesn't make any sense to you, post your tables,
their fields and how the tables are related. Then we can
try to help you create the needed query.
--
Marsh
MVP [MS Access]

I apologize for being such a nitwit, but you've lost me. When I create a
report, I use the wizard and create it based on a query. For the report in
question I have created 7 subreports based on 7 queries that give me totals
for each community based on the date criterea. I'm sure there's a way to do
this without all the subreports, but I couldn't figure it out so I resorted
to this method. I get the figures that I'm looking for, but each query has
date parameters and I'm being required to to put in the date ranges 7 times
to open the report and 7 more times to print it. I was using
Between[start]and[end] in each query. Is there a way to set up a query to
sort the criteria and give the totals by date range for each category. For
example: I want to a total of the traffic that visited 6 different
communities from 10/1/06 to 10/31/06. I need to show the total for each
community AND total of all communities combined. I have 5 columns of data
that I want to show the totals of for each community. My report will look
something like this:

Community totals for 10/1/06 - 10/31/06

Bordeaux Traffic w/realtor return lot hold contract
6 3 3 1
0

Blue Ridge Traffic w/realtor return lot hold contract
2 2 0 0
0

Totals 8 5 3 1
0

Hope this helps.

I'll need more information about what the report is supposed
to do before I can speculate about how to accomplish it. In
general, most report are very sime to create - - - once you
create the right query for its record source. In this case
maybe all you need is to create a Totals type of query.
E.g.

SELECT Community,
Sum(flda) As totala,
Sum(fldb) As totalb,
. . .
FROM table
WHERE . . .
--


Dcbrown428 wrote:

That works from my query, but I must have something formatted wrong in my
form because if I leave my text box unbound I can enter the dates, but when
the report is opened it has all zero's. At this point I have no doubt that I
should have set my report differently. Is there a way for me to create a
report that will sort by community and give me only the totals for each
column for each community and then a grand total as well for the date range
entered? I created the subreports because I couldn't figure any other way
around it. It works with the subreports, but because each subreport has date
parameters in the query for it, my report requires me to enter the date
parameters multiple times to open the report and again to print it.

:

The criteria in the query would look like:

Between Forms![name of form].[name ot start text box]
And Forms![name of form].[name ot end text box]

all on one line of course.


Dcbrown428 wrote:

I'm having a problem with changing the parameters in my query to make this
form work. I had my query set up using 'Between[startdate] and [enddate]'.
In my form I have two text boxes. One for the start date and one for the end
date. How do I set up both of these in my query?

:

Dcbrown428 wrote:

My database is set up to track the traffic that visit the model homes in
several new housing developments. Daily I keep track of how many are
potential home buyers, how many came with a realtor, how many contracts were
written, etc. I have 6 communities that I am tracking. I have set up
individual reports for each community to keep track of the traffic totals
based on a date parameter and these work great. I also needed a report that
would list (by community) the total for each community for a date range
parameter and then the total of all the communities. The only way I have
figured out to make this work is to create a subreport for each community
(again based on date range parameter) and insert then into the page detail
portion of the report. I also created a subreport to consolidate all the
totals for that date range and inserted that into the report footer. When I
pull this combined report (with all the subreports in it) I have to enter the
date range 7 times! That's bad enough, but when I go to print it I have to
do the same thing again. If there's a better way to set up this report, I'm
open to suggestions, but keep in mind that I'm probably a novice at best so
keep it simple!


Using parameter prompts in a query is really just a quick
and dirty way to get a query (and its form/report) up and
running. A more robust approach is to create a form with a
command button that initiates your report. This way the
form can have text boxes for the date parameters and the
query can refer to them whenever and as many times as needed
without further user interaction. Then change the query
parameters from [Enter start date] to something like:
Forms!nameofform.startdatetextbox

You can use the command button wizard to create the event
procedure to open the report.

 

[Marshall Barton]

I want to say NO NO NO. But, it is remotely possible that
there is a valid reason to have multiple tables for the
communuty entity. It better be one really great reason
though.

Please post the table infomation I requested so we can
review it and if it's ok, try to come up with an appropriate
query.
--
Marsh
MVP [MS Access]

I think I need to have separate tables for each community to show it all
separately in my query. I'm going to brainstorm this weekend and see if I
can restructure this database and make it work properly. If I fail, I'll be
posting again Monday! Thank you so much for your patience and assistance!

It think it is a matter of you getting the cart before the
horse to start with the report and then try to mangle it to
find the data.

Let me try to say it another way. First create ONE query
that includes all the data needed by the report. This is
usually a fairly simple to do by just adding the related
tables to the query design grid and dragging the related
fields from one table to the other. You should only resort
to subreports when the query runs into a logical conflict.
The report wizard is often inadequate for anything beyond
the simplest reports so don't waste your time trying to
contort its options to meet your needs. Once you get the
query right, the report will probably be so simple that the
wizard might provide a small convenience.

If all that doesn't make any sense to you, post your tables,
their fields and how the tables are related. Then we can
try to help you create the needed query.

I apologize for being such a nitwit, but you've lost me. When I create a
report, I use the wizard and create it based on a query. For the report in
question I have created 7 subreports based on 7 queries that give me totals
for each community based on the date criterea. I'm sure there's a way to do
this without all the subreports, but I couldn't figure it out so I resorted
to this method. I get the figures that I'm looking for, but each query has
date parameters and I'm being required to to put in the date ranges 7 times
to open the report and 7 more times to print it. I was using
Between[start]and[end] in each query. Is there a way to set up a query to
sort the criteria and give the totals by date range for each category. For
example: I want to a total of the traffic that visited 6 different
communities from 10/1/06 to 10/31/06. I need to show the total for each
community AND total of all communities combined. I have 5 columns of data
that I want to show the totals of for each community. My report will look
something like this:

Community totals for 10/1/06 - 10/31/06

Bordeaux Traffic w/realtor return lot hold contract
6 3 3 1
0

Blue Ridge Traffic w/realtor return lot hold contract
2 2 0 0
0

Totals 8 5 3 1
0

Hope this helps.

:

I'll need more information about what the report is supposed
to do before I can speculate about how to accomplish it. In
general, most report are very sime to create - - - once you
create the right query for its record source. In this case
maybe all you need is to create a Totals type of query.
E.g.

SELECT Community,
Sum(flda) As totala,
Sum(fldb) As totalb,
. . .
FROM table
WHERE . . .
--


Dcbrown428 wrote:

That works from my query, but I must have something formatted wrong in my
form because if I leave my text box unbound I can enter the dates, but when
the report is opened it has all zero's. At this point I have no doubt that I
should have set my report differently. Is there a way for me to create a
report that will sort by community and give me only the totals for each
column for each community and then a grand total as well for the date range
entered? I created the subreports because I couldn't figure any other way
around it. It works with the subreports, but because each subreport has date
parameters in the query for it, my report requires me to enter the date
parameters multiple times to open the report and again to print it.

:

The criteria in the query would look like:

Between Forms![name of form].[name ot start text box]
And Forms![name of form].[name ot end text box]

all on one line of course.


Dcbrown428 wrote:

I'm having a problem with changing the parameters in my query to make this
form work. I had my query set up using 'Between[startdate] and [enddate]'.
In my form I have two text boxes. One for the start date and one for the end
date. How do I set up both of these in my query?

:

Dcbrown428 wrote:

My database is set up to track the traffic that visit the model homes in
several new housing developments. Daily I keep track of how many are
potential home buyers, how many came with a realtor, how many contracts were
written, etc. I have 6 communities that I am tracking. I have set up
individual reports for each community to keep track of the traffic totals
based on a date parameter and these work great. I also needed a report that
would list (by community) the total for each community for a date range
parameter and then the total of all the communities. The only way I have
figured out to make this work is to create a subreport for each community
(again based on date range parameter) and insert then into the page detail
portion of the report. I also created a subreport to consolidate all the
totals for that date range and inserted that into the report footer. When I
pull this combined report (with all the subreports in it) I have to enter the
date range 7 times! That's bad enough, but when I go to print it I have to
do the same thing again. If there's a better way to set up this report, I'm
open to suggestions, but keep in mind that I'm probably a novice at best so
keep it simple!


Using parameter prompts in a query is really just a quick
and dirty way to get a query (and its form/report) up and
running. A more robust approach is to create a form with a
command button that initiates your report. This way the
form can have text boxes for the date parameters and the
query can refer to them whenever and as many times as needed
without further user interaction. Then change the query
parameters from [Enter start date] to something like:
Forms!nameofform.startdatetextbox

You can use the command button wizard to create the event
procedure to open the report.