Trying to count in report using IIF or SUM(ABS) not working

[uthomsst]

I have a problem with a report where I am trying to count the detail records in a report but exclude both null and zero values.

I have looked through the forums and found a couple of answers but they do not work for me. I am using a ADP project and using MSDE (Free SQL) as the backend.

If I use the command
=Count(*) (Works counts all records)
=Count(d3force) (Works excludes nulls but includes zero)

Therefore I want something that will exclude 0

I have tried using the follwing to count the zero records.

=Sum(Abs([d3force])=0)
=Sum(IIF([d3force]=0, 1, 0)

(The syntax above may not be correct but I don't have the code to hand so I just made up examples so you would get the idea). I have pretty much just copied and pasted examples directly from the forum and changed a single field name.

These just give me an error 2589 and state incorrect syntax and the report won't run at all.

Help just gives me a blank screen for this error number.

I am completely stuck now and have nothing left I can try

Has anybody got any ideas.

 

[lupins46]

I can't see why you would get syntax errors.
You can try this

=Sum(IIF(nz([d3force])>0, 1, 0)

but I would have thought your original would work.

Which section are you putting the control in?

 

[uthomsst]

I have got a break on product description so its in there
its the d3desc footer

I have a control in the detail line and the code runs with this value entered, but I cannot sum a control source.

IIf([d3force]=0,1,0)

I have entered the value below in the break footer and it causes an error.

=Sum(IIf([d3force]=0,1,0))

This states "The Expression is Invalid

 

[lupins46]

Just picked up the point about it being an ADP.
I think the only simple solution in this case is to include something in your underlying report recordsource which can be summed directly without an iif. So add an extra column which derives 1 and 0 and then just sum that.

(CASE WHEN ([d3force] >0) THEN 1 ELSE 0 END) AS knt

In the report sum the knt field

 

[uthomsst]

do iif's not work in adp's then?

Is there a list anywhere that tells me the difference between mdp and adp because a lot of the things I use to do with mdp's I can't do with adp's

I got around it by changing the input form so that when any numeric is set to 0 then that field is not wrote to the file and is therefore is null. When modifying records I delete the original record and create a new one so that again 0 fields will be null (i.e. change a 5 to a 0 and you get null not 0 as you can't set a field to null)

This means the reports now work ok.

This is someone elses system being rewrote and I have to contend with a hundred thousand of their old records.

I'll have a try of your solution though and see if it works.

Thanks

 

[lupins46]

I'm sure there will be an on-line source for such info, but I'm not aware of it. Yes there are lots of differences.

I use the Access Developer's Handbook or the Access Developer's Guide to SQL Server for help with adp stuff.