Calculate Field VBA Statement If...Then...Else in ModelBuilder

[DavidJud]

Hi
I am trying to build a model which calculates the fields of a colum of a table using an if...then...else statement.
After typing the expression in the Pre-Logic VBA Script Code window I tried to run the model but the fields wouldn't change values. When i do the same thing outside the model builder it works. :confused:
Why?

2nd question:
How can i populate a field with the maximum value of 3 other fields using the Field Calculator (again in Model Builder).

Thanks for your ideas!

David

[ToreBorvik]

(edit, read the question too quickly)

[JamesGonsoski]

Without seeing the code, I can only guess...but I'll share what I've learned about If...then...else statements in calculate field...it's all in how you structure the line breaks.

Example 1:

dim result as string
if [location] = â?�HOMEâ?� then result = â?�Aâ?�
if [location] = â?�WORKâ?� then result = â?�Bâ?�

Example 2: (this works too but only as one line)

dim output as double
if [LOCATIONID]=100 then output=100 else if [LOCATIONID] = 101 then output = 101 else output = 102

Example 3:  The following code will work BUT will give the wrong result!!

dim output as double
if [LOCATIONID] = 100 then output = 100 else
if [LOCATIONID] = 101 then output = 101 else output = 102

Example 4: This is the way it should work, but the line breaks must be correct.

dim output as double
if [LOCATIONID] = 100 then
output=100
elseif [LOCATIONID] = 101 then
output = 101
else
output = 102
end if

If thereâ??s anything following the â??thenâ?? it assumes a single-line format which, of course, is quite different from the multi-line format.

Example 5: An alternative form is the SELECT CASE â?¦ CASE â?¦ ELSE ... END SELECT format, which seems to be a tad more consistent:

dim output as double
select case [LOCATIONID]
case 100
output = 101
case 101
output = 100
case 102
output = 104
else
output = 0
end select

Good luck!

Jim G.
MN Dept. Of Agriculture

[DavidWynne]

2nd question:
How can i populate a field with the maximum value of 3 other fields using the Field Calculator (again in Model Builder).

Thanks for your ideas!

David

Hi David,
As my tendencies run to Python, I would tackle this with the Calculate Field tool using Python.

An expression something like below, will return the largest of 3 field values (no code block necessary for this one).  Of course, adjust your field names as needed.
max([!FieldA!, !FieldB!, !FieldC!])

-Dave

[JeanGroth]

Hey Dave,

I have exactly the same problem with the calculator. The VBA statement works ... but not in the ModelBuilder.

Did you manage to solve the problem?

I already tried Jims statements - also not working in the modelbuilder.

Thanks,

Jean

[KimberlyMeinert]

Someone who knows more about programming than I told me that the case statement should have a "case else" statement instead of just "else".  I then got it to work in both table view and Model Builder.

[JamesGonsoski]

Someone who knows more about programming than I told me that the case statement should have a "case else" statement instead of just "else".  I then got it to work in both table view and Model Builder.

My bad. You are absolutely right. That example of mine should look like this:


Example 5: An alternative form is the SELECT CASE �?� CASE �?� ELSE ... END SELECT format, which seems to be a tad more consistent:

dim output as double
select case [LOCATIONID]
case 100
output = 101
case 101
output = 100
case 102
output = 104
case else
output = 0
end select


Jim