Avoid Conflicts between access and SQL Server null values

In the Access Project (that is, the. ADP file) linked to an SQL Server database, the access function that you frequently use is usually not supported. But most of the time, you can use a Transact-SQL (T-SQL) function supported by SQL Server. One exception is that access's null (null) functions NZ and isnull; they have similar functionality, but they are not supported by access and T-SQL. This article will discuss when and how to use these two functions to handle null values.

 

Importance of null values

NULL values are completely legal database input items. They may provide more information than you think. A null value indicates that a value is unknown, but this does not mean that the value is unacceptable or does not exist. A null value indicates that a value has not been determined, and the value remains unknown unless an appropriate value is determined, that is, a null value.
The developer's responsibility is to determine when a null value may appear and process those values accordingly. One solution is to reject null values during data input, thus completely avoiding them. However, in this way, your data will completely prohibit null values, so the disadvantage of this solution is that when you create a record, all data must be known. Unfortunately, this solution is unrealistic for most people. Taking the Database System of the hospital emergency room as an example, is it because the data recorder refuses to create a record for a patient without knowing the intermediate name of the patient? Therefore, proper handling of null values can provide a more flexible solution, because records can be successfully created even if the data recorder does not know all the data at the time.

After realizing that null values cannot be avoided, you are responsible for correctly handling them and ensuring that they do not cause errors. Correct Handling of null values has two advantages:
• Sharing meaningful information with users.
• You can capture null values in advance to avoid returning errors in subsequent expressions.

Null Value in access

Access provides a large number of tools to manipulate null values:
• Is null -- use is null in the condition expression and SQL WHERE clause.
• Isnull -- use isnull for Visual Basic for Applications (VBA ).
• Nz -- when a null value is encountered, NZ is used to return values other than null.

The above functions are essentially slightly different. Is null and isnull capture null values, while NZ functions process null values. In other words, if you want to return a null value that is not actually stored, use NZ. For example, you can display a simple string (for example, "N/A") to let the user know that the data is "unavailable" for the current record ". In addition, you can capture null values before using them in expressions, because null values often return errors.

When using access and SQL server through a linked table or access project, a problem occurs, because access and SQL Server do not use the same function to determine the null value. Therefore, you must know what function to use and when to use it.

Use nz in the. MDB File

Most access users and developers are familiar with the NZ function. When a null value is encountered, it returns a value other than the null value. The form of this function is as follows, where variant represents the variant data type, while valueifnull is an optional parameter, which provides the true return value when variant is null.

NZ (variant [, valueifnull])

If valueifnull is omitted, the NZ return value is 0 or a zero-length string (""), which is determined by the variant data type.

The query in Figure A shows how to use NZ and its results. This query is based on the employees table in the access Sample Database northwind.
Figure

Both NZ functions return the string data type, because the region data type is text

As shown in B, both NZ functions return string values instead of null values, but the specific strings are different. The first expression returns the string "N/A" to replace the null value:

Witexceptional: NZ ([region], "N/")

By returning an actual string or value, you can provide more information than a null value. The string "N/A" indicates that the current record does not have a proper value. On the contrary, if a null value is returned, a blank field may be misunderstood by unfamiliar users.

Figure B

NZ returns an actual value instead of a null value that is easy to misunderstand.
The second expression returns a zero-length string to replace null values:

Withoutoptional: NZ ([region])

Although there is little information provided for the user, it effectively prevents null values in subsequent expressions, thus avoiding database errors. The new expression shown in Figure C proves this.

Figure C

The expression may not handle null values as expected
Both expressions depend directly or indirectly on values in the region field. The first expression directly references the region field:

Regionstring: IIF ([region] = "", "N/A", [region])

You may think that a string should appear in each field of the result record set. Figure D shows the actual result. It does not match your expectation. Not every field contains a string. In fact, the expression returns the string "N/A" only when the field contains a zero-length string ". We know that these seemingly blank fields contain null values, rather than zero-length strings. However, errors can be understood and avoided.

Figure D

When a null value is encountered, use the NZ returned value instead of an error
The next expression also references the region value, but indirectly references it through the result of the NZ function:

Withoutoptionalstring: IIF ([withoutoptional] = "", "N/A", [withoutoptional])

In this way, any null value can be expressed with a more precise value and evaluated in any subsequent expressions. D. The expression is okay when processing the blank region field. Only null values that are "not expected" or "unknown" will cause problems.

NZ cannot work in the project

Some people think that the ACCESS project also supports NZ functions, but this is not the case. Remember, tables in the project are real SQL Server tables. To prove this, you only need to use the northwindcs. ADP Demonstration Project provided by access. It is based on a simple view of the Employees table and uses the NZ function as in the Microsoft database (. mdb) file, as shown in E. When you try to run the query, access returns an error.

Figure E

The access project does not support NZ functions.

Use NZ in Access Project

NZ cannot be used in the project, but should be replaced by the isnull function of transact SQL. This function is similar to NZ in the following form:

Isnull (expression, valueifnull)

Both parameters are required. The type of isnull result value is determined by expression. If the column data type of valueifnull conflicts with the expression data type, SQL Server Returns An error when you try to enter the expression. For example, if expression is based on a numeric column and valueifnull is a string, the two parameters will conflict. Because the SQL server cannot resolve the conflict, the expression is denied.

To solve the problem of the preceding example (Figure E), replace NZ with the isnull expression, as shown in F:

Isnull (Region, N/)

The result is the same as that in Figure B (in the witexceptional field ).

Figure F

Use isnull In the ACCESS project to replace NZ

Avoid null errors

Access and SQL server use different functions to process null values (NZ and isnull respectively ). Correct Processing of null values can prevent database running errors and generate invalid data. Do not avoid null values. You only need to process them correctly.