Text Box contains "#Error" (1 Viewer)

[wmartin]

I am developing a program that tracks student tardies. I have a report(att5qryper1T) that is based on a query that looks for a particular student's tardies.
The control for Text50 of that report is:
=IIf(Nz([F1])="","0",Count([F1]))
I have another report att5 that has Text53 whose control is "=[Reports]![att5qryper1t].[Text50]" here's the problem
When Text50 has a number in it then everything works fine. If it has a zero in it, Text53 displayes "#Error". Please Help this thing is driving me nuts.

 

[dhoffman]

I would help you, however I am adamantly opposed to the tracking of tardies. Nothing personal against you, it's just your job I suppose. While tardiness is vile and disgraceful, I believe it is the teacher's duty to deal with such instances on an individual basis.

 

[dhoffman]

by the way, I find it hard to believe that 0 doesn't work but every other number does. I am pretty sure that 0 is not treated any differently form other integers. You must make absolutely sure that 0 is really there and not NULL.

 

[dhoffman]

oh wait, actually I think I see your problem now. See, the second textbox is not receiving the result of the computation from the first textbox, I think. It, I believe, is receiving the value from the first textbox before it is modified by the expression. So, you see, the second textbox is actually getting NULL when you think it is getting 0. The solution would probably go something like this:
Leave the second textbox's datasource blank, that is make the textbox unbound. Then, go to the OnFormat event for the section the textbox is in (at least I'm guessing the OnFormat event will work) and put in this line of code:
text53 = [Reports]![att5qryper1t].[Text50]

If OnFormat doesn't work then try OnPrint. Maybe just try OnPrint in the first place.

But a quick question, why don't you have the second report grab the value from the same table as the first report. WHy is this second report using another report as part of it's datasource? hmmmmmm?

And, please, go easy on the tardy students. Many of them actually are trying very hard. A few tardies is nothing to ruin one's life over.

 

[wmartin]

Thank you for your input. I tried it and received a Run-Time Error "2113" "The value you entered isn't valid for this field.
The reason I have two reports is that the report has to have both absentees and tardies. The whole problem is when the query results have no records. This then is a NZ problem that I don't know the fix.

 

[Rich]

Try Sum(Iif([F1]>0,[F1],0))

 

[wmartin]

The "Sum(Iif([F1]>0,[F1],0))" didn't work. I received the dreaded "#Error"

 

[joeyreyma]

try not enclosing 0 with parenthesis:

=IIf(Nz([F1])="",0,Count([F1]))

HTH

 

[wmartin]

I will be out of the office for about a week I will try your suggestion when I get back thanks for your help.

 

[dhoffman]

One thing that might help you somehow is the NoData event of a Report. That is, if the problem is only when one of the reports has norecords then you could tell the report to close itself or something like that if it has no data. Then, what to do about the other report? I know that you could put some code in the OnOpen event or something that would check to see if the other report is open. I think the way you do that is to try and reference the report with the Reports object. For example, just try to check the value of a field or something on the first report and if this generates an error you know that the first report has no data.