Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Flag 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
DatabasesMicrosoft Access

Avatar of undefined
Last Comment
Scott McDaniel (EE MVE )

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
John Tsioumpris

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Scott McDaniel (EE MVE )

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.
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER
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 started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Scott McDaniel (EE MVE )

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