Calculation for 16 fields in a Report

[Angel-888]

Hi,

I'm using Access 2010 and creating a report that is summing 16 fields but the wizard says I have too many fields selected. I had tried creating the report from scratch adding a sum field in the group footer but the field appears blank.

Below is what I am adding together all from one table. I have another report that will need to sum up 20 fields from a query. Can anyone help please?

=Sum([S1_MaxProfSupportAmt]+[S1_RateAirAllow]+[S1_RateRailAllow]+[S1_RateHireCarAllow]+[S1_MaxPersonCarAmt]+[S1_RateTaxiAllow]+[S1_RateParkingAllow]+[S1_MaxAccomAmt]+[S1_MaxAbsentAmt]+[S1_MaxBreakfastAmt]+[S1_MaxLunchAmt]+[S1_MaxDinnerAmt]+[S1_MaxIncidentalAmt]+[S1_MaxRoomHireAmt]+[S1_MaxSecretaryAmt]+[S1_AddNoteMaxAmt])

 

[Angel-888]

Sorry I forgot to mention that some of the fields would blank. How is this shown if need special treatment.

Thanks

 

[Pat Hartman]

You need to use the Nz() function:

=Sum(Nz([S1_MaxProfSupportAmt],0) + Nz([S1_RateAirAllow],0) + Nz([S1_RateRailAllow],0) + ....

 

[Angel-888]

Thanks Pat, however instead of a blank cell in my report I have "#Error" appearing in my text box.

Is there a way to add to the query?? or am I missing something else. I have even tried adding the table name infront such as:

=Sum(Nz([Tbl_ContractAgreementDetails.S1_MaxProfSupportAmt],0) + Nz([Tbl_ContractAgreementDetails.S1_MaxAbsentAmt],0) + Nz([Tbl_ContractAgreementDetails.S1_RateAirAllow],0) + Nz([Tbl_ContractAgreementDetails.S1_RateRailAllow],0) + Nz([Tbl_ContractAgreementDetails.S1_RateHireCarAllow],0) + Nz([Tbl_ContractAgreementDetails.S1_MaxPersonCarAmt],0) + Nz([Tbl_ContractAgreementDetails.S1_RateTaxiAllow],0) + Nz([Tbl_ContractAgreementDetails.S1_RateParkingAllow],0) + Nz([Tbl_ContractAgreementDetails.S1_MaxAccomAmt],0) + Nz([Tbl_ContractAgreementDetails.S1_MaxBreakfastAmt],0) + Nz([Tbl_ContractAgreementDetails.S1_MaxLunchAmt],0) + Nz([Tbl_ContractAgreementDetails.S1_MaxDinnerAmt],0) + Nz([Tbl_ContractAgreementDetails.S1_MaxIncidentalAmt],0) + Nz([Tbl_ContractAgreementDetails.S1_MaxRoomHireAmt],0) + Nz([Tbl_ContractAgreementDetails.S1_MaxSecretaryAmt],0) + Nz([Tbl_ContractAgreementDetails.S1_AddNoteMaxAmt],0))

I am quite new to access and really appreciate your help.

 

[JHB]

Try to remove the Sum, (and also the table name).
Else post you database with some sample data, (zip it if you haven't post 10 post).

 

[Pat Hartman]

As JHB said, the Sum() isn't necessary. Unlike Excel, Sum() in Access means to add the values in a row. In Excel it is used to sum across columns as well as rows. There are NO functions in any relational database, not just Access that work across rows since that would imply a repeating group and having a repeating group in a table violates first normal form.

The #Error may be caused because the Name property of the control is the name of a field in the RecordSource query. Change the name property to something like txtSum.

PS, your schema could do with some normalization since you have a repeating group. These are all expense items and should be stored in a separate table, one item per row. Then you would use a subform to show them and Sum() to sum them.