Hi AmyTheJoy
There's nothing wrong with your formula, but it's assuming a few things. In your first example,
1. {FLATDATA.ANAM} always contains data. If it doesn't you need to allow for null fields.
2. Arsenic is exactly this - it is in proper case (eg the A is capitalised & the rest is lowercase) and there are no spaces around it. Thus if the actual data of {FLATDATA.ANAM} was "ARSENIC " it wouldn't find it (as it's in capitals & it has two spaces at the end).
I'm assuming that {FLATDATA.ANAM} is a free text field & not tied to a code list?
If {FLATDATA.ANAM} must always contain a result then try:
if trim(lowercase({FLATDATA.ANAM}) = "arsenic" then {FLATDATA.ACOM} else ""
If it can be null, or contain no characters, try:
If isnull({FLATADATA.ANAM} or totext({FLATDATA}="" then "" else
if trim(lowercase({FLATDATA.ANAM}) = "arsenic" then {FLATDATA.ACOM} else ""
What this is doing:
the use of "isnull" is checking to see if there is no entry in the field.
the use of "totext" is checking to see if there is an entry of zero characters in this field.
the use of "trim" is removing any spaces around your field (eg it will allow for spaces before or after the word arsenic.
The use of "lowercase" is forcing the contents of your field to be lowercase, so it is comparing arsenic with arsenic, not ARSENIC or anything else.
You can always test this by replacing the "" with different words "bob", "fred", "jane" to see that the combination is working.
If you are looking at a code list, then I'd check the raw data of your code list, in case someone has replaced "Arsenic" with other versions (eg "arsenic", "Arsenic ", " Arsenic " etc, etc.
Hope this helps
IP Logged