Totalling zero values from the Nz function issues 1

[storl2]

Hey all,

I'm trying to total some values in the footer of a group for two different text boxes that have nz(field, 0) in the sql statement. When I try to run the report, I receive an error that says "The Microsoft Jet database could not execute the SQL statement because it contains a field that has an invalid data type." I have several other text boxes that are summed in the footer, so I don't think it's the placement. The only text boxes that I am having problems with are the nz ones. Also, when I created the report with the report wizard, at the point where it gives you the option of summing fields, those two fields did not come up as available to be summed. Any suggestions?

 

[BobJacksonNCI]

I suspect the fields are not defined as numeric...



HTH,
Bob

 

[Crowley16]

I suspect Bob is correct...

 

[storl2]

In the tables that the queries pull from, one field is currency and the other is numeric. I have tried leaving the format of the text boxes blank and changing to number/currency and still have the same issue. The text boxes that I am trying to sum are sums themselves, but so are the other text boxes that work fine.

 

[BobJacksonNCI]

The text boxes that I am trying to sum are sums themselves

You need to use the original expression for the sum.



HTH,
Bob

 

[storl2]

So, do a sum of whatever the original expression was? The expression from the query that I am trying to sum is "nz(Sum(tblContributions.fldContributionAmount),0)", which is named "SumOffldContributionAmount" in the report's text box.

I tried sum(nz(Sum(tblConibutions.fldContributionAmount),0)) and it gave me "can't have aggregate function in expression..." Sorry for so many questions, but I'm new to VBA.

 

[storl2]

One other thing I noticed is that on the report, the field that is set to type currency is not showing as currency after the query is run, which I'm guessing has something to do with the nz. Is there anything else I would have to add to my query to make it stay currency?

SELECT DISTINCT tblFiscalYear.fldFiscalYear, tblFiscalYear.fldFiscalMonth, nz(Sum(tblContributions.fldContributionAmount), 0) AS SumOffldContributionAmount, nz(Sum(tblEventParticipation.fldHours), 0) AS SumOffldHours
FROM (tblContributions RIGHT JOIN tblFiscalYear ON (tblContributions.fldFiscalYear = tblFiscalYear.fldFiscalYear) AND (tblContributions.fldFiscalMonth = tblFiscalYear.fldFiscalMonth)) LEFT JOIN (tblEvent LEFT JOIN tblEventParticipation ON tblEvent.fldEventNumber = tblEventParticipation.fldEventNumber) ON (tblFiscalYear.fldFiscalMonth = tblEvent.fldFiscalMonth) AND (tblFiscalYear.fldFiscalYear = tblEvent.fldFiscalYear)
GROUP BY tblFiscalYear.fldFiscalYear, tblFiscalYear.fldFiscalMonth
ORDER BY tblFiscalYear.fldFiscalYear, tblFiscalYear.fldFiscalMonth;

 

[BobJacksonNCI]

I don't know that I can solve the problem.

Some guidelines that may help...

Regardless of the query's sorting, etc, sorting and grouping must be done in the report. There's a long technical reason for this - trust it is so.

You need to establish the format you want on the report and not worry about making the query output look any particular way.

Being an old mainframer, I learned those lessons the hard way. Seems like reports ought to accept data the way the query presents it - not so.

Think about the above and make adjustments as needed.
If still not working, this is THE BEST SITE to post questions and get answers.

Bob

 

[storl2]

Access seems to want me to do all the formatting for the data types in the tables/queries since it doesn't even give any options in the combo box of format in the fields' properties on the report. I can manually type in "currency" but it still keeps it as a number.

 

[CosmoKramer]

If I'm reading this correctly, you're already getting the monthly calculated values because you are grouping by month. Is it the fiscal year totals you are looking for??

You should be able to add an unbound text box in the group footer (and the report footer if you want grand totals) with its control source set to: =Sum([SumOffldContributionAmount])

Apologies if you tried this already, but this approach does work for me....

Hoc nomen meum verum non est.

 

[dhookom]

The NZ() function returns a variant data type. All you may have to do is to wrap your column name in Val().
=Sum(Val([SumOffldContributionAmount]))

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[storl2]

Duane, you are my new hero. I knew it had something to do with was nz was returning. That worked perfectly and this report is finally done. Thanks!

 

[BobJacksonNCI]

Duane is one of the best.