Microsoft Access 掲示板

Microsoft Access 掲示板

年月で期間抽出コード

16 コメント
views
4 フォロー

ビギナーのものです。２０２３／０４、２０２３／０５等のフォーマット関数で作成した年月データがあるクエリを基に作成したフォームがあります。それの指定年月期間を抽出するコードが上手く出来ないため、やもえず下記の2つに分けてフィルターをしています。簡素化するために１つのコードにまとめるコードはどう記述したららいいでしょうか？　コードのﾙｰﾙが分かっておらず、色々試しても出来ないので。
変数1 = "年月>= '" & Me.[コンボ年月1] & "'"
変数2 = "年月<= '" & Me.[コンボ年月2] & "'"
 Me.Filter = 変数1 & " AND " & 変数2

mini
作成: 2023/11/09 (木) 13:48:58
通報 ...
1
hiroton 2023/11/09 (木) 15:02:43 67b2d@f966d

そのまま右辺の内容を変数1、変数2の位置に記述すればそれでいいと思いますが

 Me.Filter = "年月>= '" & Me.[コンボ年月1] & "'" & " AND " & "年月<= '" & Me.[コンボ年月2] & "'"

ただ、文字列リテラルをわざわざ分割して記述する必要はないので"'" & " AND " & "年月<= '"の部分は一つにまとめて

 Me.Filter = "年月>= '" & Me.[コンボ年月1] & "' AND 年月<= '" & Me.[コンボ年月2] & "'"

もしくは、範囲を抽出するならBETWEEN n1 AND n2を使って

 Me.Filter = "年月 BETWEEN '" & Me.[コンボ年月1] & "' AND '" & Me.[コンボ年月2] & "'"

なんていうのもありです

余談ですが、フィルタのコードは複雑化したりするのでバラしておいたほうが（コードを読み解く場合と合わせて）簡素だったりしますね

Dim strFilter As String
'//範囲開始フィルタ
strFilter = strFilter & " AND 年月>= '" & Me.[コンボ年月1] & "'"
'//範囲終了フィルタ
strFilter = strFilter & " AND 年月<= '" & Me.[コンボ年月2] & "'"
Me.Filter = Mid(strFilter,6)

テンプレート的な記述で、この掲示板でも何度かこのようなコードを書いています

2
たろう 2023/11/09 (木) 15:24:06 ddfe5@fd8f8

hirotonさんありがとうございました。質問が大きく変わって申しわけないのですが、このコンボで”実行時ｴﾗｰ2766”　”式が正しく入力されていないか、複雑すぎるために評価できません。たとえば、数式に複雑な要素が多すぎます。
変数に式の一部を割り当て、式を簡単にしてください。”が頻繁にでます（毎回ではなく普通に機能することもあります）。コードに問題あるのかなと思ってたのですがそうではなさそうです（すみません）。
コンボのSQLは下記です。
『SELECT Q買掛の支払締日と期日.支払締の年月
FROM Q買掛の支払締日と期日
WHERE (((Q買掛の支払締日と期日.買掛先ID)=[Forms]![F買掛仕入一覧]![cb取引先選択]) AND ((Q買掛の支払締日と期日.支払締の年月)>[Forms]![F買掛仕入一覧]![cb支払締の年月1] And (Q買掛の支払締日と期日.支払締の年月)<>"支払処理")) OR (((Q買掛の支払締日と期日.支払締の年月)>[Forms]![F買掛仕入一覧]![cb支払締の年月1] And (Q買掛の支払締日と期日.支払締の年月)<>"支払処理") AND (([Forms]![F買掛仕入一覧]![cb取引先選択]) Is Null))
GROUP BY Q買掛の支払締日と期日.支払締の年月
ORDER BY Q買掛の支払締日と期日.支払締の年月』
顧客の選択があるときは顧客＋年月、選択ないときは年月のみでフィルターしています。
これが全然解決できなくて困っております･･･

3
hiroton 2023/11/09 (木) 16:03:14 67b2d@f966d

元のデータ（テーブル構造）からみないと何ともですが、

『SELECT Q買掛の支払締日と期日.支払締の年月
FROM Q買掛の支払締日と期日
WHERE (((Q買掛の支払締日と期日.買掛先ID)=[Forms]![F買掛仕入一覧]![cb取引先選択]) AND ((Q買掛の支払締日と期日.支払締の年月)>[Forms]![F買掛仕入一覧]![cb支払締の年月1] And (Q買掛の支払締日と期日.支払締の年月)<>"支払処理")) OR (((Q買掛の支払締日と期日.支払締の年月)>[Forms]![F買掛仕入一覧]![cb支払締の年月1] And (Q買掛の支払締日と期日.支払締の年月)<>"支払処理") AND (([Forms]![F買掛仕入一覧]![cb取引先選択]) Is Null))
GROUP BY Q買掛の支払締日と期日.支払締の年月
ORDER BY Q買掛の支払締日と期日.支払締の年月』

下ではNullチェックすることがあるのに上では「=」比較をしているとか

『SELECT Q買掛の支払締日と期日.支払締の年月
FROM Q買掛の支払締日と期日
WHERE (((Q買掛の支払締日と期日.買掛先ID)=[Forms]![F買掛仕入一覧]![cb取引先選択]) AND ((Q買掛の支払締日と期日.支払締の年月)>[Forms]![F買掛仕入一覧]![cb支払締の年月1] And (Q買掛の支払締日と期日.支払締の年月)<>"支払処理")) OR (((Q買掛の支払締日と期日.支払締の年月)>[Forms]![F買掛仕入一覧]![cb支払締の年月1] And (Q買掛の支払締日と期日.支払締の年月)<>"支払処理") AND (([Forms]![F買掛仕入一覧]![cb取引先選択]) Is Null))
GROUP BY Q買掛の支払締日と期日.支払締の年月
ORDER BY Q買掛の支払締日と期日.支払締の年月』

「年月」データに対して「"支払処理"（なにかの文字列）」比較してるとかは首をかしげる部分ですね
とくに、Nullは扱いを間違うと不具合がよく出るので注意が必要です

選択ないときは年月のみで

「選択がない（=Null）ときは全データを対象にする」のはちょっとテクニックが必要ですが、たとえば

SELECT 支払締の年月
FROM Q買掛の支払締日と期日
WHERE 買掛先ID=Nz([Forms]![F買掛仕入一覧]![cb取引先選択],買掛先ID)
 AND 支払締の年月>Nz([Forms]![F買掛仕入一覧]![cb支払締の年月1],"")
 AND 支払締の年月<>"支払処理"
GROUP BY 支払締の年月
ORDER BY 支払締の年月

とかしてみたらどうでしょう？

4
たろう 2023/11/09 (木) 16:42:49 ddfe5@fd8f8

hirotonさんありがとうございました。それにして試してみます。いける時とエラーがでるときがあるので。結果はまた連絡しますね。因みに”Nz([Forms]![F買掛仕入一覧]･･･”のNzはこの場合どういう意味合いになりますでしょうか？
「年月」データに対して「"支払処理"（なにかの文字列）」比較は･･･基クエリにIIFでそうしているのですが年／月と文字を一緒にしない方がいいでしょうか？

5
hiroton 2023/11/09 (木) 17:21:46 67b2d@f966d

Nz 関数

1つ目のデータがNullなら2つ目のデータに置き換える関数です
今回の場合
[Forms]![F買掛仕入一覧]![cb取引先選択]が選択済みのとき

買掛先ID=[Forms]![F買掛仕入一覧]![cb取引先選択]

[Forms]![F買掛仕入一覧]![cb取引先選択]が未選択（Null）のとき

買掛先ID=買掛先ID

上記どちらかが実行されることになります。買掛先ID=買掛先IDは自分自身と比較すれば必ずTRUEになる(※)ので、すなわち全データ。ということになります

※買掛先IDがNULLの場合、正しく動作しません

「年月」データに対して「"支払処理"（なにかの文字列）」比較は･･･基クエリにIIFでそうしているのですが年／月と文字を一緒にしない方がいいでしょうか？

データベースでは「１つのフィールドに複数の情報を持たせない」というのが鉄則です
フィールド名のイメージと合わない情報が含まれるデータ構造は、なにかと厄介な問題を引き起こします
6
hatena 2023/11/10 (金) 00:47:33

２０２３／０４、２０２３／０５等のフォーマット関数で作成した年月データがあるクエリを基に作成したフォームがあります。

日付/時刻型のフィールドに対して例えばFormat([年月日],"yyyy/mm")というような式を設定した演算フィールドがあるということでしょうか。

だとしたら演算フィールドに対して抽出条件を設定するとインデックスが利用されないので、データ件数が多い場合は処理が重くなる可能性があります。
式は冗長になりますが、処理を少しでも高速にしたい場合は、演算フィールドにせずに下記のように生のフィールドに条件を設定するようにした方がいいです。(日付/時刻型フィールドにはインデックスを設定)

Me.Filter = "年月日>=#" & Me.[コンボ年月1] & "/01# AND " & _
            "年月日< #" & DateAdd("m", 1, Me.[コンボ年月2]) & "#"

参考リンク
抽出条件でインデックスが無効になる場合 - hatena chips

7
たろう 2023/11/13 (月) 09:39:19 ddfe5@f146b

（連絡が遅くなりました）hirotonさん hatenaさんありがとうございました。
hirotonさんのｱﾄﾞﾊﾞｲｽ通りNull時の条件にNz使い、年月部に関係ない文字列変換をやめたたら挙動がなくなり安定してFilterが出来てます。Nz関数はよくつかいますがｸｴﾘ条件に使うｱｲﾃﾞｱは全く思いもつきませんでした。
ひとつだけ理解できないのですが（知識不足で）最初の回答の下記です。
Dim strFilter As String
'//範囲開始フィルタ
strFilter = strFilter & " AND 年月>= '" & Me.[コンボ年月1] & "'"
'//範囲終了フィルタ
strFilter = strFilter & " AND 年月<= '" & Me.[コンボ年月2] & "'"
Me.Filter = Mid(strFilter,6)
⇒同じ変数の名前のstrFilterがあり、Mid　6でどういう形で生成されるのでしょうか？

hatenaさんのｱｲﾃﾞｱも目からウロコです。年/月だけに固執してました。年/月から年/月/日に生成すればいいのですね。
DateAdd("m", 1, Me.[年/月形式])で最後の年/月には日がないけどDateAddで日付形式にしておけば例えば2023/10が勝手に2023/10/1と生成されるということなのですね。

8
hiroton 2023/11/13 (月) 11:13:08 0d37e@f966d

同じ変数の名前のstrFilterがあり、Mid　6でどういう形で生成されるのでしょうか？

一応の確認ですが、プログラミングにおける（特にここの）「=」は代入を表していて「左の変数に右の計算結果を代入する」という動作をします
なので、

変数 = 変数 & (追加の式)

の形は、「元の変数の後ろに文字を追加する」記述になります

Me.Filter = Mid(strFilter,6)について

Mid 関数

「任意のフィルタを設けたい」時にコードが簡素に記述できます
フィルタを複数項目で連続してかける場合、それぞれの条件を AND でつなぎますが、「項目が任意」の場合、既にフィルタがあるかどうか（ AND をつけるかどうか）をチェックするのが煩わしくなってきます
なので、フィルタをつける場合はとりあえず AND をつける、最後に先頭の AND を除いた文字を取り出すという手順にして簡素化しています

もう一つ、改修を見越してというのもあります
「フィルタ項目を追加したい」となった場合、上記のような問題を考える必要がでるので、もともと対応済みの記述にしておけば、「元のコードに手を付けず、追加したい項目の処理を追加するだけ」で済みます

「年月」データの持ち方について

パソコンは基本的に文字列よりも数値のほうが楽に扱えます。日付のデータも、表示上は書式設定にて「yyyy/mm/dd」形式で表示し、内部的には数値データとして持つ場合が多いです（シリアル値と呼ばれたりします）
この場合、関連する日付を数値計算で算出したり、日付計算用の関数が使えたりするというメリットが受けられます

ただし、必ず「日」のデータまで必要なので「年月」でいい場合には注意が必要です。「日」まで気にして範囲指定しないと漏れが出たりします（そのための計算で複雑化したりします）

現状であろう「年/月」の文字列で保存するとか、「yyyymm」を数値として保存するとかありますが、桁数（月の十の位の0詰め）に気を付ける必要があるとか出ますし、究極的には「年」「月」でフィールドを分けて保存するのが一番安全なのかなと思っています

9
たろう 2023/11/13 (月) 12:32:50 ddfe5@f146b

hirotonさん　すごく分かり易い説明ありがとうございました。自分では全くでてこないアイデアです。
日付を年・月・日に分けた場合（クエリでYear関数等で）、単独日付のフィルター式はＡＮＤで結合させて記述できるのですが範囲指定となるとどうもややこしくて、避けています。例えば2023・10･01　から　2024・01･31　は年月日が分割されたデータを使ってどうやってコード記述したら出来ますでしょうか？

10
hiroton 2023/11/13 (月) 15:37:06 修正 0d37e@f966d

前提として、「日」までデータに含むなら「年月日」で一つのデータにしたほうがいいです

不要部分を丁寧に除外してくとわかりやすいんじゃないかと思います

SELECT 年, 月
FROM TBL1
WHERE not 年<2023 and not (年=2023 and 月<10) and not (年=2024 and 月>1) and not 年>2024;

SQL直接記述するとなんてことないんですけど、クエリデザインで作ろうとしたらなかなか大変ですねこれ

11
たろう 2023/11/13 (月) 16:32:49 ddfe5@f146b

hirotonさん ありがとうございます。ｸｴﾘﾃﾞｻﾞｲﾝよりもSQLで記述した方が簡単な事も多いのですね。SQL記述を勉強した方が今後いいですね。（今まで直接記述したことがないので）
何度もすみません、この関連で最後に1つだけ教えて欲しいのですが、年と月だけを使って（数字扱いで）範囲指定するにはどういうｺｰﾄﾞになりますでしょうか？（Filter）　
例えば年の数値ﾃﾞｰﾀが2023、2024とあり月の数値ﾃﾞｰﾀが1～12があります。一つの年月なら下記で出来るのですが
 strFilter1 = "年 =" & Me.[ｺﾝﾎﾞ年1]
 strFilter2 = "月 =" & Me.[ｺﾝﾎﾞ月1]
 Me.Filter = strFilter1 & " AND " & strFilter2

範囲となるとｺｰﾄﾞがややこしいもので。（ｺﾝﾎﾞ年2、ｺﾝﾎﾞ月2を範囲最後として）　

12
hiroton 2023/11/13 (月) 17:24:50 0d37e@f966d

上のSQL構文を元にたろうさんのデータでSQLビューでクエリを作成したあと、保存したクエリを開きなおしてみてください。デザインビューで表現する方法が表示されると思いますが、hirotonはサッと作れるようなデザインではなかったですね

あとは、任意項目でフィルタを掛けたい場合なんかはVBAで作ったほうが楽なので、やっぱりSQLそのものを覚えていくと出来ることが増えるでしょう

コードでフィルタを作る場合も、SQL構文のWHERE部分を作ればいいだけなので、上記の形でいいならそのまま、数字部分をコントロール参照に変えるだけですよ

1行で記述してもいいと思いますが、分割しながら記述するなら

    Dim strFilter As String
    
    '範囲開始
    strFilter = strFilter & " AND NOT 年<" & Me!cmb年1
    strFilter = strFilter & " AND NOT (年=" & Me!cmb年1 & " AND 月<" & Me!cmb月1 & ")"
    '範囲終了
    strFilter = strFilter & " AND NOT (年=" & Me!cmb年2 & " AND 月>" & Me!cmb月2 & ")"
    strFilter = strFilter & " AND NOT 年>" & Me!cmb年2
    
    Me.Filter = Mid(strFilter, 6)
13
hiroton 2023/11/13 (月) 17:51:29 0d37e@f966d

ただ、元の質問をみると、「年」と「月」でコンボボックスを分けるのはあんまり望んでなさそうですよね

テーブル構造で「年」「月」分けた場合でも、コンボボックスのSQLでユーザー選択用に「年月」表記にして、コンボボックスは一つにしてもいいと思います

SELECT 年 & "/" & Format(月,"00") AS 年月 , 年,　月

これで、コンボボックスの列数プロパティを「3」にすると、フォーム上の表記は「yyyy/mm」形式で、コードでは2列目、3列目を参照して「年」「月」のデータを直接使えるようになります

範囲の開始用を「cmb期間開始」、終了用を「cmb期間終了」とすることにして

    Dim strFilter As String
    Dim 開始年 As Long
    Dim 開始月 As Long
    Dim 終了年 As Long
    Dim 終了月 As Long
    
    'データチェック
    If IsNull(Me!cmb期間開始) Or IsNull(Me!cmb期間終了) Then Exit Sub
    
    開始年 = Me!cmb期間開始.Column(1)
    開始月 = Me!cmb期間開始.Column(2)
    終了年 = Me!cmb期間終了.Column(1)
    終了月 = Me!cmb期間終了.Column(2)
    
    '範囲開始
    strFilter = strFilter & " AND NOT 年<" & 開始年
    strFilter = strFilter & " AND NOT (年=" & 開始年 & " AND 月<" & 開始月 & ")"
    '範囲終了
    strFilter = strFilter & " AND NOT (年=" & 終了年 & " AND 月>" & 終了月 & ")"
    strFilter = strFilter & " AND NOT 年>" & 終了年
    
    Me.Filter = Mid(strFilter, 6)

コンボボックスの列指定まで入れるとコードが見にくくなるので、それらもそれぞれ変数に置くようにすると、SQL構築部分もすっきりしていいんじゃないかと思います

まぁ、どこまで細かく記述するか？は慣れと好みでいいところではあります

14
たろう 2023/11/14 (火) 08:46:18 ddfe5@f146b

hirotonさん　ありがとうございました。ｺｰﾄﾞの事で strFilter & " AND NOT 年＜　等でこのNOTはどういう意味になるのでしょうか？　理解力がなくてすみません。

15
hiroton 2023/11/14 (火) 10:19:34 修正 a415b@f966d

既に説明しているところの繰り返しになりますが

例えば2023・10･01　から　2024・01･31　は年月日が分割されたデータを使ってどうやってコード記述したら

を実現するためにはクエリ（SQL構文）で

WHERE not 年<2023 and not (年=2023 and 月<10) and not (年=2024 and 月>1) and not 年>2024

を実行します

ACCESSの仕様として、フォーム上でフォームのレコードソースからさらにデータを抽出する場合、フィルタプロパティにSQLのWHERE句と同じ記述方法を指定することができます
画像1

フォームのプロパティはVBAから操作することができ、フィルタプロパティはMe.Filterで参照し

Me.Filter = "not 年<2023 and not (年=2023 and 月<10) and not (年=2024 and 月>1) and not 年>2024"
Me.FilterOn = True

とすれば、フィルタをかけることができます

なので、NOTはSQL構文で使うところのNOTです。上記のWHEREをNOTが掛かる部分ごとに分けると次のようになります

WHERE
 not 年<2023
and
 not (年=2023 and 月<10)
and
 not (年=2024 and 月>1)
and
 not 年>2024

NOTは「否定」を表すので、「指定内容以外はOK」となります。つまり、「ここはダメ」を指定します。必要なだけ「ここはダメ」を重ねる（ANDで繋げる）と欲しい部分が残ります

画像1

まぁ、SQLの記述方法はいろいろあるので、ここの方法は一つではありません。NOTを使わずに記述することもできるでしょう。そういうのも経験ですね

16
たろう 2023/11/14 (火) 13:58:31 ddfe5@f146b

hirotonさん　ありがとうございました。消去方的な感じだったのですね。図まで用意して頂き大変分かり易かったです。お手数かけました。逆の発想が中々出来ず、自分自身がもどかしいところです。本当に色々とありがとうございました。