こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

ACCESS VBA クエリを開く

ACCESS初心者です。
VBAはEXCELで独学した程度の知識です。

フォームから受注納期を入力し、クエリを抽出。
クエリでレコード毎に製品票の印刷枚数を計算し
レポートで各レコード毎に求められた印刷枚数分を
印刷させたいのですが・・・

クエリを開く段階でつまずいており困っています。

Private Sub コマンド23_Click()

Dim db As DAO.database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("受注クエリ")

With qdf
.Parameters("納期") = Format(Forms![受注データ一覧]![納期], "yyyy/mm/dd")
Set rs = .OpenRecordset
.Close
End With

MsgBox rs![注文番号]

rs.Close

End Sub

パラメータクエリの開き方、上記コードで問題ないでしょうか?

そしてこのコードを実行した際
実行時エラー3421
データ型の変換エラーが発生しましたと表示されます。

ローカルウィンドウで 変数rs = nothingとなっており
クエリのレコードが読み込めていないようです。
(Msgbox rs!注文番号はデータが読み込めたかテストするために
コードを書いています。)

どうぞ宜しくお願いいたします。

投稿日時 - 2014-01-02 19:15:13

QNo.8410590

困ってます

質問者が選んだベストアンサー

ですから、

>など設定がばらばらなのでQueryDefを使ってパラメータを
>VBAで設定するならば、他の方への補足の内容をもとにすると、
>SQL文を、

として、補足にあるパラメータの宣言、

>PARAMETERS 納期1 DateTime;

をはずしたクエリのSQL文を提案し、VBAでは、

>With qdf
>.Parameters("納期") = Format(Forms![受注データ一覧]![納期], "yyyy/mm/dd")
>Set rs = .OpenRecordset
>.Close
>End With

のところで、

With qdf
qdf.Parameters("XYZ") = CDate(Format(Forms![受注データ一覧]![納期], "@@@@/@@/@@"))
Set rs = .OpenRecordset
End With

のように、Parameters("納期") を Parameters("XYZ") のように
パラメータが「納期」というようなフィールド名と同じになるのを
避けるために「XYZ」としているのですが・・・。パラメータと
フィールド名を同じにするとエラーが出るのでこのようにしています。



補足にある、

>フォームからyyyy/mm/ddの書式で日付を指定し該当する
>レコードをクエリで抽出します。

ということは、フォームではテキストボックスの書式が
日付型に設定してあると、いうことだと解釈します。したがって、
VBAでは、
.Parameters("XYZ") = Forms![受注データ抽出]![date1]
とします。


>上記記述で
>実行時エラー3061
>パラメーターが少なすぎます。2を指定してください。
>エラーが発生します。

これは、補足にある書き変えたVBAの中で、

>With qdf
>.Parameters("納期1") = Forms![受注データ抽出]![date1]
>Set rs = db.OpenRecordset("確定クエリ", dbOpenDynaset)
>End With

のところで、
db.OpenRecordset
としているために、「確定クエリ」をパラメータを指定せずに
レコードセットとして接続しようとしているためにエラーが
エラーがでるのです。したがって、ここのところは、もともとの
設定のようにQueryDefを使って、

With qdf
.Parameters([XYZ]) = Forms![受注データ抽出]![date1]
Set rs = .OpenRecordset("確定クエリ")
End With

とします。ようするに、
Set rs = qdf.OpenRecordset("確定クエリ")
ということです。





以上より、クエリの「納期1」の抽出条件に「[XYZ]」 (XYZではなく、[XYZ]です)と記入し、
そのクエリのSQL文は、

SELECT 確定.注文番号, 確定.注文年月日, 確定.発注者品名コード, 確定.[品名(品名仕様)], 確定.[注文数量(受注数量)], 社内データ.SNP, 確定.単位, 確定.納期1, 確定.納入No1, 確定.納品キー番号1, 確定.受渡場所名, 確定.発注者用備考, 確定.発注者名, Int([納入指示数量1]/[SNP]+0.5) AS 印刷枚数
FROM 確定 LEFT JOIN 社内データ ON 確定.発注者品名コード = 社内データ.得意先品目コード
WHERE (((確定.納期1)=[XYZ]));


VBAは、

Private Sub コマンド23_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("確定クエリ")

With qdf
.Parameters("XYZ") = Forms![受注データ抽出]![date1]
Set rs = .OpenRecordset
End With

MsgBox rs![注文番号]

qdf.Close: Set qdf = Nothing
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub

投稿日時 - 2014-01-05 17:24:17

お礼

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

上記コードを試してみたところ無事にクエリを開くことができました。
ご回答いただいたコードを参考に
.Parameters("Forms![受注データ抽出]![date1]") = Forms![受注データ抽出]![date1]
とし、同様の結果を得ることができました。

これまでのコードミスと原因が理解できました。
当初のご回答、理解不足で誠に申し訳ありません。

またコードの掲載に際して修正により混乱を招いて
しまい申し訳ありませんでした。

ご回答いただいた他ご回答者様へもこの場をお借りして
お詫び申し上げます。


どうもありがとうございました。

投稿日時 - 2014-01-05 20:35:57

このQ&Aは役に立ちましたか?

9人が「このQ&Aが役に立った」と投票しています

回答(7)

ANo.7

#5ですが、あとからあとから訂正入るが、列名見直しした?
intの中身、納入指示数量1はドコにあるの?

テーブルの列名とデータ型も開示したほうが良くない?

投稿日時 - 2014-01-04 16:54:29

補足

度々申し訳ありません。

納入指示数量1⇒注文数量(受注数量)の誤りでした。


フィールド名、データ型は下記の通りです。

確定テーブルより
注文番号     [テキスト型]
注文年月日    [日付/時刻型]
発注者品名コード [テキスト型]
品名(品名仕様)  [テキスト型]
注文数量(受注数量)[数値型]
単位       [テキスト型]
納期1       [日付/時刻型]
納入No1      [テキスト型]
納品キー番号1   [テキスト型]
受渡場所名    [テキスト型]
発注者用備考   [テキスト型]
発注者名     [テキスト型]

社内データテーブルより
SNP        [数値型]
得意先品目コード [テキスト型]

クエリ 計算式による新規フィールド
印刷枚数      [数値型]

投稿日時 - 2014-01-04 20:59:01

ANo.6

No3です。
まず、前提として「納期」が日付/時刻型であるとしています。
そうでないならば、テーブルの設定を日付/時刻型にしないと、
変換する意味がありませんが。No3の型が一致しないという
エラーもひょっとして「納期」のフィールドの型が
日付/時刻型以外の型になっていませんか。


>PARAMETERS 納期1 DateTime;
とは、あるフィールドに設定した[納期1]という
抽出条件が、日付/時刻型であるということなのに、
VBAにおいては、
>.Parameters("納期") = Format(Forms![受注データ一覧]![納期],
のように[納期]というフィールド名と同じパラメータ名を
設定していたり・・・・・
など設定がばらばらなのでQueryDefを使ってパラメータを
VBAで設定するならば、他の方への補足の内容をもとにすると、
SQL文を、


SELECT
受注.注文番号, 受注.注文年月日, 受注.品名コード, 受注.[品名(仕様)], 受注.[注文数量(数量)], 参照データ.SNP, 受注.単位, 受注.納期, 受注.納品No, 受注.発注者, Int([注文数量(量)]/[SNP]+0.5)
AS 印刷枚数
FROM 受注 LEFT JOIN 参照データ ON 受注.品名コード = 参照データ.品目コード
WHERE 受注.納期=[XYZ];

として、行儀良く後始末をするためにVBAを、


Private Sub コマンド23_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("受注クエリ")

With qdf
qdf.Parameters("XYZ") = CDate(Format(Forms![受注データ一覧]![納期], "@@@@/@@/@@"))
Set rs = .OpenRecordset
End With

MsgBox rs![注文番号]

qdf.Close: Set qdf = Nothing
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub


補足の中のSQL文には、「納期1」、「受注.納期1」「受注.納期」
など、意味不明のフィールド名が出てくるので、「受注」テーブル
には、「納期」というフィールドが存在していて、「納期1」という
フィールドは存在しないものとします。

なお、「受注データ一覧」というフォームの「納期」という
(たぶん)テキストボックスに入力するのは、たとえば、
「20140115」のような数値型あるいはテキスト型の
文字列という前提にしています。そのために
CDate(Format(Forms![受注データ一覧]![納期], "@@@@/@@/@@"))
のような型変換をしています。

これでエラーが出るようであれば他の問題があるのでは?

投稿日時 - 2014-01-03 17:41:01

補足

申し訳ありません、得意先の名前がわかるテーブルや
フィールドがあった為SQLを手直しして掲載した為
誤ったコードになってしまいました。

改めてクエリを作成、フィールド名も確認しました。
フォームからyyyy/mm/ddの書式で日付を指定し該当する
レコードをクエリで抽出します。
フィールドの納期1については日付の書式に設定されていたので
Parameterのコードを書き換えました。

投稿日時 - 2014-01-03 19:05:37

お礼

補足を誤って確定してしまいました。

下記にSQLコードとVBAコードを記載します。

PARAMETERS 納期1 DateTime;
SELECT 確定.注文番号, 確定.注文年月日, 確定.発注者品名コード, 確定.[品名(品名仕様)], 確定.[注文数量(受注数量)], 社内データ.SNP, 確定.単位, 確定.納期1, 確定.納入No1, 確定.納品キー番号1, 確定.受渡場所名, 確定.発注者用備考, 確定.発注者名, Int([納入指示数量1]/[SNP]+0.5) AS 印刷枚数
FROM 確定 LEFT JOIN 社内データ ON 確定.発注者品名コード = 社内データ.得意先品目コード
WHERE (((確定.納期1)=[Forms]![受注データ抽出]![date1]));

Private Sub コマンド23_Click()

Dim db As DAO.database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("確定クエリ")

With qdf

.Parameters("納期1") = Forms![受注データ抽出]![date1]
Set rs = db.OpenRecordset("確定クエリ", dbOpenDynaset)

End With

MsgBox rs!注文番号

qdf.Close: Set qdf = Nothing
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub

上記記述で
実行時エラー3061
パラメーターが少なすぎます。2を指定してください。
エラーが発生します。

ただ他にパラメータを設定しているフィールドがありません。
やはり他に誤った記述があるのでしょうか・・・

投稿日時 - 2014-01-03 19:16:03

ANo.5

VBAを介さないクエリで動かしてみるのが良さそう。

「パラメータを要求される」とは、SQL文に出てくる項目がFromで指定された
テーブル・クエリに存在しないから「どうすれば良いの?」と問われることです。

whereの左辺、なぜ項目末尾に1がある?投稿のためのコピペミスじゃ無いでしょ。
一度、すべての列名を見直しされると良いと思います。

投稿日時 - 2014-01-03 12:37:10

ANo.4

Endwithの前行でCLOSEしちゃダメでしょ。

「受注クエリ」が2つ目のパラメータを要求してるのかな?
SQLビューで開いてコピペで開示できますか?

投稿日時 - 2014-01-03 02:30:15

補足

.close 失礼しました。
新規で空のデータベースに適当なデータを入力し
(パラメータの設定は無し)
クエリをRecordsetで開いたところ問題なく開けました。

下記にSQLコードを記述します。


PARAMETERS 納期1 DateTime;
SELECT
受注.注文番号, 受注.注文年月日, 受注.品名コード, 受注.[品名(仕様)], 受注.[注文数量(数量)], 参照データ.SNP, 受注.単位, 受注.納期, 受注.納品No, 受注.発注者, Int([注文数量(量)]/[SNP]+0.5) AS 印刷枚数
FROM 受注 LEFT JOIN 参照データ ON 受注.品名コード = 参照データ.品目コード
WHERE (((受注.納期1)=[Forms]![受注データ一覧]![納期]));

やはり問題は納期のパラメータでしょうか?
フィールド印刷枚数には計算結果のデータを入れています。
テーブルは同フォルダにcsvファイルを落とし込み
リンクテーブルを作成しています。

投稿日時 - 2014-01-03 11:22:19

ANo.3

>Format(Forms![受注データ一覧]![納期], "yyyy/mm/dd")



CDate(Format(Forms![受注データ一覧]![納期], "@@@@/@@/@@"))

投稿日時 - 2014-01-02 23:12:42

補足

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

ご指摘のコードを試してみた結果ですが
別のエラーが発生してしまいました。

実行時エラー13
型が一致しません。

投稿日時 - 2014-01-03 10:50:45

ANo.2

動作確認したわけではありませんが、想像での話しです。

パラメータ「納期」は日付型フィールドでしょうか?

Format()関数の戻り値はString型ですので、その値をCdate()関数を使用して日付型に
変換してから、パラメータにセットしたらどうでしょうか?

参考URL:http://office.microsoft.com/ja-jp/access-help/HA001229018.aspx

投稿日時 - 2014-01-02 20:15:12

補足

回答ありがとうございます。
早速CDate関数を使用してみました。

CDate(Format(Forms![受注データ一覧]![納期], "yyyy/mm/dd"))

先の質問のエラー表示ではなく
実行時エラー3061
パラメーターが少なすぎます。2を指定してください。
とのエラーメッセージ。

デバッグを開くと

Set rs = .OpenRecordset 部分がマーキングされ
rs = nothing
やはりクエリが開けていない様子でした。

投稿日時 - 2014-01-02 20:59:44

あなたにオススメの質問