#Error

[Ray67]

Hello



I have a report thats giving me #Error. I have three text boxes on the report.

1)Text box Totals from System CL
2)Text box Totals from System CT
3)Text box Sum of System CL and System CT

If there is an amount for text box 1 and text box 2 it would give me a total for text box 3. Now if text box one or two is missing an, amount text box three would gave me #Error.

Text box One $300
Text box Two $0.00
Text box three#Error

Thank you

[Ray67]

One more thing Text box two is a subreport.

[JoeM]

Use the NZ function in your SUM calculation, which will account for missing (NULL) values, i.e.
=NZ([TextBox1],0) + NZ([TextBox2],0)


Edit: Be sure you use your Subreport object reference when referencing the text box on your subreport!

[Ray67]

Thank you for your reply

I'm still getting #error

=NZ([Grand Total],0)+NZ([R_Care Total].Report.ct,0)

[JoeM]

I don't think you are referencing the text box on your subreport properly.
See: https://groups.google.com/forum/?fro...ts/rQZaQIBkjWg

[Ray67]

I also notice that for the ones that don't have a valve on the subreport, it doesn't show up on the main report. It only shows up when the provider has an amount.

[JoeM]

Could explain the relationship between your Report and Subreport and what this sum calculation represents, and why some values are Null/blank?

[Ray67]

Main Report
I have a from to collect parameter value, User enters provider name, month and year, than the report opens. Text box one is the main report. Text box two is the subreport. Here are the link child fields Provider Name;Month;Year. I would like to add the sum of text box 1 and text box2. We are moving all of our billing to a new system. So text box one is total payment collect for old billing system and text box 2 is total payment collected for new billing system.

Thank you

[Ray67]

reports.mdb
I did it in a new db and it still gave me the error. Look at reports, and for system CA you see the error.

[JoeM]

A few things. In your table, you have your Amount field formatted as Text. You should choose a Numerical option instead of Text.

I am not sure why the NZ function isn't working when there is nothing in the SubReport, but perhaps we can go about this another way. We can make a query shows the records and does the calculations, and then use that as the basis of our Report (no subreport).

Based on your example and sample queries, here is what that code may look like:

SELECT 
    Query1.[Dr name], 
    Query1.[Systems posted on], 
    Query1.Amount, Query2.[Systems posted on], 
    Query2.Amount, 
    [Query1]![Amount]+nz([Query2]![Amount],0) AS Total_Amount
FROM 
    Query1 
LEFT JOIN 
    Query2 ON Query1.[Dr name] = Query2.[Dr name];

[Ray67]

Thank you very much that wroked great.