Microsoft Access 掲示板

Microsoft Access 掲示板

サブクエリ内での変数展開

2 コメント
views
4 フォロー

テーブルの構成

　テーブル名　      myDate
　　カラム名　   partsCode テキスト型  部品番号
　　　　　　　   orderDate 日付型　　　受注日
　　　　　　　deliveryDate 日付型　　　納期

　テーブル名　   calendar
　　カラム名　     myDate　日付型　 月間のカレンダー
　　　　　　　holidayFlag　YES/NO型 休日にレ点を入れる

サブクエリで[myDate]テーブルにあるレコードの納期と受注日の間にある
休日回数(レ点あり)を取得したいと思い、サブクエリを作成しました。

ACCESSの場合は、BETWEEN句内で日付を"#"を&とダブルクォーテーションで囲みましたが
エラーになりました。

SELECT
 O.partsCode
,O.orderDate
,O.deliveryDate
,(SELECT COUNT(myDate) AS A
     FROM calendar
    WHERE (myDate BETWEEN & "#" & O.orderDate & "#" & AND & "#" & O.deliveryDate & "#") &  
      AND  (holidayFlag = true))" AS myholiDay
FROM myData AS O;

そこで、SQL文全体をダブルクォーテーションでくくり、日付のカラムの変数を展開できるように
したところ、SQL文自体が文字列として、出力さてしました。　BETWEEN句内の日付は展開されていました。

SELECT
 O.partsCode
,O.orderDate
,O.deliveryDate
,("SELECT COUNT(myDate) AS A
     FROM calendar
    WHERE (myDate BETWEEN" & "#" & O.orderDate & "#" & "AND" & "#" & O.deliveryDate & "#") & "
      AND  (holidayFlag = true))" AS myholiDay
FROM myData AS O;

SQL　結果

    partsCode 　orderDate 　deliveryDate　 myholiDay
999-999-99999 　2022/12/01 　2022/11/01 　　SELECT
　　　　　　　　　　　　　　　　　　　　　　　　　　　O.partsCode
　　　　　　　　　　　　　　　　　　　　　　　　　,O.orderDate
　　　　　　　　　　　　　　　　　　　　　　　　　,O.deliveryDate
　　　　　　　　　　　　　　　　　　　　　　　　　,(SELECT COUNT(myDate) AS A
     　　　　　　　　　　　　　　　　　　　　　　     FROM calendar
    　　　　　　　　　　　　　　　　　　　　　　     WHERE (myDate BETWEEN #2022/12/01# AND #2022/12/30#)
      　　　　　　　　　　　　　　　　　　　　　　     AND (holidayFlag = true)) AS myholiDay
　　　　　　　　　　　　　　　　　　　　　　　　      FROM myData AS O;

試しに日付の所を文字列を代入した所、正常に動作しました。
以下、正常動作のSQL文

SELECT
 O.partsCode
,O.orderDate
,O.deliveryDate
,( SELECT COUNT(myDate) AS A
     FROM calendar
    WHERE (myDate BETWEEN #2022/12/01# AND #2022/12/20#)
      AND (holidayFlag = true) AS myholiDay
FROM myData AS O;

またSQLサーバ-のビューであれば、以下のSQL文で正常に動作しました。
※カラム名を別名に置き換えるとエラーになりますが、カラム名をそのままの
場合では、正常に動作します。　　SQLServerでは、holidayFlagのカラムはbit型に
なるため、Trueを1に変えて記述しています。

SELECT
 O.partsCode
,O.orderDate
,O.deliveryDate
,(SELECT COUNT(myDate) AS A
   FROM calendar
  WHERE (myDate BETWEEN　O.orderDate AND　O.deliveryDate)
    AND  (holidayFlag = 1) AS myholiDay
  FROM myData AS O;

SQL　結果

    partsCode orderDate deliveryDate myholiDay
999-999-99999 2022/12/01 2022/11/01 　　　8　

BETWEEN句の変数を日付の文字列を代入した所正常に動作するため、
SQL文の構文には問題はなさそうに思えます。
Accessのサブクエリで、BETWEEN句内の変数展開を指定する方法は
別途あるのでしょうか。　皆様のお知恵をお借りしたいと思い、投稿させていただきました。
よろしくお願いいたします。

追記　202/11/23　11：40
DCOUNT関数を用いた記述では正常に動作します。
D系関数は処理件数が多くなると時間がかかる上、ほかのデータベースとの
親和性を考えて今回は見送る事にしています。　ACCESSを使用する頻度が
高い為、Access内でのサブクエリの記述を完成させたいと思います。

以下　DCOUNT関数での記述内容です。
, DCount("myDate","myCalendar","myDate BETWEEN" & "#" & O.orderDate & "#" & "AND" & "#" & O.deliveryDate & "#  AND holidayFlag = true" ) AS 式1

みどり
作成: 2022/11/23 (水) 11:21:27
最終更新: 2022/11/23 (水) 13:02:28
通報 ...
1
hatena 2022/11/23 (水) 14:01:08

クエリのSQLビューにSQLを記述するのですよね。
なんかVBAとか関数内の記述と混同してませんか。VBAや関数内ではSQL文は文字列にする必要がありますが、クエリのSQLビューならSQLをそのまま記述すればOKです。

基本、SQLServerなどのデータベースと同じ記述でいいです。

SELECT
 O.partsCode
,O.orderDate
,O.deliveryDate
,( SELECT COUNT(myDate) AS A
   FROM calendar
   WHERE (myDate BETWEEN O.orderDate AND O.deliveryDate)
      AND (holidayFlag = true)) AS myholiDay
FROM myDate AS O;
2
みどり 2022/11/23 (水) 14:52:18 a8670@817c8

hatenaさん、ご返信ありがとうございます。

なるほど、"＃"で括る必要はないのですね‼
回答頂いた方法で、正常に動作しました。
誠にありがとうございました。