Copy link to clipboard
Copied
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.
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
...Copy link to clipboard
Copied
How do you code the expression when you are looking for the value from the query to be one of a list of values?
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
Thank you for the help and the explanation.
Copy link to clipboard
Copied
You're welcome Bonni.
What solution did you end up using?
Copy link to clipboard
Copied
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>