Access Reports are evil - please help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to insert a formula to calculate the total of a specific kind of
entry. Each entry in the database has a numeric value and I am looking to
find the sum.

The formula I have inserted is the simple =sum(field). This is not working.

Please tell me what formula I need for simple calculation.
 
Are you putting this in the report footer, group footer or
page footer. Page footer will not work.

Chris
 
I am putting it in the report footer.

Chris Reveille said:
Are you putting this in the report footer, group footer or
page footer. Page footer will not work.

Chris
Click to expand...
 
I am trying to insert a formula to calculate the total of a specific kind of
entry. Each entry in the database has a numeric value and I am looking to
find the sum.

The formula I have inserted is the simple =sum(field). This is not working.

Please tell me what formula I need for simple calculation.
Click to expand...

You are probably trying to place this sum in the report's Page Footer.
See Microsoft KnowledgeBase article:
132017 "How to sum a column of numbers in a report by page"
 
No. I have not inserted the formula in the page footer, as I do not want the
sum to appear at the bottom of the page, I want it to appear at the end of
the report.

This seems to be what most people imagine I've done incorrectly. I am
trying to find out if there is some sort of formulaic typo that I've inserted
that is impeding the calculation.
 
working said:
I am putting it in the report footer.
Click to expand...

Then you will need to define what you mean by "This is not working." as your
expression looks fine (assuming that the field name you have inserted in
"=Sum(Field)" actually exists.

Do you get an error?
Do you get a result that looks incorrect?
 
No. I have not inserted the formula in the page footer, as I do not want the
sum to appear at the bottom of the page, I want it to appear at the end of
the report.

This seems to be what most people imagine I've done incorrectly. I am
trying to find out if there is some sort of formulaic typo that I've inserted
that is impeding the calculation.
Click to expand...

Then you have to be a bit more forthcoming.
'Not Working' how?
Do you get a wrong sum? an #error? no sum?
What is the EXACT control source?
What is the Name of the control? Is it the same as the name of the
Field?
Can any of the values summed be Null?
 
#Name? Is the error I get when I try to total the entries.

Not all entries are complete, and if they aren't complete, they won't have
the figures coded in.

The table's function is to keep a record of the volume of work hours spent
on each given project. We calculate the total per project, enter it in the
table, and then we want Access to give us a grand total.

So, to be clear, the formula I have entered reads =SUM(Proofreading Time Log
Total) and for some reason, doesn't work.

I have always done this sort of calculations in Excel, so the way Access
uses this feature is new to me. I really appreciate the feedback.
 
#Name? Is the error I get when I try to total the entries.

Not all entries are complete, and if they aren't complete, they won't have
the figures coded in.

The table's function is to keep a record of the volume of work hours spent
on each given project. We calculate the total per project, enter it in the
table, and then we want Access to give us a grand total.

So, to be clear, the formula I have entered reads =SUM(Proofreading Time Log
Total) and for some reason, doesn't work.

I have always done this sort of calculations in Excel, so the way Access
uses this feature is new to me. I really appreciate the feedback.
Click to expand...

1) =SUM(Proofreading Time Log Total) wont work.
You have spaces within the field name and therefore you must surround
the field name with brackets.

=SUM([Proofreading Time Log Total])

2) Make sure the name of this control is not
"Proofreading Time Log Total"

3) Is [Proofreading Time Log Total] included in the report's record
source?

Note: If there is a chance one or more of the entries may be null, use
the Nz() function:
=SUM(Nz([Proofreading Time Log Total],0))
 
working said:
#Name? Is the error I get when I try to total the entries.

What is that all about?!?
Click to expand...

Make sure the name of the control is not the same as the name of the field
you are summing. Other than that it usually means that the field name does
not exist in the RecordSource for the report (check your spelling).
 
Hiya fredg.
Well, I tried both of your formulas, and I got the message "The Microsoft
Jet database engine could not execute the SQL statement because it contains a
field that has an invalid data type."

Now what?

Oh, yes, Proofreading Time Log is in the record source.

fredg said:
#Name? Is the error I get when I try to total the entries.

Not all entries are complete, and if they aren't complete, they won't have
the figures coded in.

The table's function is to keep a record of the volume of work hours spent
on each given project. We calculate the total per project, enter it in the
table, and then we want Access to give us a grand total.

So, to be clear, the formula I have entered reads =SUM(Proofreading Time Log
Total) and for some reason, doesn't work.

I have always done this sort of calculations in Excel, so the way Access
uses this feature is new to me. I really appreciate the feedback.
Click to expand...

1) =SUM(Proofreading Time Log Total) wont work.
You have spaces within the field name and therefore you must surround
the field name with brackets.

=SUM([Proofreading Time Log Total])

2) Make sure the name of this control is not
"Proofreading Time Log Total"

3) Is [Proofreading Time Log Total] included in the report's record
source?

Note: If there is a chance one or more of the entries may be null, use
the Nz() function:
=SUM(Nz([Proofreading Time Log Total],0))
Click to expand...
 
Hiya fredg.
Well, I tried both of your formulas, and I got the message "The Microsoft
Jet database engine could not execute the SQL statement because it contains a
field that has an invalid data type."

Now what?

Oh, yes, Proofreading Time Log is in the record source.

fredg said:
#Name? Is the error I get when I try to total the entries.

Not all entries are complete, and if they aren't complete, they won't have
the figures coded in.

The table's function is to keep a record of the volume of work hours spent
on each given project. We calculate the total per project, enter it in the
table, and then we want Access to give us a grand total.

So, to be clear, the formula I have entered reads =SUM(Proofreading Time Log
Total) and for some reason, doesn't work.

I have always done this sort of calculations in Excel, so the way Access
uses this feature is new to me. I really appreciate the feedback.

:

On Tue, 18 Jan 2005 11:17:04 -0800, working girl wrote:

No. I have not inserted the formula in the page footer, as I do not want the
sum to appear at the bottom of the page, I want it to appear at the end of
the report.

This seems to be what most people imagine I've done incorrectly. I am
trying to find out if there is some sort of formulaic typo that I've inserted
that is impeding the calculation.

:

On Tue, 18 Jan 2005 09:25:09 -0800, working girl wrote:

I am trying to insert a formula to calculate the total of a specific kind of
entry. Each entry in the database has a numeric value and I am looking to
find the sum.

The formula I have inserted is the simple =sum(field). This is not working.

Please tell me what formula I need for simple calculation.

You are probably trying to place this sum in the report's Page Footer.
See Microsoft KnowledgeBase article:
132017 "How to sum a column of numbers in a report by page"
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.


Then you have to be a bit more forthcoming.
'Not Working' how?
Do you get a wrong sum? an #error? no sum?
What is the EXACT control source?
What is the Name of the control? Is it the same as the name of the
Field?
Can any of the values summed be Null?
Click to expand...

1) =SUM(Proofreading Time Log Total) wont work.
You have spaces within the field name and therefore you must surround
the field name with brackets.

=SUM([Proofreading Time Log Total])

2) Make sure the name of this control is not
"Proofreading Time Log Total"

3) Is [Proofreading Time Log Total] included in the report's record
source?

Note: If there is a chance one or more of the entries may be null, use
the Nz() function:
=SUM(Nz([Proofreading Time Log Total],0))
Click to expand...
Click to expand...

What SQL statement?
The expressions I gave you were to go in the control source of an
Unbound control placed in the Report's Report Footer (not in a Query).
You're expression used "Proofreading Time Log Total"
not "Proofreading Time Log".

Is there a field in the report's recordsource named "Proofreading Time
Log Total"?
Are you surrounding the field name with brackets, (as in my previous
reply)?
 
I don't even know what an SQL statement is.

Is there a field in the report's recordsource named "Proofreading Time Log
Total"?
YES, and that is the field I am trying to calculate
Are you surrounding the field name with brackets, (as in my previous reply)?
YES, I copied the formula from your email.

Why isn't this working?

fredg said:
Hiya fredg.
Well, I tried both of your formulas, and I got the message "The Microsoft
Jet database engine could not execute the SQL statement because it contains a
field that has an invalid data type."

Now what?

Oh, yes, Proofreading Time Log is in the record source.

fredg said:
On Tue, 18 Jan 2005 13:21:04 -0800, working girl wrote:

#Name? Is the error I get when I try to total the entries.

Not all entries are complete, and if they aren't complete, they won't have
the figures coded in.

The table's function is to keep a record of the volume of work hours spent
on each given project. We calculate the total per project, enter it in the
table, and then we want Access to give us a grand total.

So, to be clear, the formula I have entered reads =SUM(Proofreading Time Log
Total) and for some reason, doesn't work.

I have always done this sort of calculations in Excel, so the way Access
uses this feature is new to me. I really appreciate the feedback.

:

On Tue, 18 Jan 2005 11:17:04 -0800, working girl wrote:

No. I have not inserted the formula in the page footer, as I do not want the
sum to appear at the bottom of the page, I want it to appear at the end of
the report.

This seems to be what most people imagine I've done incorrectly. I am
trying to find out if there is some sort of formulaic typo that I've inserted
that is impeding the calculation.

:

On Tue, 18 Jan 2005 09:25:09 -0800, working girl wrote:

I am trying to insert a formula to calculate the total of a specific kind of
entry. Each entry in the database has a numeric value and I am looking to
find the sum.

The formula I have inserted is the simple =sum(field). This is not working.

Please tell me what formula I need for simple calculation.

You are probably trying to place this sum in the report's Page Footer.
See Microsoft KnowledgeBase article:
132017 "How to sum a column of numbers in a report by page"
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.


Then you have to be a bit more forthcoming.
'Not Working' how?
Do you get a wrong sum? an #error? no sum?
What is the EXACT control source?
What is the Name of the control? Is it the same as the name of the
Field?
Can any of the values summed be Null?
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.


1) =SUM(Proofreading Time Log Total) wont work.
You have spaces within the field name and therefore you must surround
the field name with brackets.

=SUM([Proofreading Time Log Total])

2) Make sure the name of this control is not
"Proofreading Time Log Total"

3) Is [Proofreading Time Log Total] included in the report's record
source?

Note: If there is a chance one or more of the entries may be null, use
the Nz() function:
=SUM(Nz([Proofreading Time Log Total],0))
Click to expand...
Click to expand...

What SQL statement?
The expressions I gave you were to go in the control source of an
Unbound control placed in the Report's Report Footer (not in a Query).
You're expression used "Proofreading Time Log Total"
not "Proofreading Time Log".

Is there a field in the report's recordsource named "Proofreading Time
Log Total"?
Are you surrounding the field name with brackets, (as in my previous
reply)?
Click to expand...
 
"Name of the control is not the same as the name of the field" What does
that mean exactly? The main record has the appropriate field. As for
spelling, not an issue. It's a straight copy/paste - no possibility of typo.
 
working said:
"Name of the control is not the same as the name of the field" What
does that mean exactly? The main record has the appropriate field.
As for spelling, not an issue. It's a straight copy/paste - no
possibility of typo.
Click to expand...

If I have a TextBox named "TotalDollars" and in the ControlSource property I
enter the expression "=Sum([TotalDollars])" I will get a #NAME error
because I have named the control the same as one of the fields contained
within the ControlSource expression. That is not allowed.
 
Okay, so in the Control Source, if it simply says "=Sum([TotalDollars])" and
the field is not included in the Control Source, then it should work?

Rick Brandt said:
working said:
"Name of the control is not the same as the name of the field" What
does that mean exactly? The main record has the appropriate field.
As for spelling, not an issue. It's a straight copy/paste - no
possibility of typo.
Click to expand...

If I have a TextBox named "TotalDollars" and in the ControlSource property I
enter the expression "=Sum([TotalDollars])" I will get a #NAME error
because I have named the control the same as one of the fields contained
within the ControlSource expression. That is not allowed.
Click to expand...
 
Okay, so in the Control Source, if it simply says "=Sum([TotalDollars])" and
the field is not included in the Control Source, then it should work?

Rick Brandt said:
working said:
"Name of the control is not the same as the name of the field" What
does that mean exactly? The main record has the appropriate field.
As for spelling, not an issue. It's a straight copy/paste - no
possibility of typo.
Click to expand...

If I have a TextBox named "TotalDollars" and in the ControlSource property I
enter the expression "=Sum([TotalDollars])" I will get a #NAME error
because I have named the control the same as one of the fields contained
within the ControlSource expression. That is not allowed.
Click to expand...
Click to expand...

No, that shouldn't work. The field named [TotalDollars] must be in the
report's recordsource, and shown on the control's Control source. How
else is it going to be able to Sum it?

Re-read Rick's message.
The NAME of the control cannot be the same as the name of a field used
in it's control source. Access will get confused and not know whether
you are referring to the Control itself, or a field of that name in
the control source.
So, look on the control's property sheet's Other tab. Name is the
first property shown. If it is "TotalDollars" change it to
"TotalDollars1" or anything else. Just not "TotalDollars".
 
So, then, am I to understand that the control source should read
Total Dollars =Sum([Total Dollars])

Or are you saying that it should read =Sum(Total Dollars1)


fredg said:
Okay, so in the Control Source, if it simply says "=Sum([TotalDollars])" and
the field is not included in the Control Source, then it should work?

Rick Brandt said:
working girl wrote:
"Name of the control is not the same as the name of the field" What
does that mean exactly? The main record has the appropriate field.
As for spelling, not an issue. It's a straight copy/paste - no
possibility of typo.

If I have a TextBox named "TotalDollars" and in the ControlSource property I
enter the expression "=Sum([TotalDollars])" I will get a #NAME error
because I have named the control the same as one of the fields contained
within the ControlSource expression. That is not allowed.
Click to expand...
Click to expand...

No, that shouldn't work. The field named [TotalDollars] must be in the
report's recordsource, and shown on the control's Control source. How
else is it going to be able to Sum it?

Re-read Rick's message.
The NAME of the control cannot be the same as the name of a field used
in it's control source. Access will get confused and not know whether
you are referring to the Control itself, or a field of that name in
the control source.
So, look on the control's property sheet's Other tab. Name is the
first property shown. If it is "TotalDollars" change it to
"TotalDollars1" or anything else. Just not "TotalDollars".
Click to expand...
 
working said:
So, then, am I to understand that the control source should read
Total Dollars =Sum([Total Dollars])

Or are you saying that it should read =Sum(Total Dollars1)
Click to expand...

No. The control source should read...

=Sum([FieldName])

....where everything between the square brackets is the name of the field in
the Report's RecordSource that you want to sum. The *Name* of the TextBox
that you put this expression in can be ANYTHING allowed in Access except for
"FieldName".

And by "FieldName" I mean the same string of characters that is included in
the Sum() expression.
 
Back
Top