I am using ArcGIS 10.2 for Desktop, and I've been looking through this site, and can't seem to figure out my answer yet. Very new to VBA and Python, but lots of years with ArcGIS. I know I can do this the slow way with Select By Attributes, but It's time consuming.

I am attempting a spatial join between cases of a disease (points layer) and US Census Tracts (polygon layer). This requires count data. For each point/case, I have a field called YEAR with date ranges 2001 to 2012 depending on what year the case happened. I need a count column for each year. For example, the first one I'm calling COUNT01. If the case date in YEAR is 2001, then COUNT01 will have a 1 in it. If it is any other year (2002-2012), then there will need to be a 0. I will have COUNT02, COUNT03...COUNT12 columns. Can't have a "Null" value.

Here's what I've tried so far

enter image description here

And here is what the data looks like in Arc.

enter image description here

  • 3
    I recommend that you do this using Python and edit your Question to replace the vba and vbscript tags with a python tag. Although not gone yet VBScript is in the process of being deprecated. I expect it will easy to do with the Python parser once you provide a more detailed description which includes a picture/table of some sample rows showing actual input and expected output. Is YEAR a date field or an integer field containing values corresponding to years? – PolyGeo Jan 2 '14 at 23:35
  • Thanks for the advice and help. I've added an image of the attribute table. The YEAR input variable is currently stored as a Double (It was parsed out from a SAS Date), and the output variable column Counts01 is stored as Short Integer. – BenW Jan 3 '14 at 0:37
  • 1
    Have you considered using Summary Statistics with YEAR as your case field? That should give you the COUNT for each year without needing to use Python or the Field Calculator. – PolyGeo Jan 3 '14 at 0:50
  • Thanks for your advice. I did consider Summary Statistics, but the final goal is to have yearly case counts per the 1,249 individual census tracts from which I can calculate annual and 10yr average incidence rates per 100k. Some census tracts have had no cases, some have had a ton, but I need them all to be included. Even summarizing Tracts by year omits 278 of those tracts with no cases. – BenW Jan 3 '14 at 17:13

I think this is what you want .... using python you could do the following (assumes YEAR and COUNT0X fields are integers)

  • Change to Python as highlighted below
  • Add the code to the 'Pre Logic' input box
  • Change the yearVal as needed for each field you calculate (Count01 would use 2001, Count02 would use 2002, etc)

Note Python using indentation to parse the code so ensure the spacing is correct.

def myCalc(year,yearVal):
  if (year == yearVal):
    return 1
    return 0


  • 3
    @BenW - if this solved your problem, please mark this as the answer (check box under the answer vote count). – Chad Cooper Jan 9 '14 at 13:50
  • What to do if I want to check a string value in the if statement what contains accentuated letters? – greyline Oct 22 '14 at 12:18
  • What to do if the fields are on another type e.g. text, double, etc? – khaliff Nov 24 '17 at 0:10
  • @khaliff you could insert some type conversion function in the python code For example if (int(year) == yearVal): , or others (see : informit.com/articles/article.aspx?p=459269&seqNum=7). – gisnside Nov 26 '17 at 12:35
  • Thank you. Do you also have a link where I can find about when to use myCalc, myFunc, Reclass, etc. ? – khaliff Nov 27 '17 at 0:34

This answer is essentially the same as listed above, however it's a way to not have to use the code block... for the Count01 field, you would set the parser to Python and then set your calculation to

1 if !YEAR! == 2001 else 0

The way this reads is: Set the field to 1 if the YEAR field is 2001, if it's not 2001, then set it to 0...

If you have multiple if conditions, you can nest the 2nd (and subsequent) "if" conditions inside the else statement, such as this...

1 if !YEAR! == 2001 else (2 if !YEAR! == 2002 else 0)

The way this reads is: Set the field to 1 if the YEAR field is 2001, if it's not 2001, then set it to 2 if it's 2002, if not, then set it to 0...

  • How should I modify this calculation if I have more than one condition, I mean as if we are using "elif"? – khaliff Nov 24 '17 at 0:09
  • If you have more than one condition, you can nest the 2nd (and subsequent) "if" conditions inside the else statement... so you might end up with something like this... 1 if !YEAR! == 2001 else (2 if !YEAR! == 2002 else (3 if !YEAR! == 2003 else 0)) – Jason Miller Nov 26 '17 at 11:11

If you are going to use VBScript then your field calculation was set up wrong. Firstly I would not use a variable which is the name of the field, this is confusing, use a different name so its crystal clear what you were setting. Your "endif" was wrong it should be "end if" and your code should have been in the pre-logic script section. The correct way of setting this up is shown below. But as the others have said, try not to use VBScript as ESRI are hell bent on getting rid of it in favour of Python.

Correct Field Calculator Usage

  • Thanks very much for your help. I ended up going with Python as that seems to be the overall consensus. – BenW Jan 3 '14 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.