1 / 18

Labeling “ Chemboxes ” in ArcGIS

Labeling “ Chemboxes ” in ArcGIS. Automating Sampling D ata L abels. Problem. We needed constantly updated maps of groundwater well sampling results. The n eed for maps showing one year of data or three years of data. Not too many examples of this on the Internet. Solution.

tracey
Télécharger la présentation

Labeling “ Chemboxes ” in ArcGIS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Labeling “Chemboxes” in ArcGIS Automating SamplingData Labels

  2. Problem • We needed constantly updated maps of groundwater well sampling results. • The need for maps showing one year of data or three years of data. • Not too many examples of this on the Internet. Solution • Automate labeling using VBScript Expression

  3. Benefits of Scripting • Easy to update data on the map • Time frame of data shown can easily be changed • Results above an action level can be shown in bold to stand out. • Avoid typos and incorrect data. Data errors can be blamed on the lab. • Data is presented uniformly.

  4. One Year sample of Data

  5. Three years of sample data

  6. Process Dim w w = 2 bold="<_BOL>" unbold = "</_BOL>" Dim TheLabel If InStr([Res_results2.N2009_01], "<") Then [Res_results2.N2009_01] = Replace([Res_results2.N2009_01], "<", "&lt;") Else [Res_results2.N2009_01] = [Res_results2.N2009_01] End If If IsNumeric(trim([Res_results2.N2009_01])) Then If cint(trim([Res_results2.N2009_01])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_01]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_01] & " (1/09)" & unbold bold="<_BOL>" unbold = "</_BOL>" End if If InStr([Res_results2.N2009_02], "<") Then [Res_results2.N2009_02] = Replace([Res_results2.N2009_02], "<", "&lt;") Else [Res_results2.N2009_02] = [Res_results2.N2009_02] End If If IsNumeric(trim([Res_results2.N2009_02])) Then If cint(trim([Res_results2.N2009_02])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_02]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_02] & " (2/09)" & unbold end if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2009_03], "<") Then [Res_results2.N2009_03] = Replace([Res_results2.N2009_03], "<", "&lt;") Else [Res_results2.N2009_03] = [Res_results2.N2009_03] End If If IsNumeric(trim([Res_results2.N2009_03])) Then If cint(trim([Res_results2.N2009_03])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_03]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_03] & " (3/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.A2009_04], "<") Then [Res_results2.A2009_04] = Replace([Res_results2.A2009_04], "<", "&lt;") Else [Res_results2.A2009_04] = [Res_results2.A2009_04] End If If IsNumeric(trim([Res_results2.A2009_04])) Then If cint(trim([Res_results2.A2009_04])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.A2009_04]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.A2009_04] & " (4/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2009_06], "<") Then [Res_results2.N2009_06] = Replace([Res_results2.N2009_06], "<", "&lt;") Else [Res_results2.N2009_06] = [Res_results2.N2009_06] End If If IsNumeric(trim([Res_results2.N2009_06])) Then If cint(trim([Res_results2.N2009_06])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_06]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_06] & " (6/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2009_07], "<") Then [Res_results2.N2009_07] = Replace([Res_results2.N2009_07], "<", "&lt;") Else [Res_results2.N2009_07] = [Res_results2.N2009_07] End If If IsNumeric(trim([Res_results2.N2009_07])) Then If cint(trim([Res_results2.N2009_07])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_07]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_07] & " (7/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2009_08], "<") Then [Res_results2.N2009_08] = Replace([Res_results2.N2009_08], "<", "&lt;") Else [Res_results2.N2009_08] = [Res_results2.N2009_08] End If If IsNumeric(trim([Res_results2.N2009_08])) Then If cint(trim([Res_results2.N2009_08])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_08]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_08] & " (8/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.A2009_09], "<") Then [Res_results2.A2009_09] = Replace([Res_results2.A2009_09], "<", "&lt;") Else [Res_results2.A2009_09] = [Res_results2.A2009_09] End If If IsNumeric(trim([Res_results2.A2009_09])) Then If cint(trim([Res_results2.A2009_09])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.A2009_09]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.A2009_09] & " (9/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2009_11], "<") Then [Res_results2.N2009_11] = Replace([Res_results2.N2009_11], "<", "&lt;") Else [Res_results2.N2009_11] = [Res_results2.N2009_11] End If If IsNumeric(trim([Res_results2.N2009_11])) Then If cint(trim([Res_results2.N2009_11])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_11]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_11] & " (11/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2009_12], "<") Then [Res_results2.N2009_12] = Replace([Res_results2.N2009_12], "<", "&lt;") Else [Res_results2.N2009_12] = [Res_results2.N2009_12] End If If IsNumeric(trim([Res_results2.N2009_12])) Then If cint(trim([Res_results2.N2009_12])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_12]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_12] & " (12/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2010_03], "<") Then [Res_results2.N2010_03] = Replace([Res_results2.N2010_03], "<", "&lt;") Else [Res_results2.N2010_03] = [Res_results2.N2010_03] End If If IsNumeric(trim([Res_results2.N2010_03])) Then If cint(trim([Res_results2.N2010_03])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2010_03]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2010_03] & " (3/10)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2010_06], "<") Then [Res_results2.N2010_06] = Replace([Res_results2.N2010_06], "<", "&lt;") Else [Res_results2.N2010_06] = [Res_results2.N2010_06] End If If IsNumeric(trim([Res_results2.N2010_06])) Then If cint(trim([Res_results2.N2010_06])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2010_06]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2010_06] & " (6/10)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2010_07], "<") Then [Res_results2.N2010_07] = Replace([Res_results2.N2010_07], "<", "&lt;") Else [Res_results2.N2010_07] = [Res_results2.N2010_07] End If If IsNumeric(trim([Res_results2.N2010_07])) Then If cint(trim([Res_results2.N2010_07])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2010_07]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2010_07] & " (7/10)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2010_09], "<") Then [Res_results2.N2010_09] = Replace([Res_results2.N2010_09], "<", "&lt;") Else [Res_results2.N2010_09] = [Res_results2.N2010_09] End If If IsNumeric(trim([Res_results2.N2010_09])) Then If cint(trim([Res_results2.N2010_09])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2010_09]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2010_09] & " (9/10)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2010_10], "<") Then [Res_results2.N2010_10] = Replace([Res_results2.N2010_10], "<", "&lt;") Else [Res_results2.N2010_10] = [Res_results2.N2010_10] End If If IsNumeric(trim([Res_results2.N2010_10])) Then If cint(trim([Res_results2.N2010_10])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2010_10]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2010_10] & " (10/10)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2010_11], "<") Then [Res_results2.N2010_11] = Replace([Res_results2.N2010_11], "<", "&lt;") Else [Res_results2.N2010_11] = [Res_results2.N2010_11] End If If IsNumeric(trim([Res_results2.N2010_11])) Then If cint(trim([Res_results2.N2010_11])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2010_11]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2010_11] & " (11/10)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2010_12], "<") Then [Res_results2.N2010_12] = Replace([Res_results2.N2010_12], "<", "&lt;") Else [Res_results2.N2010_12] = [Res_results2.N2010_12] End If If IsNumeric(trim([Res_results2.N2010_12])) Then If cint(trim([Res_results2.N2010_12])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2010_12]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2010_12] & " (12/10)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_01], "<") Then [Res_results2.N2011_01] = Replace([Res_results2.N2011_01], "<", "&lt;") Else [Res_results2.N2011_01] = [Res_results2.N2011_01] End If If IsNumeric(trim([Res_results2.N2011_01])) Then If cint(trim([Res_results2.N2011_01])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_01]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2011_01] & " (1/11)" & unbold bold="<_BOL>" unbold = "</_BOL>" End if If InStr([Res_results2.N2011_02], "<") Then [Res_results2.N2011_02] = Replace([Res_results2.N2011_02], "<", "&lt;") Else [Res_results2.N2011_02] = [Res_results2.N2011_02] End If If IsNumeric(trim([Res_results2.N2011_02])) Then If cint(trim([Res_results2.N2011_02])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End IF If trim([Res_results2.N2011_02]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_02] & " (2/11)"& unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_03], "<") Then [Res_results2.N2011_03] = Replace([Res_results2.N2011_03], "<", "&lt;") Else [Res_results2.N2011_03] = [Res_results2.N2011_03] End If If IsNumeric(trim([Res_results2.N2011_03])) Then If cint(trim([Res_results2.N2011_03])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_03]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_03] & " (3/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_04], "<") Then [Res_results2.N2011_04] = Replace([Res_results2.N2011_04], "<", "&lt;") Else [Res_results2.N2011_04] = [Res_results2.N2011_04] End If If IsNumeric(trim([Res_results2.N2011_04])) Then If cint(trim([Res_results2.N2011_04])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_04]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_04] & " (4/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_05], "<") Then [Res_results2.N2011_05] = Replace([Res_results2.N2011_05], "<", "&lt;") Else [Res_results2.N2011_05] = [Res_results2.N2011_05] End If If IsNumeric(trim([Res_results2.N2011_05])) Then If cint(trim([Res_results2.N2011_05])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_05]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_05] & " (5/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_06], "<") Then [Res_results2.N2011_06] = Replace([Res_results2.N2011_06], "<", "&lt;") Else [Res_results2.N2011_06] = [Res_results2.N2011_06] End If If IsNumeric(trim([Res_results2.N2011_06])) Then If cint(trim([Res_results2.N2011_06])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_06]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_06] & " (6/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_07], "<") Then [Res_results2.N2011_07] = Replace([Res_results2.N2011_07], "<", "&lt;") Else [Res_results2.N2011_07] = [Res_results2.N2011_07] End If If IsNumeric(trim([Res_results2.N2011_07])) Then If cint(trim([Res_results2.N2011_07])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_07]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_07] & " (7/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_08], "<") Then [Res_results2.N2011_08] = Replace([Res_results2.N2011_08], "<", "&lt;") Else [Res_results2.N2011_08] = [Res_results2.N2011_08] End If If IsNumeric(trim([Res_results2.N2011_08])) Then If cint(trim([Res_results2.N2011_08])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_08]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_08] & " (8/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_09], "<") Then [Res_results2.N2011_09] = Replace([Res_results2.N2011_09], "<", "&lt;") Else [Res_results2.N2011_09] = [Res_results2.N2011_09] End If If IsNumeric(trim([Res_results2.N2011_09])) Then If cint(trim([Res_results2.N2011_09])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_09]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_09] & " (9/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_10], "<") Then [Res_results2.N2011_10] = Replace([Res_results2.N2011_10], "<", "&lt;") Else [Res_results2.N2011_10] = [Res_results2.N2011_10] End If If IsNumeric(trim([Res_results2.N2011_10])) Then If cint(trim([Res_results2.N2011_10])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_10]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_10] & " (10/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_11], "<") Then [Res_results2.N2011_11] = Replace([Res_results2.N2011_11], "<", "&lt;") Else [Res_results2.N2011_11] = [Res_results2.N2011_11] End If If IsNumeric(trim([Res_results2.N2011_11])) Then If cint(trim([Res_results2.N2011_11])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_11]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_11] & " (11/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_12], "<") Then [Res_results2.N2011_12] = Replace([Res_results2.N2011_12], "<", "&lt;") Else [Res_results2.N2011_12] = [Res_results2.N2011_12] End If If IsNumeric(trim([Res_results2.N2011_12])) Then If cint(trim([Res_results2.N2011_12])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_12]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_12] & " (12/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2012_02], "<") Then [Res_results2.N2012_02] = Replace([Res_results2.N2012_02], "<", "&lt;") Else [Res_results2.N2012_02] = [Res_results2.N2012_02] End If If IsNumeric(trim([Res_results2.N2012_02])) Then If cint(trim([Res_results2.N2012_02])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2012_02]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2012_02] & " (2/12)" & unbold End If TheLabel = Replace([Perchlorate_Monitoring_Locations.WC_Location], "&", "&amp;") & TheLabel FindLabel = TheLabel End Function Flatten data tables (data from Lab or data validation) Join data to shapefile containing sample locations Write VBScript Expression

  7. Flatten data tables • Pivot Table in Excel • Or build a query to output a flattened table in Microsoft Access • Also know as denormalizing a database This makes it easier to join your Sample/Well Locations to your sample data. Allows for simple access to data for scripting labels in ArcGIS. No need for data arrays or FOR NEXT programming Loops. Function FindLabel ( [Perchlorate_Monitoring_Locations.LOCATION], [Perchlorate_Monitoring_Locations.WC_Location] , [Perchlorate_Monitoring_Locations.PARID], [Res_results2.N2012_02] , [Res_results2.N2011_12] , [Res_results2.N2011_11] , [Res_results2.N2011_10] , [Res_results2.N2011_09] , [Res_results2.N2011_08] , [Res_results2.N2011_07] , [Res_results2.N2011_06] , [Res_results2.N2011_05] , [Res_results2.N2011_04] , [Res_results2.N2011_03] , [Res_results2.N2011_02] , [Res_results2.N2011_01], [Res_results2.N2009_01], [Res_results2.N2009_02], [Res_results2.N2009_03], [Res_results2.N2009_04], [Res_results2.N2009_06], [Res_results2.N2009_07], [Res_results2.N2009_08] , [Res_results2.N2009_09] , [Res_results2.N2009_11] , [Res_results2.N2009_12] , [Res_results2.N2010_03] , [Res_results2.N2010_06] , [Res_results2.N2010_07] , [Res_results2.N2010_09] , [Res_results2.N2010_10] , [Res_results2.N2010_11] , [Res_results2.N2010_12], [Res_results2.A2009_04] , [Res_results2.A2009_09] ) Dim w w = 2 bold="<_BOL>" unbold = "</_BOL>" Dim TheLabel If InStr([Res_results2.N2009_01], "<") Then [Res_results2.N2009_01] = Replace([Res_results2.N2009_01], "<", "&lt;") Else [Res_results2.N2009_01] = [Res_results2.N2009_01] End If If IsNumeric(trim([Res_results2.N2009_01])) Then If cint(trim([Res_results2.N2009_01])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_01]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_01] & " (1/09)" & unbold bold="<_BOL>" unbold = "</_BOL>" End if If InStr([Res_results2.N2009_02], "<") Then [Res_results2.N2009_02] = Replace([Res_results2.N2009_02], "<", "&lt;") Else [Res_results2.N2009_02] = [Res_results2.N2009_02] End If If IsNumeric(trim([Res_results2.N2009_02])) Then If cint(trim([Res_results2.N2009_02])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_02]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_02] & " (2/09)" & unbold end if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2009_03], "<") Then [Res_results2.N2009_03] = Replace([Res_results2.N2009_03], "<", "&lt;") Else [Res_results2.N2009_03] = [Res_results2.N2009_03] End If If IsNumeric(trim([Res_results2.N2009_03])) Then If cint(trim([Res_results2.N2009_03])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_03]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_03] & " (3/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.A2009_04], "<") Then [Res_results2.A2009_04] = Replace([Res_results2.A2009_04], "<", "&lt;") Else [Res_results2.A2009_04] = [Res_results2.A2009_04] End If If IsNumeric(trim([Res_results2.A2009_04])) Then If cint(trim([Res_results2.A2009_04])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.A2009_04]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.A2009_04] & " (4/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2009_06], "<") Then [Res_results2.N2009_06] = Replace([Res_results2.N2009_06], "<", "&lt;") Else [Res_results2.N2009_06] = [Res_results2.N2009_06] End If If IsNumeric(trim([Res_results2.N2009_06])) Then If cint(trim([Res_results2.N2009_06])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_06]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_06] & " (6/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2009_07], "<") Then [Res_results2.N2009_07] = Replace([Res_results2.N2009_07], "<", "&lt;") Else [Res_results2.N2009_07] = [Res_results2.N2009_07] End If If IsNumeric(trim([Res_results2.N2009_07])) Then If cint(trim([Res_results2.N2009_07])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_07]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_07] & " (7/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2009_08], "<") Then [Res_results2.N2009_08] = Replace([Res_results2.N2009_08], "<", "&lt;") Else [Res_results2.N2009_08] = [Res_results2.N2009_08] End If If IsNumeric(trim([Res_results2.N2009_08])) Then If cint(trim([Res_results2.N2009_08])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_08]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_08] & " (8/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.A2009_09], "<") Then [Res_results2.A2009_09] = Replace([Res_results2.A2009_09], "<", "&lt;") Else [Res_results2.A2009_09] = [Res_results2.A2009_09] End If If IsNumeric(trim([Res_results2.A2009_09])) Then If cint(trim([Res_results2.A2009_09])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.A2009_09]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.A2009_09] & " (9/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2009_11], "<") Then [Res_results2.N2009_11] = Replace([Res_results2.N2009_11], "<", "&lt;") Else [Res_results2.N2009_11] = [Res_results2.N2009_11] End If If IsNumeric(trim([Res_results2.N2009_11])) Then If cint(trim([Res_results2.N2009_11])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_11]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_11] & " (11/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2009_12], "<") Then [Res_results2.N2009_12] = Replace([Res_results2.N2009_12], "<", "&lt;") Else [Res_results2.N2009_12] = [Res_results2.N2009_12] End If If IsNumeric(trim([Res_results2.N2009_12])) Then If cint(trim([Res_results2.N2009_12])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2009_12]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_12] & " (12/09)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2010_03], "<") Then [Res_results2.N2010_03] = Replace([Res_results2.N2010_03], "<", "&lt;") Else [Res_results2.N2010_03] = [Res_results2.N2010_03] End If If IsNumeric(trim([Res_results2.N2010_03])) Then If cint(trim([Res_results2.N2010_03])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2010_03]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2010_03] & " (3/10)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2010_06], "<") Then [Res_results2.N2010_06] = Replace([Res_results2.N2010_06], "<", "&lt;") Else [Res_results2.N2010_06] = [Res_results2.N2010_06] End If If IsNumeric(trim([Res_results2.N2010_06])) Then If cint(trim([Res_results2.N2010_06])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2010_06]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2010_06] & " (6/10)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2010_07], "<") Then [Res_results2.N2010_07] = Replace([Res_results2.N2010_07], "<", "&lt;") Else [Res_results2.N2010_07] = [Res_results2.N2010_07] End If If IsNumeric(trim([Res_results2.N2010_07])) Then If cint(trim([Res_results2.N2010_07])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2010_07]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2010_07] & " (7/10)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2010_09], "<") Then [Res_results2.N2010_09] = Replace([Res_results2.N2010_09], "<", "&lt;") Else [Res_results2.N2010_09] = [Res_results2.N2010_09] End If If IsNumeric(trim([Res_results2.N2010_09])) Then If cint(trim([Res_results2.N2010_09])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2010_09]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2010_09] & " (9/10)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2010_10], "<") Then [Res_results2.N2010_10] = Replace([Res_results2.N2010_10], "<", "&lt;") Else [Res_results2.N2010_10] = [Res_results2.N2010_10] End If If IsNumeric(trim([Res_results2.N2010_10])) Then If cint(trim([Res_results2.N2010_10])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2010_10]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2010_10] & " (10/10)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2010_11], "<") Then [Res_results2.N2010_11] = Replace([Res_results2.N2010_11], "<", "&lt;") Else [Res_results2.N2010_11] = [Res_results2.N2010_11] End If If IsNumeric(trim([Res_results2.N2010_11])) Then If cint(trim([Res_results2.N2010_11])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2010_11]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2010_11] & " (11/10)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2010_12], "<") Then [Res_results2.N2010_12] = Replace([Res_results2.N2010_12], "<", "&lt;") Else [Res_results2.N2010_12] = [Res_results2.N2010_12] End If If IsNumeric(trim([Res_results2.N2010_12])) Then If cint(trim([Res_results2.N2010_12])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2010_12]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2010_12] & " (12/10)" & unbold End if bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_01], "<") Then [Res_results2.N2011_01] = Replace([Res_results2.N2011_01], "<", "&lt;") Else [Res_results2.N2011_01] = [Res_results2.N2011_01] End If If IsNumeric(trim([Res_results2.N2011_01])) Then If cint(trim([Res_results2.N2011_01])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_01]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2011_01] & " (1/11)" & unbold bold="<_BOL>" unbold = "</_BOL>" End if If InStr([Res_results2.N2011_02], "<") Then [Res_results2.N2011_02] = Replace([Res_results2.N2011_02], "<", "&lt;") Else [Res_results2.N2011_02] = [Res_results2.N2011_02] End If If IsNumeric(trim([Res_results2.N2011_02])) Then If cint(trim([Res_results2.N2011_02])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End IF If trim([Res_results2.N2011_02]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_02] & " (2/11)"& unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_03], "<") Then [Res_results2.N2011_03] = Replace([Res_results2.N2011_03], "<", "&lt;") Else [Res_results2.N2011_03] = [Res_results2.N2011_03] End If If IsNumeric(trim([Res_results2.N2011_03])) Then If cint(trim([Res_results2.N2011_03])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_03]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_03] & " (3/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_04], "<") Then [Res_results2.N2011_04] = Replace([Res_results2.N2011_04], "<", "&lt;") Else [Res_results2.N2011_04] = [Res_results2.N2011_04] End If If IsNumeric(trim([Res_results2.N2011_04])) Then If cint(trim([Res_results2.N2011_04])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_04]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_04] & " (4/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_05], "<") Then [Res_results2.N2011_05] = Replace([Res_results2.N2011_05], "<", "&lt;") Else [Res_results2.N2011_05] = [Res_results2.N2011_05] End If If IsNumeric(trim([Res_results2.N2011_05])) Then If cint(trim([Res_results2.N2011_05])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_05]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_05] & " (5/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_06], "<") Then [Res_results2.N2011_06] = Replace([Res_results2.N2011_06], "<", "&lt;") Else [Res_results2.N2011_06] = [Res_results2.N2011_06] End If If IsNumeric(trim([Res_results2.N2011_06])) Then If cint(trim([Res_results2.N2011_06])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_06]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_06] & " (6/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_07], "<") Then [Res_results2.N2011_07] = Replace([Res_results2.N2011_07], "<", "&lt;") Else [Res_results2.N2011_07] = [Res_results2.N2011_07] End If If IsNumeric(trim([Res_results2.N2011_07])) Then If cint(trim([Res_results2.N2011_07])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_07]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_07] & " (7/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_08], "<") Then [Res_results2.N2011_08] = Replace([Res_results2.N2011_08], "<", "&lt;") Else [Res_results2.N2011_08] = [Res_results2.N2011_08] End If If IsNumeric(trim([Res_results2.N2011_08])) Then If cint(trim([Res_results2.N2011_08])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_08]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_08] & " (8/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_09], "<") Then [Res_results2.N2011_09] = Replace([Res_results2.N2011_09], "<", "&lt;") Else [Res_results2.N2011_09] = [Res_results2.N2011_09] End If If IsNumeric(trim([Res_results2.N2011_09])) Then If cint(trim([Res_results2.N2011_09])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_09]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_09] & " (9/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_10], "<") Then [Res_results2.N2011_10] = Replace([Res_results2.N2011_10], "<", "&lt;") Else [Res_results2.N2011_10] = [Res_results2.N2011_10] End If If IsNumeric(trim([Res_results2.N2011_10])) Then If cint(trim([Res_results2.N2011_10])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_10]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_10] & " (10/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_11], "<") Then [Res_results2.N2011_11] = Replace([Res_results2.N2011_11], "<", "&lt;") Else [Res_results2.N2011_11] = [Res_results2.N2011_11] End If If IsNumeric(trim([Res_results2.N2011_11])) Then If cint(trim([Res_results2.N2011_11])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_11]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_11] & " (11/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2011_12], "<") Then [Res_results2.N2011_12] = Replace([Res_results2.N2011_12], "<", "&lt;") Else [Res_results2.N2011_12] = [Res_results2.N2011_12] End If If IsNumeric(trim([Res_results2.N2011_12])) Then If cint(trim([Res_results2.N2011_12])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2011_12]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2011_12] & " (12/11)" & unbold End If bold="<_BOL>" unbold = "</_BOL>" If InStr([Res_results2.N2012_02], "<") Then [Res_results2.N2012_02] = Replace([Res_results2.N2012_02], "<", "&lt;") Else [Res_results2.N2012_02] = [Res_results2.N2012_02] End If If IsNumeric(trim([Res_results2.N2012_02])) Then If cint(trim([Res_results2.N2012_02])) > w Then bold = "<BOL>" unbold = "</BOL>" End if End if If trim([Res_results2.N2012_02]) <> "" Then TheLabel = TheLabel&vbnewline & " " & bold & [Res_results2.N2012_02] & " (2/12)" & unbold End If TheLabel = Replace([Perchlorate_Monitoring_Locations.WC_Location], "&", "&amp;") & TheLabel FindLabel = TheLabel End Function

  8. Create One to Many Relationship One Location to many results.

  9. Building a VBScript Expression

  10. The Script Sample_Monitoring_Locationsshapefile joined with Res_Results2 (lab results) Table. Function FindLabel ( [Sample_Monitoring_Locations.LOCATION], [Sample_Monitoring_Locations.WC_Location], [Sample_Monitoring_Locations.PARID], [Res_results2.N2012_02] , [Res_results2.N2011_12] , [Res_results2.N2011_11] , [Res_results2.N2011_10] , [Res_results2.N2011_09] , [Res_results2.N2011_08] … Define and populate your variables with data from the table. Sample Location Shapefile Parcel ID Column in Shapefile Column in Table (August 2009) Lab Results Table

  11. One routine per month If the string contains < “less than” swap it out with an equivalent character code. If InStr([Res_results.N2009_01], "<") Then [Res_results2.N2009_01] = Replace([Res_results2.N2009_01], "<", "&lt;") Else [Res_results2.N2009_01] = [Res_results2.N2009_01] End If If IsNumeric(trim([Res_results2.N2009_01])) Then If cint(trim([Res_results2.N2009_01])) > w Then bold = "<BOL>" End if End if If trim([Res_results2.N2009_01]) <> "" Then TheLabel = TheLabel & vbnewline & bold & [Res_results2.N2009_01] & " (1/09)" & unbold End if Parse number from String. If greater than MCL then Bold. If results are not null then include label. Trim removes blank spaces. This routine is repeated for every month of data Changing the date/Column name of course

  12. End the function and replace ‘&’ TheLabel= Replace([sample_Monitoring_Locations.WC_Location], "&", "&amp;") & TheLabel FindLabel= TheLabel End Function

  13. Displaying < > and & SymbolsUsing Equivalent Character Codes ESRI Desktop Help mentions this work around: Function FindLabel ([LABELFIELD]) NewString = Replace([LABELFIELD],"&","&amp;") FindLabel = "<ITA>" & NewString & "</ITA>" End Function I used this work around: If InStr([Res_results.N2009_01], "<") Then [Res_results2.N2009_01] = Replace([Res_results2.N2009_01], "<", "&lt;") TheLabel = Replace([sample_Monitoring_Locations.WC_Location], "&", "&amp;") & TheLabel

  14. Balance text size to avoid overlap

  15. Resources • “Using VBSript to Build Complex Labels in ArcGIS” Arcuser Magazine, Oct-Dec 2004 (deals mostly with text formatting) • ESRI Help on building Label expressions • “Advanced Labeling in ArcMap with VBScript Findlabel Functions” by Chad Cooper • Note: Maplex was used mainly to stack label location name.

  16. Query in Access to

More Related