Getting #Type! in a calculated field on a report

I have a report that has two sub-reports.  Then in the control source for a calculated field on the main report is this:

=[Reports]![Income Statement].[Report]![subrptIncomeStatementINCOME].[Report]![txtSumCurrentPeriod]-[Reports]![Income Statement].[Report]![subrptIncomeStatementEXPENSE].[Report]![txtSumCurrentPeriod]

But in some cases there is no [Reports]![Income Statement].[Report]![subrptIncomeStatementINCOME].[Report]![txtSumCurrentPeriod]   --    (the first part of the formula.

In those cases I am getting #Type! as a result on the report.  How can I overcome this?
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveL13Author Commented:
Further explanation as to why in some cases there is no first report...  because the query (Record Source) has no records.
0
John TsioumprisSoftware & Systems EngineerCommented:
How about using a Dcount to check if the query returns data (>0) and the "execute" the formula
0
SteveL13Author Commented:
But I have to execute the formula whether there is a first sub-report or not.  Don't I?
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

IrogSintaCommented:
Since the calculated control is on the main report, there's no need to have the main report as part of your formula.  You can shorten it to this:
= [subrptIncomeStatementINCOME].[Report]![txtSumCurrentPeriod] - [subrptIncomeStatementEXPENSE].[Report]![txtSumCurrentPeriod]

Open in new window

Now when you say there is no subrptIncomeStatementINCOME.report!txtSumCurrentPeriod, do you mean there is no value in that control?  If so, you need to see what is causing it to have no value.  If its control source is summing up a field called CurrentPeriod, are there any records where CurrentPeriod is null?  If so, use the NZ function to force a zero in those cases.  Something like Sum(Nz(CurrentPeriod)).
1
SteveL13Author Commented:
IrogSinta...  Thanks for the tip.  What I mean is that because the record source query has no records, the first subform isn't even appearing.  Therefore, the formula isn't working in those cases.
0
IrogSintaCommented:
Maybe you can upload a small sample database with just the problem report and enough data in the table for us to see.  Be sure if you do this that the data in the table is fine for public viewing.

Ron
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Steve,

you can use the HasData property of a report to see if there is a record
=iif([Reports]![Income Statement].[Report]![subrptIncomeStatementINCOME].[Report].HasData, 
nz([Reports]![Income Statement].[Report]![subrptIncomeStatementINCOME].[Report]![txtSumCurrentPeriod],0) 
-nz([Reports]![Income Statement].[Report]![subrptIncomeStatementEXPENSE].[Report]![txtSumCurrentPeriod],0),null)

Open in new window

I added line breaks to make it easier to read, but they should not be there

have an awesome day,
crystal
0
SteveL13Author Commented:
Back to this issue after a Christmas break.  Since the first sub-report has no data because the query has no data, nothing has worked so far.  So now I'm trying the following code using IsLoaded = True in the onprint event of the report group.  But when I put a breakpoint on the first line it seems the code thinks the sub-report is NOT loaded when it should be loaded because the query has data.  Am so confused right now.

    If CurrentProject.AllReports("subrptIncomeStatementEXPENSE").IsLoaded = True Then
        Me.Text35 = [Reports]![Income Statement].[Report]![subrptIncomeStatementINCOME].[Report]![txtSumCurrentPeriod] - [Reports]![Income Statement].[Report]![subrptIncomeStatementEXPENSE].[Report]![txtSumCurrentPeriod]
        Me.Text36 = [Reports]![Income Statement].[Report]![subrptIncomeStatementINCOME].[Report]![txtSumSamePeriodLastYear] - [Reports]![Income Statement].[Report]![subrptIncomeStatementEXPENSE].[Report]![txtSumSamePeriodLastYear]
        Me.Text37 = [Reports]![Income Statement].[Report]![subrptIncomeStatementINCOME].[Report]![txtSumYearToDate] - [Reports]![Income Statement].[Report]![subrptIncomeStatementEXPENSE].[Report]![txtSumYearToDate]
        Me.Text38 = [Reports]![Income Statement].[Report]![subrptIncomeStatementINCOME].[Report]![txtSumLastYearYearToDate] - [Reports]![Income Statement].[Report]![subrptIncomeStatementEXPENSE].[Report]![txtSumLastYearYearToDate]
        Exit Sub
    End If

Open in new window

0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Steve,

use HasData, which is a property of the subreport   ... not  IsLoaded, which is a property of the current project.

Get rid of (or comment) the code. Try using HasData in the control source. I think you will get what you desire, if I understand the problem correctly.

HasData is needed is NZ won't work if there is no record.

Since this is a report, and you do not have to be concerned about editing data, you can also create queries for the subreport(s) that summarize the data and join them into the main report RecordSource.  Be sure to use Left or Right Join to show all records for the main source even if there are no statistics.

have an awesome day,
crystal
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
I do believe this actually is working now.  Thank you so much!!!!
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Steve ~ happy to help.  

Others helped too  ... hand out Assisted Solution marks to those who also gave you helpful information when you close a question

~~~
"there's no need to have the main report as part of your formula.  You can shorten it to this: ..."

excellent catch, Igog! ... better for accuracy and performance too
~~~

have an awesome day,
crystal
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.