Link to home
Start Free TrialLog in
Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScFlag for Zambia

asked on

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

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
ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

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
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

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