|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
August 29th, 2006, 07:29 PM
posted to microsoft.public.access.forms
|
|||
|
|||
It doesn't Add up
Hello,
I have a report with at sub report in it. The main report is of employees and the sub report is of their overtime. This is a one to many relationship. I need to add various fields to come to a total field. Several of the fields are on the main report and one is on the sub report. The sub report field is a fiels that may not have a value. For example; if an employee does not have overtime in a given week then there is no value to add. When this is the case the calculation field returns an #error. I do not know how to fix that. I also do not know exactly what the #error message is telling me. Any help would be appreciated. Joe Papi 
|
|
#2
August 29th, 2006, 07:53 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
It doesn't Add up
In your Sum field in the subreport, you need something to turn Null values
(or no records returned) into zeros. Look at the NZ function. This will take nulls and return zeros. Then your calculation will the the sum of overtime hours as zero instead of null. This should prevent the #error. Barry "Joe C" wrote: Hello, I have a report with at sub report in it. The main report is of employees and the sub report is of their overtime. This is a one to many relationship. I need to add various fields to come to a total field. Several of the fields are on the main report and one is on the sub report. The sub report field is a fiels that may not have a value. For example; if an employee does not have overtime in a given week then there is no value to add. When this is the case the calculation field returns an #error. I do not know how to fix that. I also do not know exactly what the #error message is telling me. Any help would be appreciated. Joe Papi
|
|
#3
August 29th, 2006, 08:00 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
It doesn't Add up
On Tue, 29 Aug 2006 11:29:03 -0700, Joe C wrote:
Hello, I have a report with at sub report in it. The main report is of employees and the sub report is of their overtime. This is a one to many relationship. I need to add various fields to come to a total field. Several of the fields are on the main report and one is on the sub report. The sub report field is a fiels that may not have a value. For example; if an employee does not have overtime in a given week then there is no value to add. When this is the case the calculation field returns an #error. I do not know how to fix that. I also do not know exactly what the #error message is telling me. Any help would be appreciated. Joe Papi Look up the Nz() function in VBA help. =Sum(Nz([FieldName])) or = Nz([FieldA]) + Nz([FieldB]) + Nz(etc...) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
|
#4
August 30th, 2006, 02:15 AM
posted to microsoft.public.access.forms
|
|||
|
|||
|
It doesn't Add up
Fred,
Does this have to be in a VB statement? I have been using a text box control. Could you please give some direction if it has to be VB Thanks Joe Papi "fredg" wrote: On Tue, 29 Aug 2006 11:29:03 -0700, Joe C wrote: Hello, I have a report with at sub report in it. The main report is of employees and the sub report is of their overtime. This is a one to many relationship. I need to add various fields to come to a total field. Several of the fields are on the main report and one is on the sub report. The sub report field is a fiels that may not have a value. For example; if an employee does not have overtime in a given week then there is no value to add. When this is the case the calculation field returns an #error. I do not know how to fix that. I also do not know exactly what the #error message is telling me. Any help would be appreciated. Joe Papi Look up the Nz() function in VBA help. =Sum(Nz([FieldName])) or = Nz([FieldA]) + Nz([FieldB]) + Nz(etc...) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
|
#5
August 30th, 2006, 04:02 AM
posted to microsoft.public.access.forms
|
|||
|
|||
|
It doesn't Add up
On Tue, 29 Aug 2006 18:15:01 -0700, Joe C wrote:
Fred, Does this have to be in a VB statement? I have been using a text box control. Could you please give some direction if it has to be VB Thanks Joe Papi "fredg" wrote: On Tue, 29 Aug 2006 11:29:03 -0700, Joe C wrote: Hello, I have a report with at sub report in it. The main report is of employees and the sub report is of their overtime. This is a one to many relationship. I need to add various fields to come to a total field. Several of the fields are on the main report and one is on the sub report. The sub report field is a fiels that may not have a value. For example; if an employee does not have overtime in a given week then there is no value to add. When this is the case the calculation field returns an #error. I do not know how to fix that. I also do not know exactly what the #error message is telling me. Any help would be appreciated. Joe Papi Look up the Nz() function in VBA help. =Sum(Nz([FieldName])) or = Nz([FieldA]) + Nz([FieldB]) + Nz(etc...) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail In a Text control is fine. Directly in an Unbound control Control Source, type it as: = Nz([FieldA]) + Nz([FieldB]) + Nz(etc...) Make sure th name of the control is NOT the same as the name of any field used in the expression. In VBA it would be: [ControlName] = Nz([FieldA]) + Nz([FieldB]) + Nz(etc...) or VariableName = Nz([FieldA]) + Nz([FieldB]) + Nz(etc...) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
|
#6
August 30th, 2006, 03:26 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
It doesn't Add up
Fred,
I am not doing something right. Here is what I have for a control source: =Sum(Nz([OT rate],0)) Just be clear, this control is on a sub form and, when added to other controls on the main form it will give me a total. The resulting expression is on the main form with this sub form field added to several main form fields. "fredg" wrote: On Tue, 29 Aug 2006 18:15:01 -0700, Joe C wrote: Fred, Does this have to be in a VB statement? I have been using a text box control. Could you please give some direction if it has to be VB Thanks Joe Papi "fredg" wrote: On Tue, 29 Aug 2006 11:29:03 -0700, Joe C wrote: Hello, I have a report with at sub report in it. The main report is of employees and the sub report is of their overtime. This is a one to many relationship. I need to add various fields to come to a total field. Several of the fields are on the main report and one is on the sub report. The sub report field is a fiels that may not have a value. For example; if an employee does not have overtime in a given week then there is no value to add. When this is the case the calculation field returns an #error. I do not know how to fix that. I also do not know exactly what the #error message is telling me. Any help would be appreciated. Joe Papi Look up the Nz() function in VBA help. =Sum(Nz([FieldName])) or = Nz([FieldA]) + Nz([FieldB]) + Nz(etc...) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail In a Text control is fine. Directly in an Unbound control Control Source, type it as: = Nz([FieldA]) + Nz([FieldB]) + Nz(etc...) Make sure th name of the control is NOT the same as the name of any field used in the expression. In VBA it would be: [ControlName] = Nz([FieldA]) + Nz([FieldB]) + Nz(etc...) or VariableName = Nz([FieldA]) + Nz([FieldB]) + Nz(etc...) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
