Field Calclator Issue (if Statement?)

681
3
12-14-2011 12:01 PM
ColterSikora1
New Contributor II
Hello All,

I am trying to fill data in a field through Model Builder which requires me to concatenate data from two fields and then separate the data with a hyphen.  The issue I have is that in many cases, the second field has no data and thus, there is no point to having a hyphen.  Is there a relatively simple if statement that could handle this issue?

Thank you for your help in advance!

Colter
0 Kudos
3 Replies
PSch
by
New Contributor
Dear Colter,

I'm stuck with almost the same problem. Did u find a solution?

Thx greetings
Philipp
0 Kudos
RichardFairhurst
MVP Honored Contributor
It depends if the second field contains Null values or whitespace characters or a mixture of the two.  Assuming it is a mixture (and that the first field does not also need to be tested for blank or Null) try:

Dim Output
If IsNull([MyField2]) Then
  Output = [MyField1]
Elseif Trim([MyField2]) = "" Then
  Output = [MyField1]
Else
  Output = [MyField1] & "-" & [MyField2]
End If


If you plan on exporting to a Python script than this needs to be rewritten in Python to work.  However, this post suggests that Python cannot test for Null values in the field calculator like VB ...
0 Kudos
RobertJones6
New Contributor
Python does allow for <null> check in field calculator.
Open field calculator, choose the Python Parser.
Check the "Show Codeblock" box
Enter the following in the "Pre-Logic Script Code:" box

def Concatenate(field1,field2):
  if field2 == "":
    return field1
  else:
    return field1 + '-' + field2


in the bottom box enter (change the stuff between the !'s to your actual field names):
Concatenate(str(!MyField1!), str(!MyField2!))


This works because the null values ( <null> ) are converted to empty strings before being sent to the custom "Concatenate" function build in the code block. More info can be found at this link

Additional options:
if both fields contain <null> values and/or you want to you want to retain said <null> values... this example code should help:

Parser: Python
PreLogic:
def myFunction(one,two):
  if one == "":   # check if first field is <null>
    if two == "": # check if both fields are <null> and returns <null>
      pass
    else:
      return two   # otherwise, just return the second field value
  else:                # check if the first field is populated...
    if two == "": # if second field is blank
      return one  # return just the first field
    else:             # if both fields are populated
      return one + "-" + two  # return a concatenation of the fields with a hyphen deliminator


Calculation box:
myFunction(str(!field1!), str(!field2!))
0 Kudos