NULL Problem (Need a Zero when database is empty)!

[Khelben]

I have a Running Total defined that counts distinctively the number of items in a certain field.&nbsp;&nbsp;This works fine when there are records in the database (I'm using a simple .MDB file).&nbsp;&nbsp;When there are no records, it displays nothing, but what I need is for it to display a zero.<br><br>I wrote a formula to try and handle it, but no matter what I do, it always displays nothing.&nbsp;&nbsp;It seems that the formula never gets evaluated.&nbsp;&nbsp;If a formula contains database fields, it gets evaluated no sooner than WhileReadingRecords.&nbsp;&nbsp;And if the database contains no records, I guess it never gets that far.<br><br>What am I to do?&nbsp;&nbsp;I want a zero displayed when the database is empty, and the count displayed when there is at least one record in the database.&nbsp;&nbsp;Thanks for any ideas!

 

[MalcolmW]

How about putting a zero in a text box, put it where you want, and conditionally suppress it if the RecordCount &gt;1<br>Don't have time to test it, but it might work. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Khelben]

Thanks for the suggestion Malcolm, but is there some special RecordCount function/param available that I don't know about?&nbsp;&nbsp;I've looked through the help, but I don't see anything that does that...but then, I'm new to it.<br><br>My problem seems to be that I can't get the record count without a formula that uses a database field, which aparently never gets evaluated if there aren't any records in the database.&nbsp;&nbsp;<br><br>I guess that's really my question then...how do I get the record count of the database I'm using such that it will give me a ZERO when there are no records?&nbsp;&nbsp;This doesn't seem like a strange request...there's gotta be a way.&nbsp;&nbsp;Anyone have an idea?&nbsp;&nbsp;Thanks!

 

[LindaC]

Perhaps you could set your formula field to zero before reading the records by using the function under 'Evaluation Time' - 'BeforeReadingRecords'. <p>LindaC<br><a href=mailto:lcastner@co.orange.ny.us>lcastner@co.orange.ny.us</a><br><a href= > </a><br>

 

[Khelben]

Good suggestion.&nbsp;&nbsp;However, I tried that, too.&nbsp;&nbsp;For it to work, I'd need something like this:<br><br>BeforeReadingRecords;<br>0;<br>WhileReadingRecords;<br>Count({MyField});<br><br>And this doesn't appear to work...I suspect you can only use one of the 'evaluation time specifiers' for each formula.&nbsp;&nbsp;Apparently, I need some way of telling Crystal &quot;if you don't reach the WhileReadingRecords stage, just default to a 0 rather than ignoring this formula.&quot;<br><br>Good thoughts, all.&nbsp;&nbsp;Anybody have something else I might try?

 

[LindaC]

Do you have to use the same formula field? <p>LindaC<br><a href=mailto:lcastner@co.orange.ny.us>lcastner@co.orange.ny.us</a><br><a href= > </a><br>

 

[Khelben]

Basically, yes.&nbsp;&nbsp;Here's the situation using two formulas:<br><br>--- FORMULA1 ---<br>BeforeReadingRecords;<br>0;<br><br>--- FORMULA2 ---<br>WhileReadingRecords;<br>Count({MyField});<br><br>First, notice that the evaluation-time statements are now completely unnecessary (they are only enforcing the normal operation, so you could just skip them).&nbsp;&nbsp;<br><br>Now here's the problem:&nbsp;&nbsp;FORMULA1 will always display a ZERO on the report.&nbsp;&nbsp;Even if FORMULA2 evaluates, FORMULA1 will superimpose a big 'ol ZERO on top of whatever value F2 displays.&nbsp;&nbsp;So, you have to have a way to tell FORMULA1 when to display a ZERO and when to display nothing.&nbsp;&nbsp;Now we're back to the original problem, because if I reference FORMULA2 in FORMULA1 by saying something like &quot;when FORMULA2 returns a value, don't display a zero&quot; I'm now referencing a database field again (although it's indirectly) and nothing at all gets evaluated when the database contains no records!<br><br>Notice FORMULA2 behaves really well, actually.&nbsp;&nbsp;If there's no records, it's nothing.&nbsp;&nbsp;If there's records it's a number.&nbsp;&nbsp;Great (sort of, anyway).<br><br>If FORMULA1 behaved the similarly, that is if there's no records it's ZERO, and if there's any records it's nothing, we'd be in business.&nbsp;&nbsp;I'd insert both formula fields right on top of each other and be done with it.&nbsp;&nbsp;But FORMULA1 can't be made to discrimiate between 'has records' and 'has no records' without it becomming like FORMULA2, and simply not evalutaing at all when the database is empty.<br><br>See the Catch22 here?&nbsp;&nbsp;If Crystal can do this at all, there must be some special functionality, setting, param, etc somewhere that we're missing.&nbsp;&nbsp;And I would be really surprised if Crysal can't do this...<br><br>Any other ideas??&nbsp;&nbsp;Thanks for all the brain storming!<br>

 

[MalcolmW]

to get around the problem of formula1 always printing a zero...<br>--- FORMULA1 ---<br>BeforeReadingRecords;<br>NumberVar Counter := 0;<br>//Format this formula to suppress if the RecordCount is &gt; 1<br>//ie put the following in the suppress formula box<br>//RecordCount &gt; 1<br><br>--- FORMULA2 ---<br>WhileReadingRecords;<br>Count({MyField});<br><br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[MalcolmW]

whoops - that should be RecordCount &gt; 0 <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Khelben]

The suppression doesn't recognize the &quot;RecordCount&quot; variable that you refer to (I get &quot;The remaining text does not appear to be part of the formula&quot; and similar error messages)...this doesn't appear to be defined.&nbsp;&nbsp;I cannot find any special &quot;RecordCount&quot; parameter that would tell me the number of records in the database.<br><br>I've managed to get around this problem by using &quot;Count&quot; as opposed to &quot;Distinct Count&quot; in my running total.&nbsp;&nbsp;This displays a zero as expected.&nbsp;&nbsp;Luckily, I am already assured that the entries in this field will be distinct.&nbsp;&nbsp;This is a pretty circumstantial solution, though, and I continue to wonder how this would get done using a &quot;Distinct Count&quot; running total.&nbsp;&nbsp;Ah, well.<br><br>Thanks to everyone for your suggestions and helpful efforts.

 

[Ina]

Pretty sloppy work around but how about this as formula2:<br><br>evaluateafter(formula1);<br><br>stringvar dsplyzero = &quot;0&quot;;<br><br>if( formula1 &gt; 0 or not isnull(formula1))<br>then &quot; &quot;<br><br>Put both the formulas on top of each other so this formula will display &quot;0&quot; if the other one does not get evaluated and &quot; &quot; if it does.<br>

 

[Khelben]

Sweet.&nbsp;&nbsp;I haven't tested that yet, but if it works it's as good a solution as the next.&nbsp;&nbsp;I didn't know you could provide the &quot;evaluateafter&quot; statement with a parameter like that.<br><br>Thanks for the excellent idea.

 

[theDeej]

I ran into the same problem, and happened upon this post while searching for an answer. Malcolm's post gave me the idea - althoug I couldn't use RecordCount, I was able to do a count and instead of > 0 I used isnull. I was able to get my report to display a zero when no records were returned using the following formula:

if isnull(count({table.field}))
then 0
else sum({table.field})

Not sure if this is still a problem for anyone...but just in case someone happens upon this post as I did, this is the solution that worked for me.