Annoying Parameter Input While Loading Report

[Gilberto]

Hello,

I have my sub-query as:

SELECT Sum(SumOfIncomeamount) AS SumOfSumOfIncomeamount
FROM qryincome;

So, as you can see there are no groups on it and I am calling it from a
report that calculates my monthly expenses that by itself works pretty well.
So, if I put a calculate test box on my report pointing to the above query it
keeps asking me to input something as a parameter what subsequently (after
entering any value) shows as an error. I don't know how to get rid of this
and show my total income extracted from my query thru the variable
SumOfSumOfIncomeamount.

TIA!
Gilberto

 

[Marshall Barton]

I have my sub-query as:

SELECT Sum(SumOfIncomeamount) AS SumOfSumOfIncomeamount
FROM qryincome;

So, as you can see there are no groups on it and I am calling it from a
report that calculates my monthly expenses that by itself works pretty well.
So, if I put a calculate test box on my report pointing to the above query it
keeps asking me to input something as a parameter what subsequently (after
entering any value) shows as an error. I don't know how to get rid of this
and show my total income extracted from my query thru the variable
SumOfSumOfIncomeamount.

Why do you say that is a "sub-query"?

How do you make a text box "point" to a query?

If you want a report header/footer text box to display the
grand total of all the income amounts in the report's record
source query, then just use the expression
=Sum(SumOfIncomeamount)

If that generates a multiple group level kind of error, then
try the expression:

=DSum("SumOfIncomeamount", "qryincome")

 

[gilberto.beltrao]

Why do you say that is a "sub-query"?

How do you make a text box "point" to a query?

If you want a report header/footer text box to display the
grand total of all the income amounts in the report's record
source query, then just use the expression  
        =Sum(SumOfIncomeamount)

If that generates a multiple group level kind of error, then
try the expression:

        =DSum("SumOfIncomeamount", "qryincome")

Hello Marsh
Well, maybe "sub-query" is not the right term... Let's say, it's an
external query I use to get the total from a table (Incomes) that has
no constrains to the tables used in this report. This report has two
tables: Expenses and Billers which are connected to each other thru a
foreign key as Expenses.BillerID --> Billers.BillerdID. The other
table has my Incomes. I created a simple query summing all my incomes
as I mentioned previously (qryincome) and I've been trying to use the
output from this query to show my total income within my report (at
the report's footer) but it keeps asking me to enter a value
(parameter) before loading the report. As I mentioned before, my query
has no input parameters nor groups - it just sums all my incomeAmout
column.
Regards,
Gilberto

 

[Marshall Barton]

Well, maybe "sub-query" is not the right term... Let's say, it's an
external query I use to get the total from a table (Incomes) that has
no constrains to the tables used in this report. This report has two
tables: Expenses and Billers which are connected to each other thru a
foreign key as Expenses.BillerID --> Billers.BillerdID. The other
table has my Incomes. I created a simple query summing all my incomes
as I mentioned previously (qryincome) and I've been trying to use the
output from this query to show my total income within my report (at
the report's footer) but it keeps asking me to enter a value
(parameter) before loading the report. As I mentioned before, my query
has no input parameters nor groups - it just sums all my incomeAmout
column.

If you are prompted for a value, it means that the prompt
string is a name that Access can not resolve. Make careful
not of the prompt and then look for it in the report's
record source query, as the control source of a bound
control or as a field in the Sorting and Grouping window.
When you find it, fix the spelling. If it is not a field
name, then you are doing something the wrong way (possible
your attempt to "point" to this "external" query?)

As I tried to say before, I don't see why you need this
extra query. Did you try using the Sum and DSum functions?

 

[gilberto.beltrao]

Well, maybe "sub-query" is not the right term... Let's say, it's an
external query I use to get the total from a table (Incomes) that has
no constrains to the tables used in this report. This report has two
tables: Expenses and Billers which are connected to each other thru a
foreign key as Expenses.BillerID --> Billers.BillerdID. The other
table has my Incomes. I created a simple query summing all my incomes
as I mentioned previously (qryincome) and I've been trying to use the
output from this query to show my total income within my report (at
the report's footer) but it keeps asking me to enter a value
(parameter) before loading the report. As I mentioned before, my query
has no input parameters nor groups - it just sums all my incomeAmout
column.

If you are prompted for a value, it means that the prompt
string is a name that Access can not resolve.  Make careful
not of the prompt and then look for it in the report's
record source query, as the control source of a bound
control or as a field in the Sorting and Grouping window.
When you find it, fix the spelling.  If it is not a field
name, then you are doing something the wrong way (possible
your attempt to "point" to this "external" query?)

As I tried to say before, I don't see why you need this
extra query.  Did you try using the Sum and DSum functions?

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thanks for your response, Marsh. Even thou I'm an advanced .NET
developer, I don't know much about Acess - I've been trying to help my
wife who's learning it and decided to create our home account
management using it (crazy, isn't she? ). Forget about the external
query, but how do I sum all the records at my Incomes table and
present it in my report Text Box? I tried the following formulas
without success (it keeps asking for an input)

Directly from my table: =Sum([Incomes]![Incomeamount])

From my query (which sums all my incomes): =Sum( [qryincomeQuery]!
[SumOfIncomeamount] )

Thanks again!

Gilberto

 

[Marshall Barton]

If you are prompted for a value, it means that the prompt
string is a name that Access can not resolve.  Make careful
not of the prompt and then look for it in the report's
record source query, as the control source of a bound
control or as a field in the Sorting and Grouping window.
When you find it, fix the spelling.  If it is not a field
name, then you are doing something the wrong way (possible
your attempt to "point" to this "external" query?)

As I tried to say before, I don't see why you need this
extra query.  Did you try using the Sum and DSum functions?

Thanks for your response, Marsh. Even thou I'm an advanced .NET
developer, I don't know much about Acess - I've been trying to help my
wife who's learning it and decided to create our home account
management using it (crazy, isn't she? ). Forget about the external
query, but how do I sum all the records at my Incomes table and
present it in my report Text Box? I tried the following formulas
without success (it keeps asking for an input)

Directly from my table: =Sum([Incomes]![Incomeamount])

From my query (which sums all my incomes): =Sum( [qryincomeQuery]!
[SumOfIncomeamount] )

Your attempts to use Sum failed because the field is not in
the report's record source table/query.

Sum only works for fields in the report's record source
table/query. This menas that if you want to sum all the
values displayed in the report, you can use a text box
expression like =Sum([the field name])

Note that Sum is not aware of controls in the report so you
can not sum values calculated in a text box. This means
that if you have a text box named Aount with the expression
=Price*Quantity, you can not use =Sum(Amount). You need to
use =Sum(Price*Quantity) instead

If you want to sum a field from some other table/query, then
you can use the DSum function in a text box expression:
=DSum("[the field name]", "the table/query name")

The Domain aggregate functions (DCount,DSum,etc) will
construct the analogous query, run it and return the
resulting value so they are a convenient way to get a single
value.

Side note: when it necessary to use a table name to
disambiguate a field name, the "correct" syntax is to use
dot instead of bang. In an attempt to appeal to all skill
levels, Access sometimes accepts either, but rather than try
to remember where the other one doesn't work, it's best to
use the right one everywhere. Generally, bang is used to
identify a member of a collection and dot is used with
fields, properties and methods.