Access フォームの検索機能で、Format([作業日],"yyyy\年mm\月") とまとめた日付型レコードを検索する方法

Question

Access(2007-2016)を利用し、勤怠管理DBを作成しています。表形式フォームにて、レコード検索を行えるようにしたいと思っているのですが、Format([作業日],"yyyy\年mm\月") とまとめた日付型レコードを検索する方法が分からず、質問させていただきました。

1.T_勤怠テーブル
・勤怠No：オートナンバー型
・作業日:日付型
・従業員名:短いテキスト

2.T_稼働テーブル
・稼働No：オートナンバー型
・勤怠No:数値型
・クライアント名:短いテキスト
・業務名：短いテキスト
・稼働時間：数値型

3.Q_月次_勤怠稼働クエリ
・作業月: Format([作業日],"yyyy\年mm\月") (グループ化)
・クライアント名 (グループ化)
・従業員名 (グループ化)
・業務名 (グループ化)
・稼働時間 (合計)

「Q_月次_勤怠稼働クエリ」をレコードソースにした表形式フォーム「F_勤怠稼働フォーム」にて、「クライアント名」「作業月」でのレコード検索を行えるようにしたいと思っています。現在は以下のようなコードで検索を行っています。

※クライアント検索窓を「client_Search」、検索開始日を「Time_start」、検索終了日を「Time_end」としています。

Private Sub 検索ボタン_Click()

 Dim strSearch As String

 strSearch = ""
 
 If client_Search <> "" Then
   strSearch = "(クライアント名='" & client_Search & "')"
 End If
 
 strSearch = strSearch & " AND "
strSearch = strSearch & "(作業月 Bwtween #" & Format(DateSerial(Year(Me.Time_start), Month(Me.Time_start), Day(Me.Time_start)), "yyyy年mm月") & " # AND # " _
 & Format(DateSerial(Year(Me.Time_end), Month(Me.Time_end), Day(Me.Time_end)), "yyyy年mm月") & "#)"

 Forms!F_勤怠稼働フォーム.Filter = strSearch
 Forms!F_勤怠稼働フォーム.FilterOn = True
 Forms!F_勤怠稼働フォーム.AllowAdditions = False

End Sub

しかしこれだと「3075エラー：構文エラー。演算子がありません」とのエラーが出てしまいます。「#」を「'」に変えてみても、同様のエラーが表示されます。

恐らくクエリで「xxxx/xx/xx」と表示される日付型データ「作業日」を、「xxxx年xx月」という文字列型表記のデータ「作業月」に変えてしまっているのが原因だと思われるのですが、どのように解決すればよいのでしょうか。お力添えいただけると助かります。

Answer 1

まず、下記の点について理解しましょう。

作業月: Format([作業日],"yyyy\年mm\月") (グループ化)

この演算フィールドは日付型ではなく、テキスト型になります。Formatの戻り値は文字列ですので。
つまり、"2020年08月"、"2020年10月" というような文字列です。

次に、

Filterプロパティに設定する条件式は、SQL文のWHERE句に設定する条件式を文字列にしたものを設定します。
SQLコードでは文字列は"または'で囲みます。

作業月が"2020年08月"から"2020年10月"までの範囲のデータを抽出したい場合は、下記のようなSQLコードになります。

作業月 Between "2020年08月" AND "2020年10月"

または

作業月 Between '2020年08月' AND '2020年10月'

VBAはSQLコードをコードとして解釈できないので、このSQL条件式をVBAの文字列にします。
VBAの文字列は"で囲みます。('では囲めません。）

"作業月 Between ""2020年08月"" AND ""2020年10月"""

または

"作業月 Between '2020年08月' AND '2020年10月'"

VBAの文字列内の"は""と記述します。後者の'を使う方法の方か読みやすいのでお勧めです。

現状の Time_start、Time_endテキストボックスは日付型で 2020/08/01 というように入力されているので、Format関数で文字列に変換します。

Format(Me.Time_start,"yyyy\年mm\月") というように作業月フィールドと同じ書式の文字列に変換します。

これを上記のVBA文字列としての条件式になる様にするには & 演算子で連結します。

"作業月 Between '" & Format(Me.Time_start,"yyyy\年mm\月") & "' AND '" & Format(Me.Time_End,"yyyy\年mm\月") & "'"

strSearchに代入するコードは、下記のようになります。

strSearch = strSearch & " AND "
strSearch = strSearch & _
            "(作業月 Between '" & Format(Me.Time_start,"yyyy\年mm\月") & _
            "' AND '" & Format(Me.Time_End,"yyyy\年mm\月") & "')"

---

フォームで「月範囲指定」したい場合、どのような検索仕様にするのがスマートなのでしょうか。

自分なら下記の２つのどちらかにします。

１．テキストボックスの定型入力を使う。

|プロパティ|設定値|
|:--|:--:|-
|定型入力|0000\年00\月;0;_|
|自動タブ|はい|

このように設定しておくと、Time_startテキストボックスで
「202008[Enter]202010」と入力するだけで、"2020年08月"、"2020年10月"という文字列になります。
キーボード入力をメインに高速入力したい場合はつかう手法です。
文字持つ列になっているので、VBAはFormat変換する必要はなくなります。

strSearch = strSearch & " AND "
strSearch = strSearch & _
            "(作業月 Between '" & Me.Time_start & _
            "' AND '" & Me.Time_End & "')"

　
２．年月選択はコンボボックスでする。

コンボボックスの値集合タイプを「テーブル/クエリ」、値集合ソースを下記のように設定します。

SELECT DISTINCT 作業月 FROM Q_月次_勤怠稼働クエリ ORDER BY 作業月;

これでQ_月次_勤怠稼働クエリ内の作業月がリストに表示されますのでそれで年月を選択できます。
これも文字列になってますので、１．の場合と同じvbaでstrSearchに代入できます。

Comments

[ryoya_access]

回答いただきありがとうございます。『クエリ式' AND (作業月 Between '2020年09月' AND '2020年10月')'の構文エラー：演算子がありません。』と表示されてしまうのですが、文字列型である「"yyyy\年mm\月")」をBetweenで挟む形で使えるのでしょうか。

[ryoya_access]

上記質問は取り消しました。

[hatena19]

> 文字列型である「"yyyy\年mm\月")」をBetweenで挟む形で使えるのでしょうか。

桁数の揃った書式の場合は、文字列でもBetweenは使えます。

[ryoya_access]

皆様コメントありがとうございました。今回はhatena様をベストアンサーとさせていただきます。

Answer 2

質問者様
まず「Q_月次_勤怠稼働クエリ」時点で作業月毎にまとめられているのに
なぜ「日付範囲指定」で取得されようとしているのでしょうか？

一部内容を判りやすくするために再編集してあります

strSearch = strSearch & _
"(作業月 Bwtween " & _
"#" & Format(DateSerial(Year(Me.Time_start), Month(Me.Time_start), Day(Me.Time_start)), "yyyy年mm月") & " #" & _
" AND " & _
"# " & Format(DateSerial(Year(Me.Time_end), Month(Me.Time_end), Day(Me.Time_end)), "yyyy年mm月") & "#)"

さらに編集

strSearch = strSearch & _
"(作業月 Bwtween " & _
"#" & Format(#2020/08/01#, "yyyy年mm月") & " #" & _
" AND " & _
"# " & Format(#2020/10/31#, "yyyy年mm月") & "#)"

さらに編集

strSearch = strSearch & _
"(作業月 Bwtween " & _
"#" & "2020年08月" & " #" & _
" AND " & _
"# " & "2020年10月" & "#)"

VBA上日付指定は[#2020年08月#][#2020年10月#]という形では指定できません。

「Q_月次_勤怠稼働クエリ」に

作業月始: CDate(Format([作業日],"yyyy/mm") & "/1")

という項目を作成するか
検索自体を

strSearch = strSearch & _
"(作業月 In (" & _
"""2010年08月"""," & _
"""2010年09月"""," & _
"""2010年10月""")"

となるように変更して下さい。

Comments

[ryoya_access]

まず「Q_月次_勤怠稼働クエリ」時点で作業月毎にまとめられているのに
なぜ「日付範囲指定」で取得されようとしているのでしょうか？

>>>フォームで「月範囲指定」が出来るのであれば、そのようにしたいと思っています。しかしフォームに検索用の日付型テキストボックスを設置しようとすると、「xxxx/xx/xx」と「何日」単位まで出てしまうので、それをFormat関数で「何年何月」の形にしようとした次第です。

フォームで「月範囲指定」したい場合、どのような検索仕様にするのがスマートなのでしょうか。

[kuma_kuma_]

> どのような検索仕様にするのがスマートなのでしょうか。
普通のコンボボックスで選択リストに過去1年分程度の年月を追加しておく
("yyyy年mm月"形式)
あとはその値を元にLeft("2020年08月",4) & "/" & mid("2020年08月",6,2) & "/1" で年月日変換
検索時の条件指定の際「作業月始」をBetweenで検索