Exclude null value features when symbolizing based on a joined field in ArcMap

Summary

Note:
This article pertains to ArcGIS versions 8.x only. Later versions of ArcGIS may contain different functionality, as well as different names and locations for menus, commands and geoprocessing tools.

Instructions provided are for creating a symbology exclusion query that catches null values for joined data created with the default option of 'Keep all records'.

Note:
Exclusion is only supported for the following layer symbology options: Graduated colors, Graduated symbols, Proportional symbols, Pie charts, Bar/Column charts, Stacked bar charts, Quantity by category.

Procedure

Perform the following steps:

1. Symbolize the joined data.
2. Right-click the layer and click Properties.
3. Click the Symbology tab.
4. Click Exclude or Exclusion to open the Dialog Exclusion Properties dialog box.

Note:
If the data symbolizes Graduated colors or Graduated symbols, click Classify to open the Classification dialog box and then click Exclusion.

5. Click the Query tab on the Data Exclusion Properties dialog box.
6. Create a query that uses 'Is Null' to return the null value features.

Example:

If TableB is joined to FeatureClassA, this would be a valid query:

(FeatureClassA.OBJECTID is Not Null) and (TableB.fieldB is Null) 

Note:
The query must contain at least one term that involves a field in the base table. This is necessary to access fields from the joined table. A term that returns all records can be used. For example, FeatureClassA.OBJECTID can be used and does not affect the results since it returns all records.

Depending on the type of data, the above syntax may need to be modified by surrounding each field name with either double quotes or square brackets. For example:

Personal Geodatabase/Access:

([FeatureClassA.OBJECTID] is Not Null) and ([TableB.fieldB] is Null)

Shapefile/dBase:

("FeatureClassA.OBJECTID" is Not Null) and ("TableB.fieldB" is Null)