Help with using Count() 1

[rasticle]

Hi,

I am trying to create a summary report. The fields I want to summarize are in a Yes/No format. I have a query behind the report that pulls all of the fields that are Yes. It is set up like this...

SELECT tblEnforcement.[103], tblEnforcement.[103CR], tblEnforcement.[6RK], * FROM tblFacility INNER JOIN tblEnforcement ON tblFacility.epaIDMaster=tblEnforcement.epaID WHERE (((tblEnforcement.[103])=Yes)) OR (((tblEnforcement.[103CR])=Yes) OR ((tblEnforcement.[6RK])=Yes));

There are about 25 of these fields, so I tried to keep it short. On the report I am using, for example, =count(103) in a text box. Anyway, when there is a field that does not have a Yes value, I get an error on the report. I have tried Iff statements, but I can't figure out what I am doing wrong. I don't think this is that complicated of a problem, I am just having a moment.

Thanks,
Shawn

 

[genomon]

So what is the error saying?

Silence is golden.
Duct tape is silver.

 

[rasticle]

On the form in the textbox I enter: =Count(103) and I get this error: "The Expression you entered contains invalid syntax. You may have entered an operand without an operator."

I believe this is because the query is looking for all 103 where it has a Yes value. Sense there is no record in 103 with a Yes value, the query is passing in a null value. So I tried =Iff(103 Is Not Null, count(103), 103 = 0).. but that didn't work. =(

 

[genomon]

I suspect it thinks 103 is a number, not a field name (you really should rename those fields something meaningful).
Try =Count([103]) maybe?

Silence is golden.
Duct tape is silver.

 

[rasticle]

The query that drives this report pulls from the field 103. Where the value is "Yes". If the query finds records where the value is "Yes" then the report sees them and it cound appropriatly. If, for example, 103 did not have a "Yes" value in any record that is when I get the error. So I am trying to make an Iff statement in the textbox that will display a zero if there is no value for that field. I hope that makes sense. I just don't know how to make the Iff statement work. What I have for an Iff statement is in my above post.

The name 103, while it doesn't appear to be meaningful, is actually a federal regulatory law... so it's quasi meaningful. =P But I can see where you are coming from, it doesn't seem like a traditional name for a column.

 

[genomon]

IIf([103] = "Yes",[103], 0) oughta do it!
(Leave it to the government to goof up our nice normalized databases!)

Silence is golden.
Duct tape is silver.

 

[rasticle]

hmmm..
I tried Iff([103]="Yes", Count([103]),0)

but that just gives me a zero, even when it would count it correctly before.

Essentally, if the query returns any records with "Yes" I want to count how many times. So if 2 facilities have a 103 law violation, then I want it to count 2, but if it returns no facilities with a 103 violation, I would like it to show 0. I'm really confused. lol

Thanks,
Shawn

 

[PHV]

And what about something like this ?
Sum(IIf([103],1,0))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rasticle]

Worked great, thanks!