Access Null値の整理と判定方法、Null値によるエラーの抑止方法 IsNull VarType Nz

Nullから見る

Nullはデータ型がないかVariant型にのみ存在する

テキストでも数値でもない。
値が無効であるか、変数に有効な値、データが入っていないか、値が認識できないか値がないか。Access VBS VB6 Oracleなどいろいろなソフトにあるが、微妙に意味が異なり、MSのアプリケーション、スクリプトでも意味が異なる。

SQLではNull値は3値である

データがある データがない データがあるかないか不明(UnKnown)
このUnKnownがNull値である。

Accessではフィールドの定義として意識される

Accessではフィールドの値要求でNullが許容されるか否かが変わる。

CurrentDB.Execute(Create TABLE TABLENAME(FieldName NOT NULL);

でTABLENAMEというテーブルを作り、テーブルデザインビューでみると、値要求「はい」になっており、値が要求される。(FieldName.Required=True)
本当はテキスト型、メモ型、ハイパーリンク型には効かないはずだが、値要求「はい」で片付けられる。テキスト型、メモ型、ハイパーリンク型の場合、空文字列の許可はこの命令では変更されないことにも注意。この辺が混乱する要因。(なおSQLでは以前書いた通りHyperLink型のフィールドは定義できない)
空文字列の許可はFieldオブジェクトのAllowZeroLengthプロパティになる

Sub FldProperty()
'For Access
Dim dbs As DAO.Database: Set dbs = CurrentDb
Dim tdf As DAO.TableDef
Dim fld As DAO.Field, flds As DAO.Fields
Dim prp As Property
Set tdf = dbs.TableDefs("ThisTable"): Set flds = tdf.Fields
Set fld = tdf.Fields(0)
Set prp = fld.Properties("AllowZeroLength")
prp.Value = False
'fld.Required = False
End Sub

Accessはテキスト型、メモ型でもフィールドではNullが入るときがある

上記の値要求いいえ、空文字列いいえの時はNull値が入ることができる。

Accessの値要求と空文字列のプロパティの関係は何がベストか

テキスト型、メモ型では値要求と空文字列はどう設定すればよいか。ベストな設定を考える。
値要求いいえ、空文字列いいえの時はNull値が入ることができる
値要求いいえ、空文字列はいの時はNullか空文字列が入る
値要求はいで、空文字列いいえのときはどちらも入らない。
値要求はいで空文字列はいの時は長さ0の文字列になる

Null値が入らない設定は値要求はい、空文字列いいえが厳密でベスト

値要求はいで、空文字列いいえのときはどちらも入らない。つまり厳密なテーブルはこの設定が空文字列とNullが混在しないためよいということになる。

Null値が入らない設定で一般的に柔軟なのは値要求はい、空文字列はい柔軟でセカンドベスト

値要求はいで空文字列はいの時は長さ0の文字列になる。Nullが排除できるのでこれがセカンドチョイスになる。というか通常はこれがよい、という結論になる。

SQLによるNull 制約

SQLではフィールドにNot Null制約をするのはそのフィールドについて1度しかできない。
したがってSQLのAlter ColumnでNot Nullにすることはできるが、この逆はできない。また1度設定するとSQLでは変更できないため、テーブルデザインで手動で変更するかVBAで変更する。上記のRequiredがそのプロパティ。ただし値が入ったテーブルになるとエラーが起きたりするので値を消去するかそのフィールド自体を作り直さなければならない。

SQLでフィールドにNull値を入れることはできない

しれっと解説に書いてある。もともとNull値が仕方なく存在していることの証左と言える。

Null値を許容しない方が早くなる

値が必ず入り、Nullというエラーがないので早くなる。ただし理論上はそうであって、体感的に早くなるかは不明。

以上から最初はNot Nullは付加しない

必要ならあとからできるので、プロのテーブル設計は別として、素人が作るときには付加しない方がよい。

IndexはNot Nullではない

DisAllow Null を使う。詳しくは参考リンクを参照。しかし実際はこの設定はなくてもAutoNumberで数字が入るのでNullにならない。

Nullに似ているがNullではないもの

Empty

バリアント型に格納されている初期値。空のデータを意味する。

Nothing

オブジェクト型に格納される特殊な値でオブジェクトに何も関連付けられていない、空の状態を表す。変数が宣言された後の初期値。または変数をメモリから解放したときの状態。

If  変数 Is Nothing Then
Set 変数 = Nothing

""ダブルクォーテーション(二重引用符)の連続

長さ0の文字列。これはNull値ではない

VbNullString

値0の文字列 ""=VbnullString はTrueだが、別のもの
文字列型変数の初期値を表す
この違いはInputBoxで理解が必要
ただしAccess、Excelでは理解するだけで明示的に使われるケースは少ない

Sub test()
'For Access
Dim buf As String
Debug.Print StrPtr(buf)
buf = ""
Debug.Print StrPtr(buf)
End Sub

Null値はIsNull関数かIs (Not) Nullで判定するほかVarType関数を使い、Null値の変換にはNz関数を使う

IsNull関数

Null値が一つでもあるとすべてが無効になる

IsNull(A+B+C)でBがNullならA+B+CはNullと判定される。

このため平均などを出すときにNull値を排除しなければならない

計算実行時に NULL 値を削除することが必要な場合があります。平均値などの計算では、NULL 列が含まれていると正確な結果を得ることができません。

返り値

IsNull はTrue = -1が返る。(False = 0)
テーブルをデータシートビューで開いている状態でデータを入力して消すとNull値になる。
Accessのクエリの設定(デザインビュー)では抽出条件に Is Nullとする
下記のようにNullはFalseかTrueかがInNull関数以外では正確に評価できない。

Nz関数

バリアント型の変数が Null の場合に、0、長さ 0 の文字列 ("")、または指定された別の値を返す。たとえば、この関数を使って Null 値を別の値に変換し、式を通じて Null 値が広まらないようにすることができる。
varResult = Nz(varFreight, "No Freight Charge")
こうした指定も可能。

Nz 関数は、IIf 関数の代わりに使えることがよくあり、IIFを減らすことができる。
引数 valueifnull を使用せずに、クエリの式で Nz 関数を使用すると、NULL 値を含むフィールドは長さが 0 の文字列になる。
変数に一つでもNullがあると全体がNullになる。しかし、これを用いるとNullになったときに0にしてNull値が返ることを防ぐことができる。
IsNull(A+B+C)でBがNullならA+B+CはNullとTrue判定されるが
IsNull(Nz(A+B+C))でBがNullでも0になるため、False判定になる。Nullによるエラーを防ぐことができる

Nz関数の構文

Nz ( variant [, valueifnull ] )
Valueifnull variant 引数が NULL の場合に返す値。型は Variant です。この引数を使用すると、0 または長さが 0 の文字列以外の値を返すことができます。

VarType関数

変数の内部処理形式を表す

VarType(変数を表す文字列)
VbNull 1
しかしこの関数はAccessでは使用頻度が少ない。使うのはNzとIsnull。詳細は参考リンク等を参照のこと。

Null値は検索条件にしてはならない。

意味合いとしてはエラーに近いことから、これに索引としての要素を持たせない。

主キーでNull値は使えない

検索条件にしてはならないとともに、主キーでも使えない。なのでDisAllow Nullを使うことは素人レベルではない。

Accessとそれ以外のDB、VBSでは若干意味合いが違う

Null値はエラーに近い。

データ型から見る

クエリの検索条件は参考リンクの下に表があるので参照のこと

各データ型で使えるものがちがうので、表がある。リンク先の方が見やすいかも。
以下、ポイントを示す

Null値があるデータ型はYes/No型以外

テキスト、数字、日付/時刻型、添付ファイルにNullが存在する
Yes/NoにはNoをNullとすればある。
よってIsNull関数、クエリのIsNulL成立する。しかし、3値としては存在しない。
存在しないためYes No型にIsNullは効かない。

文字型

長さ0はあり得る
Nullもあり得る

数字日付時刻型

Nullはあり得る

YesNo(Boolean True/False)

Nullは厳密にいうとあり得ない。ただしNullをNo(0,False)とみなすことはできる。

添付ファイル

Nullはあり得る

公式の解説と、それに対するツッコミ（まじめあり）

Access の Null 値と、長さ 0 の文字列の違いについて
もちろん以下の内容の一部がよくまとまっている。
しかし、このサイトはまたいい加減な作りだ。

関連情報
881379 データを抽出するには
882979 クエリでフィールドに値がないもの (Null 値) を抽出するには
883246 クエリで任意の文字列が含まれるフィールドを抽出するには
294954 [ACC2002] パラメータ クエリで LIKE 演算子を使用する方法
411174 [ACC2002] 空白値を含むテーブルを Excel 形式としてエクスポートした場合、空白セルとして認識されない

リンクを踏むと、元のサイトに戻る。そう、このリンクはすべて死んでいるのである。

データを抽出する方法
https://web.archive.org/web/20130927150906/http://support.microsoft.com/kb/880531/JA
検索フォームを作ってそこに値を入力してデータを抽出するというもの

クエリでフィールドに値がないもの (Null 値) を抽出する方法
https://web.archive.org/web/20130923184815/http://support.microsoft.com/kb/882979/ja

クエリで任意の文字列が含まれるフィールドを抽出する方法
https://web.archive.org/web/20150331132257/http://support.microsoft.com/en-us/kb/883246/ja
※マイクロソフトはバイデンと同じで過去のタイトルすら忘れたようだ。

Access 2002 のパラメータ クエリで LIKE 演算子を使用する方法
https://web.archive.org/web/20140902072950/http://support.microsoft.com/kb/294954/ja
大体はいまでもどこかに記述があるが中には、重要なものもあった。
[ACC2002] 空白値を含むテーブルを Excel 形式としてエクスポートした場合、空白セルとして認識されない
http://support.microsoft.com/kb/411174/ja
https://web.archive.org/web/20121215072205/http://support.microsoft.com/kb/411174/ja
これは今でもテキスト型では起こりうるようである。

参考リンク

Nz関数
Access SQL を使用してテーブルやインデックスを作成、削除します。
No53625.テーブルの値要求プロパティをVBAで設定 - Access Club
値要求と空文字の許可
ACCESS　クエリで文字列を抽出方法を教えて下さい
関数でエラー回避の時Isnullを使用する。
Constants.vbNull フィールド
.Net Framework ではVariant型のオブジェクトがない(Nothing)ことを表す
VarType関数 Excel OfficeTanaka
VarPtr, StrPtr, ObjPtr関数の代わりになるものは？
VB6のVarPtr, StrPtr, ObjPtr関数は変数の下位のメモリアドレスを取得するためのドキュメント化されていない関数です。これらの関数はVB.NETではサポートされていないとのことです。
NullとEmptyとNothingと空の文字列の違い -Moug
InputBox関数でキャンセルされたときの処理－StrPtr関数
InputBoxで長さ0の文字列とキャンセルを区別するため、隠し関数のStrPtrを使う。
[VB-TIPS] VB6でのvbNullString、Null、vbNullChar、vbNull、Empty、空文字列について
Null値と長さ0の違い について
IS NULL (Transact-SQL)
expression IS [ NOT ] NULL

NULL値のレコードを抽出する- moug.net
NULL値のデータを評価するときは、IS NULLまたはIS NOT NULLを使用します。
NULL値は「無効な値」を表すため、他の演算子を使用して演算を行ってもNULL値が返されます。そのため、NULL値であるレコードを抽出する際に、「WHERE フィールド名 = NULL」と記述してもNULL値のレコードは抽出されません。
IsNull 関数 Microsoft Access
式に無効なデータ (Null 値) が含まれているかどうかを調べ、結果をブール型 (Boolean) で返します。
※無効なデータとされている
構文
IsNull ( expression )
必須の expression引数 は、数値式または文字列式を含む Variant です。

解説
expression が Null の場合、IsNull は True を返します。それ以外の場合、IsNull は False を返します。expression が複数の 変数 から構成される場合、含まれる変数に Null があると、式全体について True が返されます。
Null 値は、Variant に有効なデータが含まれていないことを示します。Null は 空 と同じではありません。変数がまだ初期化されていないことを示します。また、これは、null 文字列とも呼ばれる長さ 0 の文字列 ("") と同じではありません。
重要: 式に Null 値が含まれているかどうかを判断するには、IsNull 関数を使用します。If Var = Null、If Var <> Null など、状況によっては True に評価されることがある式は、常に False になります。これは、Null を含む式は Null であり、そのため False になるためです。
※つまりIsNull関数以外は正しくNull値かどうか判定できない。

IsNull関数 - VBS Microsoft
解説

IsNull 関数は、引数 expression の値が無効な値 (Null 値) である場合は真 (True) を返します。それ以外は偽 (False) を返します。引数 expression が複数の変数で構成されている場合、変数が 1 つでも Null 値のときには、式全体が Null 値となり、真 (True) が返されます。
Null 値は、変数に有効なデータが入っていないことを示す値であり、変数が初期化されていないことを示す Empty 値や、Null 文字列と呼ばれる長さが 0 の文字列 ("") とは異なります。

津島博士のパフォーマンス講座 -Oracle
SQLはリソースを使用する。Null値に意味を持たせない(これで判定するということをしない)

NULL 値 - SQL Server - TechNet
NULL 値は、値が認識できないことを示します。NULL 値は、空値または値 0 とは異なります。2 つの NULL 値は等しいとは限りません。2 つの NULL 値、または NULL 値と他の値を比較する場合、どの NULL 値も認識できないので、認識できないことを示す値が返されます。
NULL 値は、通常、認識されないデータ、適用できないデータ、または後から追加されるデータを示します。たとえば、受注時には顧客のミドル名イニシャルはわかりません。
次に NULL 値についての注意事項を示します。

• クエリ内で NULL 値を調べるには、WHERE 句で IS NULL または IS NOT NULL を使用します。
• SQL Server Management Studio コード エディタでクエリ結果を表示するとき、結果セットでは NULL 値は NULL と表示されます。
• NULL 値を列に挿入するには、INSERT ステートメントまたは UPDATE ステートメントで NULL を明示的に記述するか、INSERT ステートメントの外に列を置くか、ALTER TABLE ステートメントを使用して新しい列を既存テーブルに追加します。
• テーブル内のある行と別の行を区別するのに必要な、主キーなどの情報に NULL 値を使用することはできません。

クエリの抽出条件の例

(テキスト型)

対象のレコードの内容	使用する抽出条件	クエリ結果
Null値を含む	Is Null	フィールドに値が含まれないレコードが返されます。
Null値を含まない	Is Not Null	フィールドに値が含まれるレコードが返されます。
長さ 0 の文字列を含む	"" (引用符のペア)	フィールドの値が (null ではなく) 空白に設定されているレコードが返されます。たとえば、別の部門に対する販売のレコードの "国または地域" フィールドには、空白の値が含まれる場合があります。
長さ 0 の文字列を含まない	Not ""(引用符のペア)	フィールドが空白の値ではないレコードが返されます。
Null 値または長さ 0 の文字列を含む	"" Or Is Null	フィールドに値がないか、またはフィールドが空白の値

数値型"、"通貨型"、"オートナンバー型" のフィールドの抽出条件

対象のレコードの内容	使用する抽出条件	クエリ結果
null 値が含まれる (または値が含まれない)	Is Null	"単価" フィールドに値が入力されていないレコードが返されます。
非 Null 値を含む	Is Not Null	"単価" フィールドに値が含まれているレコードが返されます。

数字には長さ0という概念はない

(日付型)

対象のレコードの内容	使用する抽出条件	クエリ結果
null (または空) 値をフィルターで抽出する	Is Null	トランザクションの日付がないレコードが返されます。
非 null 値をフィルターで抽出する	Is Not Null	トランザクションの日付がわかっているレコードが返されます。

日付には長さ0という概念はない

添付ファイル

添付ファイルが含まれないレコードを含めるには、[抽出条件] 行に「Is Null」と入力します。 添付ファイルが含まれるレコードを含めるには、「Is Not Null」と入力します。

"はい/いいえ型" フィールドの抽出条件

対象のレコードの内容	使用する抽出条件	クエリ結果
Yes、True、1、または -1		Yes 値が試される。1 または -1 は、入力後に [抽出条件] 行で "True" に変換される。
No、False、または 0		No 値が試される。0 は、入力後に [抽出条件] 行で "False" に変換される。
No 値 (null)		試されない。
1、-1、0 以外の数値		それがフィールド内の唯一の抽出条件値の場合は結果なし。
Yes、No、True、False 以外の文字列		データ型不一致のエラーのため、クエリの実行は失敗する。

NoがNull値になるとされるが、Yes No型にはNullはないものと考えた方がよい。
なぜならYes No型は2値(0か絶対値が1)。
つまりNo(False)かそうでないものがYes(True)、0かそうではないものがYes(True)なので3値であるUnKnownが存在しないため。