Attribute Assistant: DynamicValue table syntax: Conditional IIF

Question

I've looked for some help on conditional Value Info statements and just can't find much. I have address points that are populated with both house numbers and unit information. I am wanting to calculate a label field with the house number if the unit number is blank, otherwise I want the label to contain the unit information. My if statement is as follows:

IIF([unit_num]="", [stnum], [stnumsuf])

This is modeled after the example on the SQL Server 2012 Transact-SQL Microsoft tech page. What this returns, however, is just the unit information (stnumsuf).

Windows Server 2008 Standard
ArcGIS 10.2
AA 10.2 add-in
SQL Server 2012 Express

Same behavior on:
Windows 7 x64
ArcGIS 10.2
AA 10.2 add-in
SQL Server 2012 Express

Any help would be appreciated,
James

MikeMillerGIS · Answer

Even though the data is not NULL, try this

IIF(isNull([unit_num]), [stnum], [stnumsuf])

we are using the MS Script Control to process these expressions and it can be picky.  We did code for many of these cases and if I recall correctly, the IsNull() we added should handle blank.  Let me know if it works and if not, I can crack open the source and see what is going on.

Anonymous User · Answer

Are you sure your unit_num field is an empty string and not NULL. Also, if your data is stored in SQL Server, you need to use single quotes, not double quotes. You could try this to take care of either case:

IIF([unit_num]='' OR [unit_num] IS NULL, [stnum], [stnumsuf])

Hey Todd,
Thanks for the reply. I am certain the field is not NULL, and have tried both double and single quotes previously. However, just for due diligence, I took your string and pasted it in my VALUEINFO field for that record and ran it manually on a selected set of 15 records (some with [unit_num] values and some that were blank): same results.

Just to be clear, my AA is actually working. For example, if I enter just [stnum], I get all house numbers in my label field. For another record, I have a rather long and nested Trim(Replace(Replace)) statement that works fine as well. It just seems to be hanging up on this conditional statement.

ToddZimmerman · Answer

Are you sure your unit_num field is an empty string and not NULL. Also, if your data is stored in SQL Server, you need to use single quotes, not double quotes. You could try this to take care of either case:

IIF([unit_num]='' OR [unit_num] IS NULL, [stnum], [stnumsuf])