Sum fields with condition

[Jason]

In my report, I have the field names: RSM, Sales Opportunity, and Sales
Converted. I need to be able to find the sum of the Sales Opportunity or
each RSM (I can use the same formula for the Sales Converted easily enough).
For example,
RSM Sales Opportunity
John 2000
Steve 5000
John 1500
Kevin 1800
John 3000

So, the total sales opportunity for John is 6500.

Basically what I am doing is using a report generated from a query that
pulls all of the record for a set date range, but in the header of this I
want to provide a the bottom line up front so that each RSM knows where they
stand.


I think what I am looking for is similar to

=Sum([Sales Opportunity], IIF([RSM] = "John", [Amount], 0))

but this doesn't work. Any suggestions or help? Thanks

 

[Marshall Barton]

In my report, I have the field names: RSM, Sales Opportunity, and Sales
Converted. I need to be able to find the sum of the Sales Opportunity or
each RSM (I can use the same formula for the Sales Converted easily enough).
For example,
RSM Sales Opportunity
John 2000
Steve 5000
John 1500
Kevin 1800
John 3000

So, the total sales opportunity for John is 6500.

Basically what I am doing is using a report generated from a query that
pulls all of the record for a set date range, but in the header of this I
want to provide a the bottom line up front so that each RSM knows where they
stand.


I think what I am looking for is similar to

=Sum([Sales Opportunity], IIF([RSM] = "John", [Amount], 0))

Without having any idea what [Sales Opportunity] and Amount
are supposed to represent, I think that would be:

=Sum(IIF([RSM] = "John", [Amount], 0))

OTOH, if the report needs to do this for all the RSMs, then
you should create a Totals type query that does all the
calculations:
SELECT RSM, Sum(Amount) As TotalOpportunity
FROM the table
WHERE ...
GROUP BY RSM

and use this query as the record source for a simple
subreport.

 

[Jason]

Sorry, a better explanation may be that I need the sum of all of the amounts
under "Sales Opportunity" that correlate with a specific entry in the "RSM"
field. I do need this for each RSM. So, if I understand you correctly, the
best way is to create a seperate report for each RSM and incorporate them as
a sub report? If so, how do I go about setting that up. The main report
will be based on a specific date range, generally a month. How do I make
sure that the queries for the RSM's will provide the correct info when
included in the main report? I still am learning a lot, so this isn't coming
to me right away and the last string you left has me a little puzzled.
Thanks.

In my report, I have the field names: RSM, Sales Opportunity, and Sales
Converted. I need to be able to find the sum of the Sales Opportunity or
each RSM (I can use the same formula for the Sales Converted easily enough).
For example,
RSM Sales Opportunity
John 2000
Steve 5000
John 1500
Kevin 1800
John 3000

So, the total sales opportunity for John is 6500.

Basically what I am doing is using a report generated from a query that
pulls all of the record for a set date range, but in the header of this I
want to provide a the bottom line up front so that each RSM knows where they
stand.


I think what I am looking for is similar to

=Sum([Sales Opportunity], IIF([RSM] = "John", [Amount], 0))

Without having any idea what [Sales Opportunity] and Amount
are supposed to represent, I think that would be:

=Sum(IIF([RSM] = "John", [Amount], 0))

OTOH, if the report needs to do this for all the RSMs, then
you should create a Totals type query that does all the
calculations:
SELECT RSM, Sum(Amount) As TotalOpportunity
FROM the table
WHERE ...
GROUP BY RSM

and use this query as the record source for a simple
subreport.

 

[Jason]

Ok, I tried to set up a query for just the RSM, Sales Opportunity and
Converted Sales, but every attempt at adding an expression to another field
to simply get the sum of each Sales field, I got an error that said I tried
to execute a query that does not include the specified expression 'RSM' as
part of an aggregate function. I have no idea what that means. Please help.

In my report, I have the field names: RSM, Sales Opportunity, and Sales
Converted. I need to be able to find the sum of the Sales Opportunity or
each RSM (I can use the same formula for the Sales Converted easily enough).
For example,
RSM Sales Opportunity
John 2000
Steve 5000
John 1500
Kevin 1800
John 3000

So, the total sales opportunity for John is 6500.

Basically what I am doing is using a report generated from a query that
pulls all of the record for a set date range, but in the header of this I
want to provide a the bottom line up front so that each RSM knows where they
stand.


I think what I am looking for is similar to

=Sum([Sales Opportunity], IIF([RSM] = "John", [Amount], 0))

Without having any idea what [Sales Opportunity] and Amount
are supposed to represent, I think that would be:

=Sum(IIF([RSM] = "John", [Amount], 0))

OTOH, if the report needs to do this for all the RSMs, then
you should create a Totals type query that does all the
calculations:
SELECT RSM, Sum(Amount) As TotalOpportunity
FROM the table
WHERE ...
GROUP BY RSM

and use this query as the record source for a simple
subreport.

 

[Marshall Barton]

I'm not sure that I am getting the idea across. Let's say
you want a report that sumarizes all the Sales Opportunities
and Sales Converted for all RSMs over the same date range as
your existing report. Let's also say that your existing
report's record source query is named qryRSM

Then this new report's record source query would be:

SELECT RSM, Sum([Sales Opportunity]) As TotalOpportunity,
Sum([Sales Converted]) As TotalConverted
FROM qryRSM
GROUP BY RSM

Run this query and make sure it displays all the required
summary totals for all the RSMs.

Next, create a new report based on the new query. This new
report would have only the detail sction with three text
boxes bound to the RSM, TotalOpportunity and TotalConverted
fields. Preview this summary report to make sure it looks
the way you want. When you have it displaying the way you
want, close and save it. Then drag and drop it from the db
window into your existing report's header section.

If all that works and you need more help specifying the date
range, post back with a Copy/Paste of the main report's
record source query.
--
Marsh
MVP [MS Access]

Sorry, a better explanation may be that I need the sum of all of the amounts
under "Sales Opportunity" that correlate with a specific entry in the "RSM"
field. I do need this for each RSM. So, if I understand you correctly, the
best way is to create a seperate report for each RSM and incorporate them as
a sub report? If so, how do I go about setting that up. The main report
will be based on a specific date range, generally a month. How do I make
sure that the queries for the RSM's will provide the correct info when
included in the main report?

In my report, I have the field names: RSM, Sales Opportunity, and Sales
Converted. I need to be able to find the sum of the Sales Opportunity or
each RSM (I can use the same formula for the Sales Converted easily enough).
For example,
RSM Sales Opportunity
John 2000
Steve 5000
John 1500
Kevin 1800
John 3000

So, the total sales opportunity for John is 6500.

Basically what I am doing is using a report generated from a query that
pulls all of the record for a set date range, but in the header of this I
want to provide a the bottom line up front so that each RSM knows where they
stand.


I think what I am looking for is similar to

=Sum([Sales Opportunity], IIF([RSM] = "John", [Amount], 0))

Without having any idea what [Sales Opportunity] and Amount
are supposed to represent, I think that would be:

=Sum(IIF([RSM] = "John", [Amount], 0))

OTOH, if the report needs to do this for all the RSMs, then
you should create a Totals type query that does all the
calculations:
SELECT RSM, Sum(Amount) As TotalOpportunity
FROM the table
WHERE ...
GROUP BY RSM

and use this query as the record source for a simple
subreport.

 

[Jason]

Ok, I understand that I need a new query and that I need to use that query to
generate what will be a sub-report in my existing report. Where I am lost is
how to create a query using the data string you provided. The only ways I
have done queries is using the wizard or in design view pulling fields from
specific tables. I attempted to make a query that totaled these fields for
me, but had no success. I got an error that said I tried to execute a query
that does not include the specified expression 'RSM' as part of an aggregate
function. I have no idea what that means. Do I have to place the data
string you provided in the code builder? I'm sure it's frustrating having to
deal with such a novice, but I really appreciate the help.

The current report's data source is DateRangeQuery. I built this query with
the wizard and simply added "Between [Enter Start Date] and [Enter End Date]"
into the criteria for the Date Recieved field.

I'm not sure that I am getting the idea across. Let's say
you want a report that sumarizes all the Sales Opportunities
and Sales Converted for all RSMs over the same date range as
your existing report. Let's also say that your existing
report's record source query is named qryRSM

Then this new report's record source query would be:

SELECT RSM, Sum([Sales Opportunity]) As TotalOpportunity,
Sum([Sales Converted]) As TotalConverted
FROM qryRSM
GROUP BY RSM

Run this query and make sure it displays all the required
summary totals for all the RSMs.

Next, create a new report based on the new query. This new
report would have only the detail sction with three text
boxes bound to the RSM, TotalOpportunity and TotalConverted
fields. Preview this summary report to make sure it looks
the way you want. When you have it displaying the way you
want, close and save it. Then drag and drop it from the db
window into your existing report's header section.

If all that works and you need more help specifying the date
range, post back with a Copy/Paste of the main report's
record source query.
--
Marsh
MVP [MS Access]

Sorry, a better explanation may be that I need the sum of all of the amounts
under "Sales Opportunity" that correlate with a specific entry in the "RSM"
field. I do need this for each RSM. So, if I understand you correctly, the
best way is to create a seperate report for each RSM and incorporate them as
a sub report? If so, how do I go about setting that up. The main report
will be based on a specific date range, generally a month. How do I make
sure that the queries for the RSM's will provide the correct info when
included in the main report?

Jason wrote:

In my report, I have the field names: RSM, Sales Opportunity, and Sales
Converted. I need to be able to find the sum of the Sales Opportunity or
each RSM (I can use the same formula for the Sales Converted easily enough).
For example,
RSM Sales Opportunity
John 2000
Steve 5000
John 1500
Kevin 1800
John 3000

So, the total sales opportunity for John is 6500.

Basically what I am doing is using a report generated from a query that
pulls all of the record for a set date range, but in the header of this I
want to provide a the bottom line up front so that each RSM knows where they
stand.


I think what I am looking for is similar to

=Sum([Sales Opportunity], IIF([RSM] = "John", [Amount], 0))


Without having any idea what [Sales Opportunity] and Amount
are supposed to represent, I think that would be:

=Sum(IIF([RSM] = "John", [Amount], 0))

OTOH, if the report needs to do this for all the RSMs, then
you should create a Totals type query that does all the
calculations:
SELECT RSM, Sum(Amount) As TotalOpportunity
FROM the table
WHERE ...
GROUP BY RSM

and use this query as the record source for a simple
subreport.

 

[Marshall Barton]

Now that I know the name of your existing query, the new
query would be:

SELECT RSM, Sum([Sales Opportunity]) As TotalOpportunity,
Sum([Sales Converted]) As TotalConverted
FROM DateRangeQuery
GROUP BY RSM

You should understand that the wizards only give you a
helping hand and that you frequently will need to modify
what a wizard generates to do exactly what you need.
Another important point is that the query designer is just a
semi graphical interface for creating a query, it is not the
query. The real query is the SQL statement that the query
designer creates from what you enter in the design grid. To
see the SQL statement you can use the View menu's SQL View
menu item.

Since the query design grid is rather difficult to express
in a text news group message, the standard is to post a
Copy/Paste of the SQL statement (i.e. the query's SQL view).
To create a query from my posted SQL statement, Create a
new query and immediately switch to SQL view and then Copy
the SQL statement I posted and Paste it over the top of
whatever Access put there in its attempt to help you get
started. To see how the SQL statement could be entered in
the query designer, switch back to design view.

It would be a good homework assignment for you to study all
of your queries in both design and SQL view. You shouldn't
find it too difficult to gain an elementary understanding of
SQL, which is a good thing because the wizards are not all
that smart. The query designer can not be used to express
even moderately complex queries so, sooner rather than
later, you will need to use SQL directly.
--
Marsh
MVP [MS Access]

Ok, I understand that I need a new query and that I need to use that query to
generate what will be a sub-report in my existing report. Where I am lost is
how to create a query using the data string you provided. The only ways I
have done queries is using the wizard or in design view pulling fields from
specific tables. I attempted to make a query that totaled these fields for
me, but had no success. I got an error that said I tried to execute a query
that does not include the specified expression 'RSM' as part of an aggregate
function. I have no idea what that means. Do I have to place the data
string you provided in the code builder? I'm sure it's frustrating having to
deal with such a novice, but I really appreciate the help.

The current report's data source is DateRangeQuery. I built this query with
the wizard and simply added "Between [Enter Start Date] and [Enter End Date]"
into the criteria for the Date Recieved field.

I'm not sure that I am getting the idea across. Let's say
you want a report that sumarizes all the Sales Opportunities
and Sales Converted for all RSMs over the same date range as
your existing report. Let's also say that your existing
report's record source query is named qryRSM

Then this new report's record source query would be:

SELECT RSM, Sum([Sales Opportunity]) As TotalOpportunity,
Sum([Sales Converted]) As TotalConverted
FROM qryRSM
GROUP BY RSM

Run this query and make sure it displays all the required
summary totals for all the RSMs.

Next, create a new report based on the new query. This new
report would have only the detail sction with three text
boxes bound to the RSM, TotalOpportunity and TotalConverted
fields. Preview this summary report to make sure it looks
the way you want. When you have it displaying the way you
want, close and save it. Then drag and drop it from the db
window into your existing report's header section.

If all that works and you need more help specifying the date
range, post back with a Copy/Paste of the main report's
record source query.

 

[Jason]

Awesome!! That's why you guys rock. Ok, so now the new report does what I
want, but when I add it as a sub report to the existing report and try to
view it, I have to enter the start and end dates three times. Can I set this
up so I only have to enter the dates once? Thanks again.

Now that I know the name of your existing query, the new
query would be:

SELECT RSM, Sum([Sales Opportunity]) As TotalOpportunity,
Sum([Sales Converted]) As TotalConverted
FROM DateRangeQuery
GROUP BY RSM

You should understand that the wizards only give you a
helping hand and that you frequently will need to modify
what a wizard generates to do exactly what you need.
Another important point is that the query designer is just a
semi graphical interface for creating a query, it is not the
query. The real query is the SQL statement that the query
designer creates from what you enter in the design grid. To
see the SQL statement you can use the View menu's SQL View
menu item.

Since the query design grid is rather difficult to express
in a text news group message, the standard is to post a
Copy/Paste of the SQL statement (i.e. the query's SQL view).
To create a query from my posted SQL statement, Create a
new query and immediately switch to SQL view and then Copy
the SQL statement I posted and Paste it over the top of
whatever Access put there in its attempt to help you get
started. To see how the SQL statement could be entered in
the query designer, switch back to design view.

It would be a good homework assignment for you to study all
of your queries in both design and SQL view. You shouldn't
find it too difficult to gain an elementary understanding of
SQL, which is a good thing because the wizards are not all
that smart. The query designer can not be used to express
even moderately complex queries so, sooner rather than
later, you will need to use SQL directly.
--
Marsh
MVP [MS Access]

Ok, I understand that I need a new query and that I need to use that query to
generate what will be a sub-report in my existing report. Where I am lost is
how to create a query using the data string you provided. The only ways I
have done queries is using the wizard or in design view pulling fields from
specific tables. I attempted to make a query that totaled these fields for
me, but had no success. I got an error that said I tried to execute a query
that does not include the specified expression 'RSM' as part of an aggregate
function. I have no idea what that means. Do I have to place the data
string you provided in the code builder? I'm sure it's frustrating having to
deal with such a novice, but I really appreciate the help.

The current report's data source is DateRangeQuery. I built this query with
the wizard and simply added "Between [Enter Start Date] and [Enter End Date]"
into the criteria for the Date Recieved field.

I'm not sure that I am getting the idea across. Let's say
you want a report that sumarizes all the Sales Opportunities
and Sales Converted for all RSMs over the same date range as
your existing report. Let's also say that your existing
report's record source query is named qryRSM

Then this new report's record source query would be:

SELECT RSM, Sum([Sales Opportunity]) As TotalOpportunity,
Sum([Sales Converted]) As TotalConverted
FROM qryRSM
GROUP BY RSM

Run this query and make sure it displays all the required
summary totals for all the RSMs.

Next, create a new report based on the new query. This new
report would have only the detail sction with three text
boxes bound to the RSM, TotalOpportunity and TotalConverted
fields. Preview this summary report to make sure it looks
the way you want. When you have it displaying the way you
want, close and save it. Then drag and drop it from the db
window into your existing report's header section.

If all that works and you need more help specifying the date
range, post back with a Copy/Paste of the main report's
record source query.

 

[Jason]

One more questio. What would I add to the SQL of the new Query so that there
would be a field that would count the total number of records for each RSM
within the same date range. I currently have a separate text box for each
RSM with the expression =Sum(IIF[RSM] = "RSM Name", 1, 0)), but this won't
line up right if one of the RSM's does not have any records in that time
period. I think with that and figuring out how to set it up so I only have
to enter the dates once, and this report will be perfect. Thank you for all
your help.

Now that I know the name of your existing query, the new
query would be:

SELECT RSM, Sum([Sales Opportunity]) As TotalOpportunity,
Sum([Sales Converted]) As TotalConverted
FROM DateRangeQuery
GROUP BY RSM

You should understand that the wizards only give you a
helping hand and that you frequently will need to modify
what a wizard generates to do exactly what you need.
Another important point is that the query designer is just a
semi graphical interface for creating a query, it is not the
query. The real query is the SQL statement that the query
designer creates from what you enter in the design grid. To
see the SQL statement you can use the View menu's SQL View
menu item.

Since the query design grid is rather difficult to express
in a text news group message, the standard is to post a
Copy/Paste of the SQL statement (i.e. the query's SQL view).
To create a query from my posted SQL statement, Create a
new query and immediately switch to SQL view and then Copy
the SQL statement I posted and Paste it over the top of
whatever Access put there in its attempt to help you get
started. To see how the SQL statement could be entered in
the query designer, switch back to design view.

It would be a good homework assignment for you to study all
of your queries in both design and SQL view. You shouldn't
find it too difficult to gain an elementary understanding of
SQL, which is a good thing because the wizards are not all
that smart. The query designer can not be used to express
even moderately complex queries so, sooner rather than
later, you will need to use SQL directly.
--
Marsh
MVP [MS Access]

Ok, I understand that I need a new query and that I need to use that query to
generate what will be a sub-report in my existing report. Where I am lost is
how to create a query using the data string you provided. The only ways I
have done queries is using the wizard or in design view pulling fields from
specific tables. I attempted to make a query that totaled these fields for
me, but had no success. I got an error that said I tried to execute a query
that does not include the specified expression 'RSM' as part of an aggregate
function. I have no idea what that means. Do I have to place the data
string you provided in the code builder? I'm sure it's frustrating having to
deal with such a novice, but I really appreciate the help.

The current report's data source is DateRangeQuery. I built this query with
the wizard and simply added "Between [Enter Start Date] and [Enter End Date]"
into the criteria for the Date Recieved field.

I'm not sure that I am getting the idea across. Let's say
you want a report that sumarizes all the Sales Opportunities
and Sales Converted for all RSMs over the same date range as
your existing report. Let's also say that your existing
report's record source query is named qryRSM

Then this new report's record source query would be:

SELECT RSM, Sum([Sales Opportunity]) As TotalOpportunity,
Sum([Sales Converted]) As TotalConverted
FROM qryRSM
GROUP BY RSM

Run this query and make sure it displays all the required
summary totals for all the RSMs.

Next, create a new report based on the new query. This new
report would have only the detail sction with three text
boxes bound to the RSM, TotalOpportunity and TotalConverted
fields. Preview this summary report to make sure it looks
the way you want. When you have it displaying the way you
want, close and save it. Then drag and drop it from the db
window into your existing report's header section.

If all that works and you need more help specifying the date
range, post back with a Copy/Paste of the main report's
record source query.

 

[Marshall Barton]

One more questio. What would I add to the SQL of the new Query so that there
would be a field that would count the total number of records for each RSM
within the same date range. I currently have a separate text box for each
RSM with the expression =Sum(IIF[RSM] = "RSM Name", 1, 0)), but this won't
line up right if one of the RSM's does not have any records in that time
period. I think with that and figuring out how to set it up so I only have
to enter the dates once, and this report will be perfect. Thank you for all
your help.

SELECT RSM, Count(*) As NumSales,
Sum([Sales Opportunity]) As TotalOpportunity,
Sum([Sales Converted]) As TotalConverted
FROM DateRangeQuery
GROUP BY RSM

To eliminate the parameter prompts, first add two text boxes
(named txtFrom and txtTo) to a form that can be used to open
the report. These text boxes will be used to enter the date
range instead of the popup prompts. Then change the query
to use theformname.txtFrom instead of [Enter Start Date] and
theformname.txtTo instead of [Enter End Date]

The form is also a good place to put a button that opens the
report.

To get results (of 0) for RSMs that do not have any activity
in the date range, you need to have a table of all RSMs and
Left Join it to the data table in your original query Or the
new query depending on whether you want the inactive RSMs in
the both reports or just the summary report.

 

[Jason]

Count(*) worked perfectly.

I'm still having to enter the dates several times. I have added two more
fields for the Month and Year the report is supposed to be for. This will
allow the report to print the correct month and year, or just year if I leave
the month field blank. I added these as text boxes along with TxtFrom and
TxtTo on the new MonthlyReport form.

So here's what I have. The main form I am working with is the TO Dashboard.
On this form is a command button which pulls up the new MonthlyReport form
where the data is to be entered. On the MonthlyReport form are four unbound
text boxes named TxtMnth, TxtYr, TxtFrom, and TxtTo. Underneath these text
boxes is another command button to preview the report, 'TO Monthly Report.'

The DateRangeQuery is set up to pull the "Date Recieved" Between
[MonthlyReport].[TxtFrom] And [MonthlyReport].[TxtTo].
Additionally, the Month and Year are identified in the query as
MonthlyReport.TxtMnth AS Expr1, MonthlyReport.TxtYr AS Expr2

After filling out the four text boxes in the new MonthlyReport form with the
required info, I still get prompted for the same info two more times after
clicking 'View Report.' I cannot figure out what is wrong. I hope I
provided everything.

Jason

One more questio. What would I add to the SQL of the new Query so that there
would be a field that would count the total number of records for each RSM
within the same date range. I currently have a separate text box for each
RSM with the expression =Sum(IIF[RSM] = "RSM Name", 1, 0)), but this won't
line up right if one of the RSM's does not have any records in that time
period. I think with that and figuring out how to set it up so I only have
to enter the dates once, and this report will be perfect. Thank you for all
your help.

SELECT RSM, Count(*) As NumSales,
Sum([Sales Opportunity]) As TotalOpportunity,
Sum([Sales Converted]) As TotalConverted
FROM DateRangeQuery
GROUP BY RSM

To eliminate the parameter prompts, first add two text boxes
(named txtFrom and txtTo) to a form that can be used to open
the report. These text boxes will be used to enter the date
range instead of the popup prompts. Then change the query
to use theformname.txtFrom instead of [Enter Start Date] and
theformname.txtTo instead of [Enter End Date]

The form is also a good place to put a button that opens the
report.

To get results (of 0) for RSMs that do not have any activity
in the date range, you need to have a table of all RSMs and
Left Join it to the data table in your original query Or the
new query depending on whether you want the inactive RSMs in
the both reports or just the summary report.

 

[Marshall Barton]

Count(*) worked perfectly.

I'm still having to enter the dates several times. I have added two more
fields for the Month and Year the report is supposed to be for. This will
allow the report to print the correct month and year, or just year if I leave
the month field blank. I added these as text boxes along with TxtFrom and
TxtTo on the new MonthlyReport form.

So here's what I have. The main form I am working with is the TO Dashboard.
On this form is a command button which pulls up the new MonthlyReport form
where the data is to be entered. On the MonthlyReport form are four unbound
text boxes named TxtMnth, TxtYr, TxtFrom, and TxtTo. Underneath these text
boxes is another command button to preview the report, 'TO Monthly Report.'

The DateRangeQuery is set up to pull the "Date Recieved" Between
[MonthlyReport].[TxtFrom] And [MonthlyReport].[TxtTo].
Additionally, the Month and Year are identified in the query as
MonthlyReport.TxtMnth AS Expr1, MonthlyReport.TxtYr AS Expr2

After filling out the four text boxes in the new MonthlyReport form with the
required info, I still get prompted for the same info two more times after
clicking 'View Report.' I cannot figure out what is wrong. I hope I
provided everything.

What, exactly, are you being prompted to enter?

If it's still the same thing as before, (e,g, Enter From
Date), then your query may be based on another query that
was not changed to the form text box references. Another
place that can generate a prompt is a text box in the report
that uses the prompt string in an expression (e.g.
=[Enter From Date] & " through " & [Enter To Date]

The month and year values on the form will need to be
converted to real dates and stuffed into the From and To
text boxes because it will be difficult to make the query
use one both sets of criteria. This can be easy to do in
the txtYr text box's AfterUpdate event:
If IsNull(Me.txtMnth) Then
Me.txtFrom = DateSerial(Me.txtYr, 1, 1)
Me.txtTo = DateSerial(Me.txtYr, 13, 0)
Else
Me.txtFrom = DateSerial(Me.txtYr, Me.txtMnth, 1)
Me.txtTo = DateSerial(Me.txtYr, Me.txtMnth + 1, 0)
End If
You probably will want something similar for txtMnth.

 

[Jason]

Thanks so much for your help. What I ended up with was this string
Between [Forms]![MonthlyReport].[TxtFrom] And [Forms]![MonthlyReport].[TxtTo]

Before I had not specified that the text box the query needed to be looking
for was within a form. I was prompted with Enter MonthlyReport.TxtFrom and
so on. Adding [Forms]! to the beginning of each location fixed the problem.

Everything is working great now. Thanks again for your help and patience.

Jason

Count(*) worked perfectly.

I'm still having to enter the dates several times. I have added two more
fields for the Month and Year the report is supposed to be for. This will
allow the report to print the correct month and year, or just year if I leave
the month field blank. I added these as text boxes along with TxtFrom and
TxtTo on the new MonthlyReport form.

So here's what I have. The main form I am working with is the TO Dashboard.
On this form is a command button which pulls up the new MonthlyReport form
where the data is to be entered. On the MonthlyReport form are four unbound
text boxes named TxtMnth, TxtYr, TxtFrom, and TxtTo. Underneath these text
boxes is another command button to preview the report, 'TO Monthly Report.'

The DateRangeQuery is set up to pull the "Date Recieved" Between
[MonthlyReport].[TxtFrom] And [MonthlyReport].[TxtTo].
Additionally, the Month and Year are identified in the query as
MonthlyReport.TxtMnth AS Expr1, MonthlyReport.TxtYr AS Expr2

After filling out the four text boxes in the new MonthlyReport form with the
required info, I still get prompted for the same info two more times after
clicking 'View Report.' I cannot figure out what is wrong. I hope I
provided everything.

What, exactly, are you being prompted to enter?

If it's still the same thing as before, (e,g, Enter From
Date), then your query may be based on another query that
was not changed to the form text box references. Another
place that can generate a prompt is a text box in the report
that uses the prompt string in an expression (e.g.
=[Enter From Date] & " through " & [Enter To Date]

The month and year values on the form will need to be
converted to real dates and stuffed into the From and To
text boxes because it will be difficult to make the query
use one both sets of criteria. This can be easy to do in
the txtYr text box's AfterUpdate event:
If IsNull(Me.txtMnth) Then
Me.txtFrom = DateSerial(Me.txtYr, 1, 1)
Me.txtTo = DateSerial(Me.txtYr, 13, 0)
Else
Me.txtFrom = DateSerial(Me.txtYr, Me.txtMnth, 1)
Me.txtTo = DateSerial(Me.txtYr, Me.txtMnth + 1, 0)
End If
You probably will want something similar for txtMnth.

 

[Marshall Barton]

Thanks so much for your help. What I ended up with was this string
Between [Forms]![MonthlyReport].[TxtFrom] And [Forms]![MonthlyReport].[TxtTo]

Before I had not specified that the text box the query needed to be looking
for was within a form. I was prompted with Enter MonthlyReport.TxtFrom and
so on. Adding [Forms]! to the beginning of each location fixed the problem.

Everything is working great now. Thanks again for your help and patience.

You're welcome. Sorry for the confusion, I should have
included the use of Forms! in my instructions.