AnsweredAssumed Answered

Help with Conditional Labeling

Question asked by jjb@tsgcorp.net on Oct 7, 2019

Hello, I am having some trouble with conditional label formatting and am hoping someone can help.

 

What I would like is for the labels to contain the point name along with lead concentrations that are highlighted when above 80. This VBScript seems to work fine:

Function FindLabel ( [SoilBoring.ID] , [Sheet1$.Lead])
FindLabel = ("<FNT size = '8'><BOL>" + [SoilBoring.ID] + "</BOL></FNT>")


If [Sheet1$.Lead] >= 80 Then
FindLabel = FindLabel & vbnewline & "<BGD red='255' green='255' blue='0' alpha='100' outline_red='0' outline_green='0' outline_blue='0' width='0.5'><CLR blue = '255'>" + [Sheet1$.Lead] + "</CLR></BGD>"
End If
If [Sheet1$.Lead] < 80 Then
FindLabel = FindLabel & vbnewline & "<BGD red='255' green='255' blue='255' alpha='100' outline_red='0' outline_green='0' outline_blue='0' width='0.5'><CLR blue = '255'>" + [Sheet1$.Lead] + "</CLR></BGD>"
End If
If isNull([Sheet1$.Lead]) Then
FindLabel = FindLabel & vbnewline & "<BGD red='255' green='255' blue='255' alpha='100' outline_red='0' outline_green='0' outline_blue='0' width='0.5'><CLR blue = '255'>NA</CLR></BGD>"
End If

 

End Function

The trouble is that some of the values contain ND. If I add an if statement for these, no label is generated for those particular points:

Function FindLabel ( [SoilBoring.ID] , [Sheet1$.Lead])
FindLabel = ("<FNT size = '8'><BOL>" + [SoilBoring.ID] + "</BOL></FNT>")


If inStr(1,[Sheet1$.Lead],"ND") = 1 Then
FindLabel = FindLabel & vbnewline & "<BGD red='255' green='255' blue='255' alpha='100' outline_red='0' outline_green='0' outline_blue='0' width='0.5'><CLR blue = '255'>ND</CLR></BGD>"
End If
If [Sheet1$.Lead] >= 80 Then
FindLabel = FindLabel & vbnewline & "<BGD red='255' green='255' blue='0' alpha='100' outline_red='0' outline_green='0' outline_blue='0' width='0.5'><CLR blue = '255'>" + [Sheet1$.Lead] + "</CLR></BGD>"
End If
If [Sheet1$.Lead] < 80 Then
FindLabel = FindLabel & vbnewline & "<BGD red='255' green='255' blue='255' alpha='100' outline_red='0' outline_green='0' outline_blue='0' width='0.5'><CLR blue = '255'>" + [Sheet1$.Lead] + "</CLR></BGD>"
End If
If isNull([Sheet1$.Lead]) Then
FindLabel = FindLabel & vbnewline & "<BGD red='255' green='255' blue='255' alpha='100' outline_red='0' outline_green='0' outline_blue='0' width='0.5'><CLR blue = '255'>NA</CLR></BGD>"
End If

 

End Function

If I use only that conditional statement, then the label is generated:

Function FindLabel ( [SoilBoring.ID] , [Sheet1$.Lead])
FindLabel = ("<FNT size = '8'><BOL>" + [SoilBoring.ID] + "</BOL></FNT>")


If inStr(1,[Sheet1$.Lead],"ND") = 1 Then
FindLabel = FindLabel & vbnewline & "<BGD red='255' green='255' blue='255' alpha='100' outline_red='0' outline_green='0' outline_blue='0' width='0.5'><CLR blue = '255'>ND</CLR></BGD>"
End If

 

End Function

Any ideas? Or maybe just a better way to do this? I really appreciate the help, I've been pulling out my hair all day. After I get this sorted out, I would like to add additional compounds to the label.

Thanks.

Outcomes