Calculate Field Tool - Wildcards

Question

Good day everyone,

Tech support, after four days and countless screen sharing sessions, hasn't been any help on this so I'll post my question to all of those that REALLY know the software.

I am trying to use a wild card in the calulate field tool to find all records that begin with "MG". Souds easy right? I thought so too. My OS is Windows 7, I am using ArcInfo 10 and I am running the tool on a File Geodatabase. Here is the offending piece of code:

Dim x 
If len( [AccountInfo_PERMIT_NO] ) = "1" Then
 x = "0000"+ [AccountInfo_PERMIT_NO] 
ElseIf len( [AccountInfo_PERMIT_NO] ) = "2" Then
 x = "000"+ [AccountInfo_PERMIT_NO]
ElseIf len( [AccountInfo_PERMIT_NO] ) = "3" Then
 x = "00"+ [AccountInfo_PERMIT_NO] 
ElseIf len( [AccountInfo_PERMIT_NO] ) = "4" Then
 x = "0"+ [AccountInfo_PERMIT_NO]
ElseIf len( [AccountInfo_PERMIT_NO] ) = "5" Then
 x = [AccountInfo_PERMIT_NO] 
ElseIf [AccountInfo_PERMIT_NO] LIKE 'MG%' Then
 x = [AccountInfo_PERMIT_NO] 
Else
 x="XYZ"
End If

If I remove the code that is in red, the tool runs just fine. I guess I don't know as much about wildcards as I thought.

Thanks in advance for any help you may have.

Tom S.

TomSedlacek · Answer

Thanks Darren, that did the trick.  I knew it was simple, I just couldn't come up with it.  Thanks to Niklas as well as I am working on some self study to learn Python.

Tom S.

NiklasNorrthon · Answer

In python the code above for CalculateField can be rewritten to (code block):
def format_permit_no(x):
    if len(x) > 5:
        return x if x.startswith('MG') else 'XYZ'
    else:
        return ('%5s' % x). replace(' ', '0')

And expression becomes:
format_permit_no(!AccountInfo_PERMIT_NO!)

DarrenWiens2 · Answer

I believe it should be:ElseIf left([AccountInfo_PERMIT_NO],2) = "MG" Then

"If the first two characters equal MG"

LIKE is SQL, but you're writing VBScript.

(I would normally test this before posting, but my ArcGIS is tied up)