Help with iff statment trying to count people within a certain parameter

[jdr3091]

I need to write a query statement that counts people not within a certain number value. The column that I am using could have 4 values: The field is empty; the field has a number between 37 & 39; the field could have a number not between 37 & 39; or the field could have UTD. I thought I could use an iff statement that would ask if the value was > =37 & <=39. Then if this was true give them a 0 value if false give a 1 value. That way I could count the ones and obtain the number of exclusions. Since there would be null values I thought I could use the Nz function to give the empty fields a 0 value and then they wouldn’t affect the count. However, I need to count the UTDs I thought the statement would generally be something like this:

Excluded by gest: Iff(Nz(between [37] AND [39]),0,1),0))

I was thinking this could go in the field row of the QBE and expression would be in the total row.



But what would I do with the UTDs? Could I assign UTD a zero value and then query the column a second time for UTD only? How would I write that? I’m way beyond my SQL writing and syntax ability and wonder if I need a vbs?

[June7]

What is UTD? Is that literal text value in the field? This is a text not number type field? What is the range of number data in this field?

[orange]

The function is IIF , not IFF.

[jdr3091]

UTD is literal text which needs a separate count. I have the field set as text. the number value range is 1-50; but there can be some of the fields that are blank.

[June7]

The field is text which means alpha sort and the number 10 will sort before the number 2. Need placeholder 0, like 01, 02, 03, 04, 05, 06, 07, 08, 09. Or make it a number field and assign something like 999 to represent UTD.

And as orange noted, the function is IIf.