Sum function in a report

[mgonzales]

Is it possible to place a sum function in an unbound textbox to give the total for a specific section in a report without building a query?

 

[boblarson]

Is it possible to place a sum function in an unbound textbox to give the total for a specific section in a report without building a query?

Yes, and you need to account for nulls so for example:

=Sum(Nz([YourFieldNameNotControlName],0))

and make sure that you don't have a control on the report that has the same name as the field (Access gets screwed up that way and will usually give you a #Name error).

 

[mgonzales]

Thanks that worked perfect!
I have 2 other sections that need to be rolled up also based on each prior total. So the function listed below also needs to be summed, and then this sum also needs to be summed. Is there a way to do this?
Thanks

Yes, and you need to account for nulls so for example:

=Sum(Nz([YourFieldNameNotControlName],0))

and make sure that you don't have a control on the report that has the same name as the field (Access gets screwed up that way and will usually give you a #Name error).

 

[boblarson]

Not sure I'm gathering it in but if you have a section total with

=Sum(Nz([YourFieldNameNotControlName],0))

You can also place that in another section and it will total it based on what that section is.

 

[mgonzales]

You are awesome! That also works. I am summing dollar amounts, how would I place the $ in front and round only to 2 decimals places?

Thanks!

Not sure I'm gathering it in but if you have a section total with

=Sum(Nz([YourFieldNameNotControlName],0))

You can also place that in another section and it will total it based on what that section is.

 

[boblarson]

You are awesome! That also works. I am summing dollar amounts, how would I place the $ in front and round only to 2 decimals places?

Thanks!

Set the format property of the text box to CURRENCY and the DECIMAL PLACES property to 2.

 

[mgonzales]

In my report ......In some cases there is information to populate a section in others there is nothing, is there a way to have the section reduced if there is no data in it?

 

[boblarson]

In my report ......In some cases there is information to populate a section in others there is nothing, is there a way to have the section reduced if there is no data in it?

Set the section's CAN SHRINK property to YES and also the same property for the text boxes. If you have labels or other controls in that section, it won't likely shrink.

 

[mgonzales]

I keep getting this error message when I open my report. My report works for all my others that have the exact fields. What does this error msg mean?

"This expression is typed incorrectly or it is too complex to be evaluated"

 

[boblarson]

I keep getting this error message when I open my report. My report works for all my others that have the exact fields. What does this error msg mean?

"This expression is typed incorrectly or it is too complex to be evaluated"

Well, it sounds like you might have something else on the report that is causing an issue (a DLookup, DSum, an IIF or something).

 

[mgonzales]

The only equation I have in the report is this =Sum(Nz([Total Cost],0)) I do not have anything else, the rest are just text or currency fields.

Well, it sounds like you might have something else on the report that is causing an issue (a DLookup, DSum, an IIF or something).

 

[boblarson]

The only equation I have in the report is this =Sum(Nz([Total Cost],0)) I do not have anything else, the rest are just text or currency fields.

Is [Total Cost] the name of a field from the query underlying the report?

 

[mgonzales]

Yes Total Cost is the field name from the query.

Is [Total Cost] the name of a field from the query underlying the report?

 

[boblarson]

Well, I'm at a loss and would need to play with the database at this point to see if I can find the problem.

 

[mgonzales]

I am importing an excel file into a table then the report is based off a query that pulls info from the table. What I have noticed is that the report will only work if I get an error message when importing the excel file. If I do not get an error message then the report does not work.
The big problem here is that I have taken over the DB from another user that built the DB, and I am not sure of all the logic behind his DB.

Thanks for your help.