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

クエリの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さん、ご返信ありがとうございます。

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