# Calculating Field B from Field A, except where Field B has existing value, using ArcGIS Desktop 9.3?

Im trying to calculate Field B from Field A except where Field B has existing value in ArcGIS Desktop 9.3. (I suspect its straight forward but I have 'searched' and havent cracked it yet)

Any guidance as to how to achieve this please?

• The simplest way is just to select the ones that don't have a value and calculate only those (i.e. select by attributes / fieldB is NULL). Commented Aug 31, 2014 at 23:35
• Use select by attributes, e.g. B=''. Do calculations on selection Commented Sep 1, 2014 at 0:27

EDIT Sept. 1, 2014: I have modified the code so that it would work with any field of any type where the typical blank values for each respective type are replaced. Testing for Null values is done first and separate from other tests, since a Null will cause any of the other conditional statements to throw an error. I left out a test for a blank currency field (vbCurrency), since most currency values are stored in either numeric or string fields in ArcGIS, but it could be added easily using the syntax pattern of one of the other types.

The way to do this with VBA (VB Script at 10.0) is to do an Advanced calculation with your field names substituted in the place of [FieldA] and [FieldB] using the following syntax:

Parser: VBA/VBScript

Show Codeblock: Checked

PreLogic CodeBlock:

``````InField = [FieldA]  # Change to Field Name that will replace blanks
Output = [FieldB]  # Change to the field being calculated to replace its blanks

OutVarType = VarType(Output)
If OutVarType = vbNull then  # test for Null first
Output = InField
ElseIf OutVarType >= vbInteger and OutVarType <= vbDouble Then  # Test for numbers = 0
if Output = 0 Then
Output = InField
End If
ElseIf OutVarType = vbDate Then  # Test for date that just equals Midnight
If Output = #12:00:00 AM# Then
Output = InField
End If
ElseIf OutVarType = vbString Then  # Test for string equals whitespace
If Len(Trim(Output)) = 0 Then
Output = InField
End If
End If
``````

Expression: Output

Performing a selection and then calculating over just the Null records is the other way to do this, and should be done if you have a large number of records and just a few Null values to calculate.

Doing a selection also should save you if you use this calculation on more than one field in a table and fail to update the [FieldA] or [FieldB] fields to the actual fields you wanted to calculate from the last calculation, unless you forgot to update the selection of blanks for the field you wanted to calculate also.

• That works for NULL values, what if values are 0, '' or ' '? I think the onus is on the user to decide at the time of selection what is and isn't an existing value - see comment by myself and FelixIP. Commented Sep 1, 2014 at 0:44
• Then change the expression to: If IsNull(OutField) Or OutField = 0 Then for Numeric fields and If IsNull(OutField) Or OutField <= " " Then for String fields. The select by attribute approach is still recommended if the number of Nulls/0/" " is small relative to the total number of records. Commented Sep 1, 2014 at 0:47
• I would point out that use of the calculation I have shown with the modified If Then statement would ensure that only blank values would be overwritten whether or not a selection was done in advance. A calculation of just [FieldB] requires the selection, otherwise all values would be overwritten. So my method is safer in case the selection step was forgotten. I have forgotten that step on more than one occasion and sometimes had to do a costly data recovery operation if the standard field calculation of just [FieldB] was done outside of an edit session and I did not have a backup. Commented Sep 1, 2014 at 1:00
• Been there, done that, learned not to forget! Your method works, and is safe. The user can decide at the time of execution what constitutes an empty field... like If IsNull(OutField) or len(Trim(OutField)) = 0 then or If IsNull(OutField) or OutField = 0 then. It's just simpler to do the selection first, then calculate. Commented Sep 1, 2014 at 1:08
• I like your string If Then statement better. I wish that I could ensure I would never forget, but all it takes is one rushed or distracted press of a button on the Field Calculator to trigger disaster. So many times I thought I had done every step only to see I missed a crucial one. Commented Sep 1, 2014 at 1:14