How to much the content of the sub reports in Ms Access using VBA

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
Hi

I want to evaluate the sub reports in my current & prior year income statements so that the accounts lines on them can be compared properly line by line in Ms Access VBA:

Condition 1

If the current year sub report ([Reports]![srptRevenueRevenueOnlyYTD]![SumOfTotal]) has some figures but the prior year sub report is empty ([Reports]![srptRevenueRevenueOnlyYTDS2]![SumOfTotal]) then the prior year must return same lines with ZEROS

Condition 2

If the prio year sub report ([Reports]![srptRevenueRevenueOnlyYTDS2]![SumOfTotal]) has some figures but the current year sub report is empty ([Reports]![srptRevenueRevenueOnlyYTD]![SumOfTotal]) then the current year must return same lines with ZEROS

I have tried the code below its not working it keeps on asking for debug.
Private Sub Report_Load()
If IsNull([Reports]![srptRevenueRevenueOnlyYTD]![SumOfTotal]) Then
[Reports]![srptRevenueRevenueOnlyYTDS2]![SumOfTotal] = Null
Else
[Reports]![srptRevenueRevenueOnlyYTDS2]![SumOfTotal] = 0
[Reports]![srptRevenueRevenueOnlyYTDS2]![SumOfTotal] = [Reports]![srptRevenueRevenueOnlyYTDS2]![SumOfTotal]
End If
End Sub

Open in new window


Regards

Chris
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Commented:
Probably you need a query to check that you have data in the previous year (i assume you want to match by month)
Then you will need a dummy table that will get filled with the available data and substitute the missing entries with 0s
Dear John Tsioumpris;

Here is what I do monthly  I post a journal with ZERO values to those line accounts that require matching , but its quite cumbersome , that is why I need a VBA code to that job for me.

Otherwise you also thinking in the same direction.

Regards

Chris
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Could you use VBA to post zero values to your journal when you have missing values? Not sure if that's kosher from an accounting standpoint (generally speaking, you shouldn't post anything to "closed" periods), but I'm not sure exactly what you're doing in this instance.

If not, then I'd agree with John regarding the temporary tables. Since you must report on "missing" data, you may not be able to do that easily with a query. In my experience, if you must have a list of data from two sources which may or may not have the same lines (i.e. date periods, or something like that). it's easier to use a temp table, fill that table (or tables), and use those in your report.

Especially with financial data, which can be tough to harvest from your tables.
Ok

But why the code below is failing to locate the reference control:

Private Sub Report_Load()
If (Me.[srptRevenueRevenueOnlyYTD].[Report].[SumOfTotal] = "") And (Me.[srptRevenueRevenueOnlyYTDS2].[Report].[SumOfTotal] = Not Null) Then
Me.[srptRevenueRevenueOnlyYTD].[Report].[SumOfTotal] = 0
End If
End Sub

Open in new window

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
I'd suggest you ask that question separately. It has nothing to do with your current question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial