Converting Excel formula to MS Access calculated queries

[vicatucd]

Hello,

I am having trouble converting Excel formulas to MS Access.

Excel Formula:


=COUNTIFS(CY807,">"&CW807, CY807,">3")+COUNTIFS(CY807,"="&CW807,CY807,">7")

My attempt in MS Access:
MetSSA: Count((IIf([PostSSA]>[PreSSA] And [PostSSA]>3,1,0) Or IIf([PostSSA]=[PreSSA] And [PostSSA]>7,1,0)))

Any help is much appreciated!

[Bulzie]

Can you explain in words what you are trying to do? Are you running this in a Query? Are those fields all numeric datatype? What is the above Access part returning when you run it? What should it return?

[ssanfu]

Maybe

MetSSA: Count(IIf([PostSSA]>[PreSSA] And [PostSSA]>3,1,0)) + Count(IIf([PostSSA]=[PreSSA] And [PostSSA]>7,1,0))

[Bulzie]

Maybe:

MetSSA: IIf([PostSSA]>[PreSSA] And [PostSSA]>3 Or [PostSSA]=[PreSSA] And [PostSSA]>7,1,0)

or

SELECT Count(IIf([PostSSA]>[PreSSA] And [PostSSA]>3 Or [PostSSA]=[PreSSA] And [PostSSA]>7,1,0)) AS MetSSA
FROM tblSSA;

[vicatucd]

The values are numeric and it is being run as a calculated field in a query.

I had already attempted:
MetSSA: IIf([PostSSA]>[PreSSA] And [PostSSA]>3 Or [PostSSA]=[PreSSA] And [PostSSA]>7,1,0)

but I get errors

Any other thoughts?

[aytee111]

Access doesn't like And's and Or's mixed together, you need to put brackets around them. (A>B AND B>3) OR (B=C AND B>7) - if that is how you want it.

[Bulzie]

What errors?

[Bulzie]

This worked for me in a query. If you want to do a count take off the first 2 fields and do a Totals "count" for that last field.