ArcMap calculate field based on conditional statements for other fields

Question

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 
[NAME] = "SIR CEREDIGION - CEREDIGION"
    OR 
IF InStr([Designat_2], "VC46") AND 
[NAME] = "SIR CEREDIGION - CEREDIGION"
OR
InStr([Designat_2], "CRM") AND 
[NAME] = "SIR GAERFYRDDIN - CARMARTHENSHIRE"
    OR
InStr([Designat_2], "VC44") AND 
[NAME] = "SIR GAERFYRDDIN - CARMARTHENSHIRE"
    OR
InStr([Designat_2], "PEM") AND 
[NAME] = "SIR BENFRO - PEMBROKESHIRE"
OR
InStr([Designat_2], "VC45") AND 
[NAME] = "SIR BENFRO - PEMBROKESHIRE"
OR
InStr([Designat_2], "WWBIC")
        THEN Cat = "3"
        ELSE Cat = "4"
        END IF
ELSE
Cat =  [Designatio]

END IF

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"
    Else
        Cat = "4"
    End If
Else
    Cat = [Designatio]
End If