0

I am trying to perform a semi-complicated labeling expression that shows one field divided by another field and some additional fields with no division. The issue I ran into was that the denominator sometimes shows up as a 0. So, I need to build an if statement to perform the division if [TOT_HH_1] <> 0, do __ and if [TOT_HH_1] = 0, do __.

Also, there are three denominators that may or may not be 0. TOT_HH_1, 5 and 10.

Here is what I have so far.

Function FindLabel ([ELEM_1], [TOT_HH_1], [ELEM_5], [TOT_HH_5], [ELEM_10], [TOT_HH_10] )
  if ( [TOT_HH_1] <> 0 OR [TOT_HH_5] <> 0 OR [TOT_HH_10] <> 0) then
    FindLabel = Round ([ELEM_1] / [TOT_HH_1] , 2)  & " , " & Round ( [ELEM_5]/ [TOT_HH_5] , 2) & " , " & Round ( [ELEM_10] / [TOT_HH_10] , 2)  & "/" & [TOT_HH_1]  & " , " & [TOT_HH_5]  & " , " & [TOT_HH_10] 
  elif ( [TOT_HH_1] = 0 ) then
    FindLabel = 0  & " , " & Round ( [ELEM_5]/ [TOT_HH_5] , 2) & " , " & Round ( [ELEM_10] / [TOT_HH_10] , 2)  & "/" & [TOT_HH_1]  & " , " & [TOT_HH_5]  & " , " & [TOT_HH_10]
End Function

Edit: Correct, the code in the answer worked great, here is a picture of what the labeling looks like. enter image description here

2
  • 2
    If you want a good answer you should ask a question or explain what is happening with the code you have worked up.
    – Barrett
    Jun 15, 2016 at 23:20
  • 1
    So, what's the problem? elif is misspelled - should be else if. end if is missing. Jun 16, 2016 at 1:33

1 Answer 1

2

I'm unsure what your calculation is trying to do, or what possible values are in your fields, but a few things to consider are:

  1. VBScript doesn't have elif but rather elseif
  2. You need to end if with an end if
  3. You probably want to separate your <> 0 out into separate if statements for when some have 0 and others don't (e.g. TOT_HH_1 = 0 but TOT_HH_5 <> 0)

I've separated everything out into different if statements and set a variable for each calc. The label then returns either a 0 or the value from each calc into your full label text.

Function FindLabel ([ELEM_1], [TOT_HH_1], [ELEM_5], [TOT_HH_5], [ELEM_10], [TOT_HH_10] )
  dim val1
  dim val5
  dim val10
  val1 = val5 = val10 = 0

  if [TOT_HH_1] <> 0 then
    val1 = Round([ELEM_1] / [TOT_HH_1] , 2)
  end if

  if [TOT_HH_5] <> 0 then
    val5 = Round([ELEM_5] / [TOT_HH_5] , 2)
  end if

  if [TOT_HH_10] <> 0 then
    val10 = Round([ELEM_10] / [TOT_HH_10] , 2)
  end if

  FindLabel = val1 & ", " & val5 & ", " & val10 & " / " & [TOT_HH_1] & ", " & [TOT_HH_5] & ", " & [TOT_HH_10]
End Function

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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