Data Management optimize

Avoid Null value conflicts between Access and SQL Server

Handling Null values is tricky, but avoiding Null errors between Access and SQL Server can be downright daunting. Find out which Null-handling functions are platform-specific and which Null functions can survive the Access to SQL transition.

Often, Access functions that you've come to depend on in regular usage aren't supported in an Access project (e.g., .adp file) that links to a SQL server database. Most of the time, though, there's an alternative Transact-SQL (T-SQL) function that's recognized by SQL Server. Such is the case with the Null value Access functions, Nz and IsNull; they're similar in task, but not supported in both Access and T-SQL. Let's discuss when and how you should use each of these functions to deal with Null values.

Why Nulls are valuable
Null values are perfectly acceptable database entries, and they provide more information than you might realize. Null values tell you that a value is unknown, which isn't the same as telling you the value isn't applicable or doesn't exist. A Null value simply means that the value has yet to be determined, and until you ascertain the appropriate value, the value remains unknown, or Null.

It's your job, as the developer or consultant, to recognize when Null values are possible and to handle those values appropriately. You can avoid them altogether by rejecting Null values during data entry. That way, they simply never make their way into your data. The down side of this approach is that all data must be known when you create a record. Unfortunately, that solution is impractical for most of us. Would you really want a database that refuses to create a record for an Emergency Room patient just because the data entry operator didn't know the patient's middle initial? Handling Null values is probably the more flexible solution because data entry operators can create records even though all the data isn't available at the time.

After recognizing that Null values are inevitable, your job is to handle them properly so they don't cause errors. Correctly handling Null values has two benefits:
  • You can share meaningful information with the user.
  • You can capture Null values before they return errors in subsequent expressions.

Null values in Access
Access provides a number of tools for working with Null values:
  • Is Null—Use Is Null in criteria expressions and SQL WHERE clauses.
  • IsNull—Use IsNull with Visual Basic for Applications (VBA).
  • Nz—Use Nz to return a value other than Null when a Null value is encountered.

The natures of the above functions are a little different. Is Null and IsNull capture Null values; the Nz function handles Null values. In other words, use Nz when you want to return a value other than the Null value that's actually stored. For instance, you might want to display a simple string such as "N/A" to let the user know the data isn't applicable for the current record. Or, you might want to catch the Null value before using it in an expression because Null values generally return errors instead of valid data.

A problem arises when you're working with Access and SQL Server either through linked tables or an Access project, because Access and SQL Server don't use the same functions to determine a Null value. Consequently, you need to know which function to use and when.

Using Nz in a .mdb file
Most Access users and developers are familiar with the Nz function, which returns a value other than Null when it encounters a Null value. This function uses the form shown below, where variant represents a Variant data type and valueifnull is an optional argument that supplies the return value when variant is Null.
 
Nz(variant[, valueifnull])

When valueifnull is omitted, Nz returns either the value 0 or a zero-length string (""), depending upon variant's data type.

The query shown in Figure A provides a quick example of using Nz and its results. This query is based on the Employees table in Northwind, the sample database that comes with Access.

Figure A
Both Nz functions return a string data type because Region's data type is Text.


As shown in Figure B, both Nz functions return string values in place of the Null value, but the strings are different. The first expression returns the string "N/A" instead of a Null value:
 
WithOptional: Nz([Region],"N/A")

Returning an actual string or value provides the user with more information than the Null value. The string "N/A" tells a story—the value isn't applicable for the current record. A blank field could mean most anything to the unfamiliar user.

Figure B
Nz returns a value instead of Null.


The second expression returns a zero-length string for each Null value:
 
WithoutOptional: Nz([Region])

This solution is less informative to the user, but it protects the database from potential errors if the value is subsequently used in expressions. The new expressions shown in Figure C illustrate this point.

Figure C
An expression may not handle a Null value as expected.


Both expressions depend on the values in the Region field, either directly or indirectly. The first expression refers to the Region field directly:
 
RegionString: IIf([Region]="","N/A",[Region])

Reviewing just the expression, you might expect to see a string in every field of the resulting recordset. Figure D shows the unexpected results—not every field contains a string. What the expression really does is return the string "N/A" if a field contains a zero-length string (""). We know that these seemingly blank fields contain Null and not zero-length strings, but the mistake is understandable. It's also avoidable.

Figure D
Use Nz to return values instead of errors when encountering Null values.


The next expression also refers to the Region values, but indirectly through the results of an Nz function:
 
WithoutOptionalString: IIf([WithoutOptional]="","N/A",[WithoutOptional])

This way, any Null values can be represented by a more appropriate value that can then be evaluated in any subsequent expression. As you can see in Figure D, the expression has no problem handling the empty Region fields. It's the unexpected or unknown Null value that causes problems.

Nz doesn't work in a project
You might expect an Access project to also support the Nz function, but it doesn't. Remember, project tables are really SQL Server tables. You can quickly test this in NorthwindCS.adp, a sample project that comes with Access. Base a simple view on the Employees table and try to use the Nz function as you did in the earlier Microsoft database (.mdb) file, as shown in Figure E. When you attempt to run this query, Access returns an error.

Figure E
Access projects don't support the Nz function.


Using Nz in an Access Project
You can't use Nz in a project; instead use Transact SQL's IsNull function. This function is similar to Nz in task and uses the following form:
 
IsNull(expression, valueifnull)

Both arguments are required and IsNull's resulting value takes its data type from expression. If valueifnulI's column data type is in conflict with expression's data type, SQL Server returns an error when you try to enter the expression. For instance, the two arguments are in conflict if expression is based on a numeric column and valueifnull is a string. There's no way SQL Server can resolve this conflict, so it rejects the expression.

You can quickly fix the previous example (Figure E) by replacing Nz with the IsNull expression as shown in Figure F:
 
IsNull(Region, 'N/A')

The results are the same as those shown earlier in Figure B (in the WithOptional field).

Figure F
Use IsNull instead of Nz in an Access project.


Avoid Null errors
Access and SQL Server use different functions to handle Null values: Nz and IsNull, respectively. As I've shown, handling Null values protects your database from runtime errors and invalid data. Don't avoid Null values; just make sure you handle them appropriately.

 

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

0 comments