accessでテキストボックスで検索時エラー出る

質問

ACCESS2010で顧客名簿を作りました。

顧客データテーブルと、届け先のデータテーブルがあります。
これは顧客（依頼主）から商品を発送する送り先（届先）のデータです。

各顧客データに対し、届先データは0から複数まであります。
各顧客データをメインフォームに反映し、その中にサブフォームを作り届け先データを入力してます。

この顧客データから氏名（依頼主氏名）を検索するために、「データで検索」というページをつくり、そこに検索用のテキストボックス「依頼主氏名検索」を用意しました。ここに顧客名を入れて検索しています。

クエリのデザインビューのフィールドに
IIf(IsNull([Forms]![データで検索]![依頼主氏名検索]),True,[依頼主名前] Like "*" & [Forms]![データで検索]![依頼主氏名検索] & "*")
という式を入れて使用しています。抽出条件に「true」と入れています。

しかし検索結果に、白紙のデータシートビューが出ることがあり困っています。
顧客データ（依頼主データ）は埋まっているのに、
サブフォームのデータ（届先データ）に全くデータが入っていない場合、こうなります。
サブフォームのカラムが一つでも埋まっていれば、希望する検索結果となって顧客データが表れます。

届け先データが空の顧客データも多いのですが、
届け先データを持っている顧客データと同様に表示されるようにしたいです。
そのためには、どこを修正すればよろしいでしょうか。

フィールドに入れた式はネットで検索した様々な参考ページから、拝借したものを入れたもので、自分自身は、なんとなくしかわかりません。一から式を書くことはできません。

式の中に届け先データに触れるような部分が見当たらないので、他の部分に問題があるのではないのだろうか、と考えていますが、見当がつきません。

どうぞお助けくださいますようお願いいたします。

回答No.5

chayamati

１．テーブル確認
　顧客テーブル、届け先テーブルには主キーが定義されていますね
　届先テーブルにリレーションシップ用の項目（顧客ID)を数値型で追加しして
　顧客テーブルの主キーと顧客ID間にリレーションシップを設定します。
　※リレーションのパターンがリストされるので選択します。
　　(1)両方のテーブルの結合フィールドが同じ
　　(2)T_顧客の全レコードとT_届先の同じ結合
　　(3)T_届先の全レコードとT_顧客の同じ結合
　今回は届け先のないレコードも表示するため(2)を選択

２．クエリー
　添付のように依頼主名前の抽出条件欄に
　Like "*" & [Forms]![データで検索]![依頼主氏名検索] & "*"
　と入力します。

　このSQL文は
　SELECT T_顧客.依頼主名前, T_顧客.顧客住所, T_届け先.届け先名, T_届け先.届け先住所
　FROM T_顧客 LEFT JOIN T_届け先 ON T_顧客.[ID] = T_届け先.[顧客ID]
　WHERE (((T_顧客.依頼主名前) Like "*" & [Forms]![データで検索]![依頼主氏名検索] & "*"));

補足

chayamatiさんには、前回のご回答いただいた時も大変助かりました。
初心者のおぼつかない質問に度々ご回答いただき誠にありがとうございます。

回答No.4

DexMachina

No.3です。
まずはじめに、前回の回答でフィールド名を「依頼主氏名」と誤記していましたので、
お詫びの上、訂正させて戴きます(汗)

> 単純に矢印を入れ替える形で結合の種類の選択を変えましたが

おおぅ・・・失礼、その展開は予想しませんでした(汗)

例えば、クエリではなく、元のテーブル単体で、「依頼主名」「依頼主名前」の
フィールドで検索を掛けた場合を想像してみてください。
それぞれのテーブルは一対多の参照整合性が設定されているとのことでしたので、
依頼主テーブルの「依頼主名」では該当レコードが表示されますが、
届け先テーブルの「依頼主名前」では、(届け先が未登録の依頼主では)レコード
が見つからない、という結果になるはずです。

クエリの式中で抽出条件を記述した場合も、これと同じことになります。
(テーブル単独の状態で表示するレコードがないなら、他のテーブルと組み合わせても
　表示するレコードはないまま、と：
　但し、「一方のテーブルにないレコードを抽出」する「不一致クエリ」の場合は除く)

ですので、前回の回答の意図をより正確にすると
　　一対多の「一」側のテーブルを「全レコード」側とし、
　　抽出条件に使用には、その「一」側のテーブルのフィールドを使用して下さい
ということになります。

> ＜届け先テーブル＞の”依頼主名前”は”依頼主名”と微妙に違っていました。

・・・あれ?
「多」側の方が、違うフィールド名、ですか?
だとすると、現在の状態で、『「一」側のテーブルのフィールド』が使用されているという
ことになりますね(汗)

・・・とはいえ、他に原因が推測できないので(汗)、念のため、もう一度フィールド名を
確認してみてもらえますか?
(「サブフォームのカラムが一つでも埋まっていれば、希望する検索結果となって顧客
　データが表れます」とのことから、フォーム側の名前の間違いなどはないと思います)

とりあえず、「一」側である依頼主テーブルのフィールド名が「依頼主名」だった場合の
式を、以下に提示します：

【現在】
　IIf(IsNull([Forms]![データで検索]![依頼主氏名検索]),True,[依頼主名前] Like "*" & [Forms]![データで検索]![依頼主氏名検索] & "*")

【修正】
　IIf(IsNull([Forms]![データで検索]![依頼主氏名検索]),True,[依頼主名] Like "*" & [Forms]![データで検索]![依頼主氏名検索] & "*")

・・・また、本当に念のためになってしまいますが(汗)、
　・上記以外で届け先テーブルのフィールドに対して抽出条件を指定していないか
についても、確認してみてください。

補足

DexMachinaさんの、親身なるご回答、毎回恐れ入ります。本当にありがたいです。

回答No.3

DexMachina

No.1です。

> ＜依頼主テーブル＞　　　　　　　＜届け先テーブル＞
> 　（鍵） 依頼主ID　　1　　→　　∞　　依頼主ID
> 　　　依頼主名前　　　　　　　　　　 依頼主名前
> 　　　（以下続く）　　　　　　　　　　　　（以下続く）

・・・このフィールド構成だとすると、前回提示された式は
「指定されたフィールド '[依頼主名前]' がSQL ステート
メントの FROM 句にある複数のテーブルを参照しました。」
というエラーになるはずなのですが・・・(汗)
(元となるテーブル群に同名のフィールドが存在する場合は、
　「[依頼主氏名]」ではなく「[依頼主]![依頼主氏名]」等と
　テーブル名を明示しないと、どのテーブルの「依頼主氏名」
　フィールドなのか、Accessが判断できないためです)

まずは、フィールド名に間違いがないか、確認してみて下さい。

その上で、対処方法としては、既にhatena1989さんからも
回答がありますが、抽出条件に使用するフィールドとしては
「全レコード」側のテーブルのフィールドを使用してください。
(結合線が「→」となっているクエリでは、矢印の先側のテーブル
　にデータがない場合、フィールドの値は「Null(空白)」になる
　ため、提示された式では抽出できません)

> 届け先テーブルの依頼主IDに（鍵）マークがないことと、
> 矢印に一対多の印が出ています。

これについては、今回ご質問の「データが表示されない」という
現象の原因になることはないと思います。
(鍵マークはそのテーブルの主キーを表しています。主キー(又は
　インデックス)があると、完全一致や先頭一致の検索の速度が
　向上する利点があるため、設定が推奨はされますが、必須では
　ありません。
　また、「1→∞」は、「一対多」という言葉を使われていることから
　既にご存知とは思いますが、参照整合性を設定しているために
　表示されているもので、これは「1」側のテーブル(マスタ)側に
　未登録のまま、「∞」側のテーブル(個別案件)側に登録される
　のを避ける等、主に登録・更新時に影響するもので、検索結果
　が表示されなくなる原因になることはない・・・と思います(汗))

なお、hatena1989さんから「IIFの式にする必要はない」との
アドバイスがありますが、これについては対象フィールドの設定及び
データを確認して、採用するかどうか判断することをお勧めします。
(当該フィールドの『値要求』プロパティが「はい」となっている等して、
　Nullのデータが1件もないことが保証されているならいいのですが、
　もしもNullのデータがある場合、提示された式にしておかないと、
　Nullのデータが表示されなくなってしまいますので・・・(汗))

補足

度重なる詳細なご回答をいただきまして、まことに恐縮です。

フィールド名の確認をしました。
＜届け先テーブル＞の”依頼主名前”は”依頼主名”と微妙に違っていました。

＞抽出条件に使用するフィールドとしては
＞「全レコード」側のテーブルのフィールドを使用してください。

単純に矢印を入れ替える形で結合の種類の選択を変えましたが、やはりだめでした。

＞(結合線が「→」となっているクエリでは、矢印の先側のテーブル
＞　にデータがない場合、フィールドの値は「Null(空白)」になる
＞　ため、提示された式では抽出できません)

ということは、式を変更するべきでしょうか。
hatenaさんの式でも結果は変わらなかったのですが・・・。

回答No.2

hatena1989

ANo.1 の回答のように結合を変更して、
クエリを下記のように変更してください。

フィールド　依頼主名前
テーブル　　依頼主テーブル
抽出条件　　Like "*" & [Forms]![データで検索]![依頼主氏名検索] & "*"

おそらく現状は 依頼主名前 を届け先テーブルのものを表示させているのではと思われます。それを依頼主テーブルに変更します。

なお、今回のようにLikeであいまい検索するときは、元の質問のようにわざわざIIfの式にする必要はないですね。式にしない方が処理も高速になります。

さらに蛇足になりますが、データベースの「正規化」の原則から言えば、届け先データに 依頼主名前 があるのは、テーブル設計としては間違っています。

補足

ご回答ありがとうございました。

依頼主氏名は依頼主テーブルから表示させている状態でした。
IIFの式については追って勉強いたします。

回答No.1

DexMachina

土曜の深夜にお疲れ様です。

> 他の部分に問題があるのではないのだろうか、と考えていますが、
> 見当がつきません。

2つのテーブルから作られたクエリで、「一方のテーブルにデータが
あれば必ずデータを表示したい」ということでしたら、クエリのデザイン
ビューで、フィールド間の結合線を変更することになります。

1)当該クエリをデザインビューで開く
2)2つのテーブルの間にある結合線をダブルクリックするなどして、
　『結合プロパティ』ダイアログを開く
3)初期状態では「◎1:　両方のテーブルの結合フィールドが同じ
　行だけを含める」が選択されているはずなので、「2」か「3」の
　どちらかにある「'顧客データ' の全レコードと '届け先データ' の
　同じ結合フィールドのレコードだけを含める。」を選択
4)『OK』ボタンをクリックして、上記ダイアログを閉じ、結合線が
　添付画像のようになっていることを確認
5)(可能であればクエリを実行してみて、希望した動作をすることが
　確認できたら、)保存して閉じる

・・・以上です。

なお、3つ以上のテーブルからクエリを作る場合、
　テーブル1－テーブル2→テーブル3　：　Ok
　テーブル1→テーブル2→テーブル3　：　Ok
　テーブル1←テーブル2→テーブル3　：　Ok
　テーブル1→テーブル2－テーブル3　：　NG(ａ)
　テーブル1→テーブル2←テーブル3　：　NG(b)
というように、
　a)矢印の先(=「>」がつく)側のテーブルに「－」の結合がある形
　b)矢印の元側となるテーブルが複数となる形
だとエラーとなりますのでご注意下さい。
また、2つのテーブルを複数のフィールドで結合している場合は、
全ての結合線を同じ種類にしないと、これもエラーになりますので、
併せて参考まで。

補足

真に詳細なご回答いただき、心よりお礼申し上げます。

すぐに試してみました。

ダイアログを開き、[結合の種類」を開き、
「”依頼主テーブル”の全レコードと”届け先テーブル”の同じ結合フィールドのレコードだけを含める」
にチェックを入れました。
するとテーブル間の線が矢印になっていました。
矢印の方向は依頼主テーブルから届け先テーブルに向っています。

これを確認して、クエリを閉じ、検索を試しましたが、残念ながら同じ結果なのです。
ということでこちらのリレーションの状態を書いてみました。
biglobeからなので、画像の投稿が出来ませんゆえ、文字画で失礼します。

＜依頼主テーブル＞　　　　　　　＜届け先テーブル＞
　（鍵） 依頼主ID　　1　　→　　∞　　依頼主ID
　　　依頼主名前　　　　　　　　　　 依頼主名前
　　　（以下続く）　　　　　　　　　　　　（以下続く）

届け先テーブルの依頼主IDに（鍵）マークがないことと、矢印に一対多の印が出ています。
検索などしてみましたが、これが原因なのか理解できない状態です。

今一度、お力拝借できますよう、よろしくお願い申し上げます。