Attribute Assistant - InStr Operator for Parsing Street Names

Question

I am trying to parse values for my NENA NextGen 9-1-1 street name fields by using the VBScript InStr operator. For example, if the original street name field contains a USPS Publication 28 Street Suffix the expression should be able to find that prefix/suffix in the name and put it in the Pre-Type or Post-Type field, find any pre- or post-separators, and then remove the suffix and separators from the Street Name field . Here's what I have so far, although I do not get results with the Pre-Type ValueInfo expressions I have listed. I'm unsure if this is a problem with the InStr operator itself because a similar expression using Left does work. Original Road Name Fields and Values ROADNAME: Old US Highway 41A NENA Road Name Fields and (Expected) Values St_PreMod : Old St_PreDir: St_PreTyp: United States Highway St_Name: 41 St_PosType: St_PosDir: St_PosMod: Alternate I will list the following lines in the Attribute Assistant in order of their Rule Weights so they go in order: Street Name (This works) * TableName: MasterStreetName * FieldName: St_Name * ValueMethod: FIELD * ValueInfo: ROADNAME * RuleWeight: 985 * Comments: Copies the ROADNAME to NENA St_Name Street Post-Modifier (This works) * TableName: MasterStreetName * FieldName: St_PosMod * ValueMethod: EXPRESSION * ValueInfo: IIF(InStr([St_Name], "41A"), "Alternate", [St_PosMod]) * RuleWeight: 984 * Comments: If the St_Name contains 41A, set the St_PosMod to Alternate Street Pre-Modifier (This works) * TableName: MasterStreetName * FieldName: St_PreMod * ValueMethod: EXPRESSION * ValueInfo: IIF(Left([St_Name], 4) = "Old ", "Old", "") * RuleWeight: 983 * Comments: If the St_Name starts with Old + a space, set the St_PreMod to Old Street Pre-Type (Neither method works) * TableName: MasterStreetName * FieldName: St_PreTyp * ValueMethod: EXPRESSION * ValueInfo:* IIF(InStr([St_Name], "US Highway "), "United States Highway", "") * IIF(InStr([St_Name], "US Highway "), "United States Highway", [St_PreTyp]) * RuleWeight: 983 * Comments: If the St_Name starts with the NENA type, set the St_PreTyp to that value Street Name (This works) * TableName: MasterStreetName * FieldName: St_Name * ValueMethod: EXPRESSION * ValueInfo: TRIM(Replace([St_Name], "Old ", "")) * RuleWeight: 982 * Comments: Replace the equivalent NENA pre- or post-type in the St_Name with nothing Street Name (This works) * TableName: MasterStreetName * FieldName: St_Name * ValueMethod: EXPRESSION * ValueInfo: TRIM(Replace([St_Name], "US Highway ", "")) * RuleWeight: 982 * Comments: Replace the equivalent NENA pre- or post-type in the St_Name with nothing Street Name (This works) * TableName: MasterStreetName * FieldName: St_Name * ValueMethod: EXPRESSION * ValueInfo: TRIM(Replace([St_Name], "41A", "41")) * RuleWeight: 981 * Comments: Replace 41A with 41. NENA Road Name Fields and (Resultant) Values St_PreMod : Old St_PreDir: St_PreTyp: St_Name: 41 St_PosType: St_PosDir: St_PosMod: Alternate It looks like the InStr operator fails. This is not the only situation I have this problem. We have Spanish streets names that need their pre-types and separators parsed as well. What is odd is that I have two identical expressions that have different results: Street Pre-Type (Different Results) * TableName: MasterStreetName * FieldName: St_PreTyp * ValueMethod: EXPRESSION * ValueInfo:* IIF([St_Name] = "Via Del Mar", "del", "") Does not work * IIF([St_Name] = "Vista Del Rio", "del", "") This works * RuleWeight: 983 * Comments: If the St_Name starts with the NENA type, set the St_PreTyp to that value The InStr operator is inconsistent buts tends to not work the way I have them coded. Any help is welcome. Thank you.

JeffSilberberg · Answer

Okay,  In your Street Pre-Type (Neither method works) What doesn't work ..  I tried it in a Fiddle and it appeared to work for me..   Returning the True value in both IIF statements based on the ROADNAME: Old US Highway 41A value.   In your second issue Street Pre-Type (Different Results) there is no inStr() not sure what does not work.

Most these should use the third value of the inStr(String, Find, Compare)  Where the default f the third value is 0 (Case Sensitive) verses 1 (Case Insensitive).  Since I never worked in ArcMAP that's about all I have to offer.  Hope this helps more.

JasonBalmut · Answer

@JeffSilberberg, our organization is still using ArcMap with the Attribute Assistant add-in until we can convert to ArcGIS Pro.  Until then, we are stuck with VBScript and unable to use Arcade.  I'm familiar with the NENA best practices and just need help with the InStr operator for this function.  Thanks.

JeffSilberberg · Answer

I can't specifically help you with the IIF issue, although I thought it was single quotes not double. But I might refer you to the NENA document for parsing the CFS address

NENA Next Generation 9-1-1 (NG9-1-1) United States
Civic Location Data Exchange Format (CLDXF) Standard
NENA-STA-004.1.1-2014, March 23, 2014

for a lot of good rules on address parsing and the related APCO cfsinfo.pdf  document as well. I had a link to the latter it but it seems broken ..

Also, you should look at this community post - https://community.esri.com/t5/arcgis-online-questions/multiple-iif-statements-in-arcade-expression/td-p/485731

Hope this helps --