I have a large dataset of species occurrence records. One field, (call it "Status_Full") contains a string of abbreviations of each designated statutory status for each record. Another field (call it "Status_Code") is a numeric field with values 1-4 donating the conservation importance of each record based on which abbreviations are in the "Status_Full" field, where 1 = "Priority", 2 = "Concern", 3 = "Locally Important" and 4 = everything else.

The "Status_Code" field has a value of 3 for any record that has an abbreviation in "Status_Full" that denotes local importance, but does not have an abbreviation that denotes higher conservation importance. At the moment a record is given a code of 3 if the "Status_Full" field contains an abbreviation that denotes local importance anywhere, e.g. if the string contains LI[VC54] ("Locally important in Vice-County 54), the record will show as locally important on the map even if it is not actually located in VC54, and hence not actually locally important. These fields were all calculated in another database before being extracted and imported into ArcMap 10.5. I now want to make a new field that gives a code value of "3" only if the previous "Status_Code" field was a 3, and the corresponding abbreviation in the string in the "Status_Full" field actually matches up with the region it is located in - contained in another field, call it "Region".

Basically I want something that says:

If [Status_Code] = 3

and [Status_Full] contains any of "abbr_1_Region_X", "abbr_2_Region_X" up to "abbr_n_Region_X"

And [Region] = "Region_X"

Then [Status_Code_New] = 3. Otherwise [Status_Code_New] = 4.

If [Status_Code] != 3, then [Status_Code_New] = [Status_Code].

If I were interested in many different regions, trying to specify all the combinations would get unmanageable pretty quickly, but I'm only interested in 3 different regions.

Below is code I tried running in the Field Calculator codeblock. I had to remove all of the link breaks to make it run. When I try to run it though, I get an "Error 99999" message, Syntax error. But all of the terms used below work to reclassify fields in isolation, it's only when I combine them all like this that it breaks. I suspect it might be easier to achieve using the Python parser, but I don't know quite where to start with that.

In the code below [Designat_2] = "Status_Full", [Designatio] = "Status_Code", [Status_Code_New] = "Cat" [NAME] = "Region"

Apologies for the confusing field names, I didn't make the database and unfortunately can't change any of them.

IF [Designatio] = "3" THEN
IF InStr([Designat_2], "CER") AND 
IF InStr([Designat_2], "VC46") AND 
InStr([Designat_2], "CRM") AND 
InStr([Designat_2], "VC44") AND 
InStr([Designat_2], "PEM") AND 
InStr([Designat_2], "VC45") AND 
InStr([Designat_2], "WWBIC")
        THEN Cat = "3"
        ELSE Cat = "4"
        END IF
Cat =  [Designatio]


1 Answer 1


There are a couple of issues with your code:

  • On line 4/5 you have OR IF which should be just OR
  • The InStr() method is not a boolean, but returns either 0 if the string is not found, or the index of the found string. So you need to use Instr(...) > 0 to see if Designat_2 contains a given string.
  • I'm not sure what the evaluation order is, but I think I'd add some parentheses

With proper indentation to make it a little bit easier to read:

If [Designatio] = "3" Then
    If (InStr([Designat_2], "CER") > 0 And [NAME] = "SIR CEREDIGION - CEREDIGION") Or 
       (InStr([Designat_2], "VC46") > 0 And [NAME] = "SIR CEREDIGION - CEREDIGION") Or 
       (InStr([Designat_2], "CRM") > 0 And  [NAME] = "SIR GAERFYRDDIN - CARMARTHENSHIRE") Or 
       (InStr([Designat_2], "VC44") > 0 And [NAME] = "SIR GAERFYRDDIN - CARMARTHENSHIRE") Or 
       (InStr([Designat_2], "PEM") > 0 And [NAME] = "SIR BENFRO - PEMBROKESHIRE") Or 
       (InStr([Designat_2], "VC45") > 0 And [NAME] = "SIR BENFRO - PEMBROKESHIRE") Or 
       (InStr([Designat_2], "WWBIC") > 0) Then
        Cat = "3"
        Cat = "4"
    End If
    Cat = [Designatio]
End If
  • Thanks a lot for pointing out those comments. I've updated and run the code as you suggested. The Calculate Field tool executes now, where it wouldn't before, but I get a warning message "000405: No records within table.". If I then use a definition query to search for records where "Cat" = 3, only one record is returned, but I know through scrolling through the attribute table that there are many more records that fit the criteria...
    – Yusef_S
    Dec 10, 2019 at 14:48
  • @biogeo Is everything in your data upper case? Because InStr by default uses a case sensitive comparison. Do you have an example of a record that should match but does not?
    – Berend
    Dec 10, 2019 at 14:56
  • Yes, everything is upper case, or at least the strings I have selected for are all upper case. As an example of a record that should match but does not, one record I have just found contains the following string as it's "Designat_2" value: "RDB1 [Wales] - LC, LI[VC44, LS], LI[VC48, LR], LI[VC52, LR], LI[WWBIC]" As I didn't even specify a matching region in the "NAME" field for the "WWBIC" string, I would have thought that should have been selected and classified as "Cat" = 3, but it hasn't happened. Same for many others.
    – Yusef_S
    Dec 10, 2019 at 15:13
  • You may want to try this in in a clean mxd. Make sure the NAME field does not have trailing spaces. And see here: gis.stackexchange.com/questions/136650/…
    – Berend
    Dec 10, 2019 at 15:21
  • Tried importing the shapefile into a blank map document and running it again. Now the exact same code that previously executed and returned a 000405 warning message fails to execute and returns a 999999 error message: Error executing function: syntax error :'(
    – Yusef_S
    Dec 10, 2019 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.