I have a report called JobCost. JobCost has three subreports: Sales, Purchases and Labor. Each subreport calculates and displays a total. These fields are called: SumofSales, SumofPurchases and SumofLabor. I have a field on the main report that calculates and displays the Sum of these sums. It works perfectly as long as all of the subreports have data. However, often there is no labor in a job so the subreport has no data so the main report total field displays the error #Type!.
I have tried many things like as an expression for the SumofLabor field: =IIf(IsNull([SumofSales]-[SumofPurchases]-[SumofLabor]),0,[SumofSales]-[SumofPurchases]-Sumof[Labor]) This returns the same error: #Type! in the SumofSums field.
I have tried using invisible fields on the main form that look to the subreports for the sums and used the NZ function for each field and then totaled those fields in the SumofSums field as follows:
For invisible field SalesTotal I used the expression Nz([Sales Subreport for Jobcost].[Report]![SumOfSales],0). The same for the other invisible fields: PurchasesTotal and LaborTotal. This should return a “0” in the LaborTotal field but instead I’m getting the error #Size! in the LaborTotal field.
I have tried using the HasData property in VBA so that when the JobCost report loads it triggers an event that checks the LaborSubreport for data and assigns a value to a variable. This variable is then used in the SumofSums field expression to exclude the SumofLabor in the SumofSums. I think I may be on to something here but I’m not really a programmer and I may be using the HasData property incorrectly. This is what I’m doing:
OnLoad of JobCost report:
Private Sub Report_Load()
Dim NoLabor As String
If Report.LaborSubreportforJobcost.SumofLaborDollars.HasData Then
NoLabor = False
Else
NoLabor = True
End If
End Sub
Then in the SumofSums field I use the expression: IIf(NoLabor=True,[Sales]-[Purchases]),[Sales]-[Purchases]-[Labor]). But Access doesn’t like the NoLabor in this expression.
What am I doing wrong?
I have tried many things like as an expression for the SumofLabor field: =IIf(IsNull([SumofSales]-[SumofPurchases]-[SumofLabor]),0,[SumofSales]-[SumofPurchases]-Sumof[Labor]) This returns the same error: #Type! in the SumofSums field.
I have tried using invisible fields on the main form that look to the subreports for the sums and used the NZ function for each field and then totaled those fields in the SumofSums field as follows:
For invisible field SalesTotal I used the expression Nz([Sales Subreport for Jobcost].[Report]![SumOfSales],0). The same for the other invisible fields: PurchasesTotal and LaborTotal. This should return a “0” in the LaborTotal field but instead I’m getting the error #Size! in the LaborTotal field.
I have tried using the HasData property in VBA so that when the JobCost report loads it triggers an event that checks the LaborSubreport for data and assigns a value to a variable. This variable is then used in the SumofSums field expression to exclude the SumofLabor in the SumofSums. I think I may be on to something here but I’m not really a programmer and I may be using the HasData property incorrectly. This is what I’m doing:
OnLoad of JobCost report:
Private Sub Report_Load()
Dim NoLabor As String
If Report.LaborSubreportforJobcost.SumofLaborDollars.HasData Then
NoLabor = False
Else
NoLabor = True
End If
End Sub
Then in the SumofSums field I use the expression: IIf(NoLabor=True,[Sales]-[Purchases]),[Sales]-[Purchases]-[Labor]). But Access doesn’t like the NoLabor in this expression.
What am I doing wrong?
