Calculated Field Perform Calc on field - on count if the value is one of...

[Cozmo2]

I am trying to add how many records have the Ethnic value of "P", "U", or "W". I have tried different variations of:

iif (query.ethnic_cod = ["P", "U", "W"], 0, 1) and the report errors out.

Please advise.

[Cozmo2]

How do you code the expression when you are looking for the value from the query to be one of a list of values?

[EddieLotter]

Use the following:

iif (FindNoCase(query.ethnic_cod, "PUW") eq 0, 0, 1)

This will return 0 if the ethnic_code is "P" or "U" or "W", otherwise it will return 1. Switch the 0 and 1 to get the opposite result, which I suspect is what you really want.

[Cozmo2]

I am trying to count the ethnic codes that are not "P", "U", "A". This code

gives me a value of zero (there are 13). If I switch the 0 and 1 I get a

value or 157 (total of all the records).

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Thu, Jan 29, 2015 at 12:32 PM, Eddie Lotter <forums_noreply@adobe.com>

[EddieLotter]

Cozmo2 wrote:

This code gives me a value of zero (there are 13).

The FindNoCase() function simply searches for one string within another.

If your ethnic_cod field is more than one character wide then this approach will not work.

Is the ethnic_cod field exactly one character wide?

[Cozmo2]

Yes, it is 2 characters wide.

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Mon, Feb 2, 2015 at 10:39 AM, Eddie Lotter <forums_noreply@adobe.com>

[EddieLotter · Accepted solution]

Ah, that explains it. If the field is defined as char(2) then single letter codes will have a trailing space.

Try the following:

iif (FindNoCase(trim(query.ethnic_cod), "PUW") eq 0, 0, 1)

The trim() function will remove a leading or trailing space.

It is important to understand that this solution still has the problem that if you have a code of "PU" or "UW" then the statement will return 0. Which is not what you want.

If you can confirm that the ethnic_cod field has a space after the "P" or "U" or "W" then a safer algorithm would be the following:

iif(FindNoCase(query.ethnic_cod, "P ,U ,W ") eq 0, 0, 1)

Instead of stripping the space from the field I have added the space after each letter in the second string of the FindNoCase() function. I am assuming your ethnic_cod field does not use the comma character, as I'm using it as a delimiter to prevent a match with a leading space.

I am also assuming the code is not case sensitive. If it is then use the Find() function rather than the FindNoCase() function.

Do you understand the logic behind what I am showing you?

[Cozmo2]

Thank you for the help and the explanation.

[EddieLotter]

You're welcome Bonni.

What solution did you end up using?

[Cozmo2]

The one you said was the correct way. It made sense!

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Mon, Feb 2, 2015 at 3:21 PM, Eddie Lotter <forums_noreply@adobe.com>