【Access2003】クエリで動作するSQLがVBAで動作せず困っています。

質問

下記のソースを実行したところ、「パラメータが少なすぎます。2を指定してください。」というエラーが表示されました。
エラーが発生する箇所は「db.Execute strsql」です。
しかし、このstrsqlに書かれているSQL文をクエリで実行した所、問題なく動作しました。
なんとなく、クエリでは書けるものの、VBAでは書きない所があるのかなと思ったのですが、どのように修正すればよいか分かりませんでした。

その為、上記の現象について、ご教示いただける方がいらっしゃいましたら、よろしくお願いします。

【VBA】
Private Sub btn_Click()
Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim str As String

Set ws = DBEngine.Workspaces(0)
Set db = CurrentDb

' トランザクション開始
ws.BeginTrans

strsql = "INSERT INTO T_W ( W_DAY )"
strsql = strsql & "SELECT T_M.M_DAY"
strsql = strsql & " FROM T_M"
strsql = strsql & " WHERE (((Left([T_M].[M_DAY],4) & Mid([T_M].[M_DAY],6,2) & Right([T_M].[M_DAY],2))"
strsql = strsql & " Between IIf(Nz([Forms]![F_1]![text_str])='',0,[Forms]![F_1]![text_end])"
strsql = strsql & " And IIf(Nz([Forms]![F_1]![text_str])='',99999999,[Forms]![F_1]![text_end])))"
strsql = strsql & " ORDER BY T_M.M_DAY"

db.Execute strsql

ws.CommitTrans
End Sub

【フォーム：F_1】
テキストボックス：text_str
テキストボックス：text_end
コマンドボタン：btn

【データベース：T_M】
フィールド名：M_DAY
　　　値　　：2001/01/01
　　　値　　：2001/01/02
　　　値　　：2001/01/03
(以下略)

【データベース：T_W】
フィールド名：W_DAY
　　　値　　：なし

よろしくお願いします。

回答No.1

DexMachina · ベストアンサー

原因は把握できていないのですが、再現は確認できました。
一応、その中で見つけた対処法をご紹介します。

その方法というのは、SQL文内のIIF関数で処理している部分を、先に文字列化
してしまう、というものです。
(なお、今回のエラーとは関係ありませんが、各句の区切りを、Spaceから改行
　(vbCrLf)に変更しています)

宣言領域に

　Dim strHead As String, strTail As String

を追加した上で、SQL文の設定部分を以下のように変更してみて下さい。

　'テキストボックスへの入力値の確認
　strHead = IIf(Nz([Forms]![F_1]![Text_str]) = "", 0, [Forms]![F_1]![Text_End])
　strTail = IIf(Nz([Forms]![F_1]![Text_str]) = "", 99999999, [Forms]![F_1]![Text_End])
　'同じフォーム上でのVBAの記述であれば、以下でも可
　'strHead = IIf(Nz(Text_str) = "", 0, Text_End)
　'strTail = IIf(Nz(Text_str) = "", 99999999, Text_End)

　'SQL文の設定
　strSQL = "INSERT INTO T_W ( W_DAY )" & vbCrLf
　strSQL = strSQL & "SELECT T_M.M_DAY" & vbCrLf
　strSQL = strSQL & "FROM T_M" & vbCrLf
　strSQL = strSQL & "WHERE (((Left([T_M].[M_DAY],4) & Mid([T_M].[M_DAY],6,2) & Right([T_M].[M_DAY],2))" & vbCrLf
　strSQL = strSQL & "Between " & strHead & " And " & strTail & "))" & vbCrLf
　strSQL = strSQL & "ORDER BY T_M.M_DAY;"

・・・以上です。

お礼

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

ご提示いただいたソースで意図した動作が行われる事を確認しました。

Private Sub btn_Click()
Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim str As String
Dim strHead As String
Dim strTail As String

Set ws = DBEngine.Workspaces(0)
Set db = CurrentDb

ws.BeginTrans

strHead = IIf(Nz(Me.text_str) = "", 0, Me.text_str)
strTail = IIf(Nz(Me.text_end) = "", 99999999, Me.text_end)

strsql = "INSERT INTO T_W ( W_DAY )"
strsql = strsql & "SELECT T_M.M_DAY"
strsql = strsql & " FROM T_M"
strsql = strsql & " WHERE (Left([T_M].[M_DAY],4) & Mid([T_M].[M_DAY],6,2) & Right([T_M].[M_DAY],2))"
strsql = strsql & " Between " & strHead & " AND " & strTail
strsql = strsql & " ORDER BY T_M.M_DAY"

db.Execute strsql

ws.CommitTrans
End Sub

今後はSQL文内の条件分岐を予め文字列化しておくようにしようと思います。

このたびはどうもありがとうございました。

以上、よろしくお願いします。

回答No.2

30246kiku

ご質問の現象についてではありません。
ちょっと気になったので

> strsql = strsql & " Between IIf(Nz([Forms]![F_1]![text_str])='',0,[Forms]![F_1]![text_end])"
> strsql = strsql & " And IIf(Nz([Forms]![F_1]![text_str])='',99999999,[Forms]![F_1]![text_end])))"

上記は正しいでしょうか。
上の行は、text_str　で、下の行は、text_end　になるのでは？と思います。
今のままでは、text_str　が空白でなければ、text_end　が両方の値になるような
（ Between text_end And text_end ）？

後、Where条件でフィールド側を加工するよりも、比較する値の方をフィールドに合わせた方が良いような気がします。

T_M.M_DAY が　2001/01/01　（テキスト型？）の値を持つのなら、
Format([Forms]![F_1]![text_str],"@@@@/@@/@@") にしたもので比較してはどうでしょう。

Between の条件を見ると、Between にこだわらずに、
必要な条件のみを、以上/以下( >= とか <= )で指定した方が楽なような気がします。

WHERE 部分の作成例）

Dim sWhere As String

sWhere = ""
If (Len(Nz([Forms]![F_1]![text_str])) > 0) Then
　　sWhere = sWhere & " AND (T_M.M_DAY >= '" _
　　　　　　　　& Format([Forms]![F_1]![text_str], "@@@@/@@/@@") & "')"
End If
If (Len(Nz([Forms]![F_1]![text_end])) > 0) Then
　　sWhere = sWhere & " AND (T_M.M_DAY <= '" _
　　　　　　　　& Format([Forms]![F_1]![text_end], "@@@@/@@/@@") & "')"
End If
If (Len(sWhere) > 0) Then sWhere = " WHERE " & Mid(sWhere, 6)

※どちらか片方でも条件があり文字列が作られていた場合、
先頭に付けていた　" AND " を削除した形で　WHERE部分を生成

後は、以下のように組み合わせれば

strsql = "INSERT INTO T_W ( W_DAY )"
strsql = strsql & "SELECT T_M.M_DAY"
strsql = strsql & " FROM T_M"
strsql = strsql & sWhere & " ORDER BY T_M.M_DAY"

※個人的には、VBAでSQLを作る時には即値に展開しています。
（フォームを参照させないようにしています）

※T_M.M_DAYはテキスト型と仮定して、Formatの前後を ' ' で囲ってます。

※実際に動かしてみていないので、、、

お礼

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

> 上の行は、text_str　で、下の行は、text_end　になるのでは？と思います。
おっしゃるとおりです。
ご指摘ありがとうございます。

ちなみに、T_M.M_DAYは日付/時刻型となります。

ご指摘のソースを実行してみたところ、私の意図する動作とは下記が異なるようでした。

1. 条件未入力時、空白のレコードを持ってきてしまう
（私が記載したBetweenのソースの場合、条件未入力時、0～99999999のレコードを取得し、
それ以外のレコード(例:空白)は対象外となります）。

2. 存在しない日付を入力の上、実行すると、日付の構文エラーになるのではと思いました。
【サンプル】
Private Sub btn_Click()
Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim str As String
Dim strHead As String
Dim strTail As String

Set ws = DBEngine.Workspaces(0)
Set db = CurrentDb

ws.BeginTrans

strHead = Format(Me.text_str, "@@@@/@@/@@")
strTail = Format(Me.text_end, "@@@@/@@/@@")

strsql = "INSERT INTO T_W ( W_DAY )"
strsql = strsql & " SELECT T_M.M_DAY"
strsql = strsql & " FROM T_M"
strsql = strsql & " WHERE M_DAY Between #" & strHead & "# AND #" & strTail & "#"
strsql = strsql & " ORDER BY T_M.M_DAY"

db.Execute strsql

ws.CommitTrans
End Sub

今回は、20090000と20100000と入力された場合、
2009/00/00～2010/00/00の間に存在する日付を抽出したいと考えている為、
質問文のようなソースとなっております。

それと、20090101を2009/01/01に変換するのに
「Format(Me.text_str, "@@@@/@@/@@")」という書き方ができるのは知らなかったので、
参考になりました（今まではLeftとMidとRightを組み合わせていたので）。

また、下記の方法も今まで考えてもいなかった方法なので参考になりました。
> 先頭に付けていた　" AND " を削除した形で　WHERE部分を生成

以上、よろしくお願いします。