Calculations in reports

[Double A]

I am trying to put together an invoice. My report already contains a field
that calculates the total invoice. I would like to add to this report a list
of payments made, by date. I would then like to subtract the sum of the
payments from the total invoice sum.

Please help me with the formula that I need to use to make this happen.

Thanks.

 

[haha]

I am trying to put together an invoice. My report already contains a field
that calculates the total invoice. I would like to add to this report a
list
of payments made, by date. I would then like to subtract the sum of the
payments from the total invoice sum.

Please help me with the formula that I need to use to make this happen.

Thanks.

 

[Double A]

Thank you for this. However, I get an error message in my total box if there
are no payments to add together. For example, the InvoiceTotalTextBox has a
number associated with it but the Sum([PaymentFieldName]) does not. This
gives me an error when I run the report.

Please advise on how to have it read the Sum([PaymentFieldName]) as 0 if
there is nothing there.

Thanks.

Hi Double A,

You should be able to calculate the sum of the payments using the Sum
function. Something like this:

=Sum([FieldName])

where FieldName is the name of the underlying field. Make certain that the
text boxes do not have the same name as the field, otherwise you will get a
circular reference error. You calculation will look something like this:

=[InvoiceTotalTextbox] - Sum([PaymentFieldName])


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I am trying to put together an invoice. My report already contains a field
that calculates the total invoice. I would like to add to this report a list
of payments made, by date. I would then like to subtract the sum of the
payments from the total invoice sum.

Please help me with the formula that I need to use to make this happen.

Thanks.

 

[Tom Wickerath]

However, I get an error message in my total box if there are no

payments to add together.

What is the error message?
Try using the Nz function.



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Thank you for this. However, I get an error message in my total box if there
are no payments to add together. For example, the InvoiceTotalTextBox has a
number associated with it but the Sum([PaymentFieldName]) does not. This
gives me an error when I run the report.

Please advise on how to have it read the Sum([PaymentFieldName]) as 0 if
there is nothing there.

Thanks.

 

[Double A]

When my report runs it shows #ERROR where the calculation should be.

I am not familiar with the Nz function.

Thanks.

However, I get an error message in my total box if there are no
payments to add together.

What is the error message?
Try using the Nz function.



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Thank you for this. However, I get an error message in my total box if there
are no payments to add together. For example, the InvoiceTotalTextBox has a
number associated with it but the Sum([PaymentFieldName]) does not. This
gives me an error when I run the report.

Please advise on how to have it read the Sum([PaymentFieldName]) as 0 if
there is nothing there.

Thanks.

 

[Tom Wickerath]

In my initial response, I wrote: "Make certain that the text boxes do not
have the same name as the field, otherwise you will get a circular reference
error." Have you done this? This KB article may be helpful to you:

Troubleshooting Tips for Error Values
http://support.microsoft.com/kb/209132

Verify that you have at least one record available in the report's
recordsource, which is likely a query. If you do not, then you should use VBA
code to cancel the report's open event procedure. Something like this:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ProcError

MsgBox "There is no data for the selected criteria.", _
vbInformation, "No Data Available..."
Cancel = True

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Report_NoData event procedure..."
Resume ExitProc
End Sub


If you are opening this report using a command button on a form, and the
above NoData event procedure triggers, then you will need to trap for Error
2501 as well. You would use code something like this on a form:

Private Sub cmdPrint_Click()
On Error GoTo ProcError

'Add this if printing the current record from a bound form
If Me.Dirty = True Then
Me.Dirty = False
End If

DoCmd.OpenReport "ReportNameGoesHere", acPreview

ExitProc:
Exit Sub
ProcError:
Select Case Err.Number
Case 2501 'Report open cancelled
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdPrint_Click event procedure..."
End Select
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________