更新クエリの日付抽出方法について

質問

Access2003を使用しています。

受注一覧DBを作成しています。
受注入力から請求書発行までを行いたいとおもっています。
テーブル：受注
クエリ：請求書発行検索
フォーム：受注入力，請求書発行検索

テーブル受注には、受注期間（開始日：2006/3/1～終了日：2006/4/30）
請求予定月2006年3月　というフィールドがあります。　

請求書発行検索フォームでは、検索テキストボックスに　2006/3　と入力すると対象データがを
抽出するようにしています。

請求書発行は、受注期間中の毎月月末です。
3/1-4/30の契約だと、3/31と4/30の2回発行することになります。現状は月末請求後に
手入力で請求月を入力していますが、更新クエリで自動入力することはできるでしょうか？？

「更新クエリ実行日が受注終了日よりも前ならば、請求予定月に１月足す」
かつ、「請求予定日が今月となっているときはそのままにする」

といったイメージです。。

いろいろと調べたのですが、書き方がわからなかったのです。
どうぞよろしくお願いいたします。

回答No.7

process9 · ベストアンサー

process9です。

蛇足かもしれませんが、もう１つやり方があったので。。。

＊＊＊＊締め日を指定して更新するクエリ＊＊＊＊＊
UPDATE 受注 SET 請求予定月 = DateAdd("m",1,[請求予定月])
WHERE [受注期間開始日]<=CDATE([締め日]) And CDATE([締め日])<=[受注期間終了日] And FORMAT([請求予定月],"yyyymm")=FORMAT(CDATE([締め日]),"yyyymm") And FORMAT([受注期間終了日],"yyyymm")>FORMAT(CDATE([締め日]),"yyyymm");

＊＊＊＊締め日を指定して更新するクエリの試験用ＳＱＬ＊＊＊＊＊
SELECT * FROM 受注
WHERE [受注期間開始日]<=CDATE([締め日]) And CDATE([締め日])<=[受注期間終了日] And FORMAT([請求予定月],"yyyymm")=FORMAT(CDATE([締め日]),"yyyymm") And FORMAT([受注期間終了日],"yyyymm")>FORMAT(CDATE([締め日]),"yyyymm");

上記の更新ＳＱＬ（選択ＳＱＬもですが）であれば、
締め日を実行日でなく、締め日を指定できます。
実行すると　締め日の入力を求められます。
そこで　2006/3/31（３月締め）　や　2006/4/30（４月締め）
などの締め日を入力すると
その月の締め日に指定した月で更新されますよ。

必ず末日又は翌月中に実行するなら、前のＳＱＬの方が入力がない分
簡便ですし、誤入力（締め日とは違う日付でも実行されちゃう）も防げます。

お礼

重ね重ねご丁寧に教えていただき、本当に
ありがとうございます！

教えていただきました、
「請求処理日を実行日の前月の末日とする」更新クエリを使用させていただきました。

これ、これです！！やりたかったことが（涙）
なんだかすきっとして気持ちいいいです（＾＾）

他のやりかたも教えていただき、感激しております。

何度もありがとうございました！！

回答No.6

process9

process9です。

＞日付を入力する画面が出てくるのですが、
＞これは、本日の日付を入力するものなのでしょうか？

すみません。検証してなかったもんで・・・・。
単なるSQLの記述ミスです。

各ＳＱＬのDATEの後ろに()が抜けてました（DATE()は本日の日付を返す関数です。）
あと、請求処理日を実行日の前月の末日として処理する更新クエリ
（４月以降に３月の請求処理をする場合のSQL）にも間違いがあったので訂正してます。

各更新ＳＱＬ毎に更新対象データの確認を行う試験用の選択クエリをつけてますので
確認してくださいね。
その選択内容であってなければ、更新対象データを決めるのに
条件が不足していること示してますので。。。。

なので、修正版ＳＱＬ
＊＊＊更新クエリ１＊＊＊＊
UPDATE 受注 SET 請求予定月 = DateAdd("m",1,[請求予定月])
WHERE [受注期間開始日]<=DATE()
And DATE()<=[受注期間終了日]
And FORMAT([請求予定月],"yyyymm")=FORMAT(DATE(),"yyyymm")
And FORMAT([受注期間終了日],"yyyymm")>FORMAT(DATE(),"yyyymm");

＊＊＊＊更新クエリで更新対象データを確認する試験用ＳＱＬ＊＊＊
SELECT *
FROM 受注
WHERE (((受注.受注期間開始日)<Date())
AND ((受注.受注期間終了日)>Date())
AND ((Format([請求予定月],"yyyymm"))=Format(Date(),"yyyymm"))
AND ((Format([受注期間終了日],"yyyymm"))>Format(Date(),"yyyymm")));

＊＊＊＊別（請求処理日を実行日の前月の末日として）更新クエリ＊＊＊
UPDATE 受注 SET 請求予定月 = DateAdd("m",1,[請求予定月])
WHERE [受注期間開始日]<=DateAdd("d",(CInt(FORMAT(DATE(),"dd")))*-1,DATE())
And DateAdd("d",(CInt(FORMAT(DATE(),"dd")))*-1,DATE())<=[受注期間終了日]
And FORMAT([請求予定月],"yyyymm")=FORMAT(DateAdd("m",-1,DATE()),"yyyymm")
And FORMAT([受注期間終了日],"yyyymm")>FORMAT(DateAdd("m",-1,DATE()),"yyyymm");

＊＊＊＊別（請求処理日を実行日の前月の末日として）更新クエリで更新対象データを確認する試験用ＳＱＬ＊＊＊
SELECT *
FROM 受注
WHERE (((受注.受注期間開始日)<=DateAdd("d",(CInt(Format(Date(),"dd")))*-1,Date()))
AND ((DateAdd("d",(CInt(Format(Date(),"dd")))*-1,Date()))<=[受注期間終了日])
AND ((Format([請求予定月],"yyyymm"))=Format(DateAdd("m",-1,Date()),"yyyymm"))
AND ((Format([受注期間終了日],"yyyymm"))>Format(DateAdd("m",-1,Date()),"yyyymm")));

回答No.5

process9

process9です。リクエストがあったので回答しますね。

問題を整理します。

受注期間、請求予定月には必ずデータがセットされているもの（受注登録時にセットされる）
という前提の上で、

更新対象データ
　受注期間開始日＜＝実行日＜＝受注期間終了日で且つ請求予定月＝実行日の月
　且つ受注終了日の月＞実行日の月の受注データ
　意味：実行日が受注期間内の受注データのうち
　　　　請求予定月に実行日の月と同じ月が指定されていて且つ
　　　　受注期間終了日の月が実行日の月翌月以降に指定されている
　　　　受注データ
　　　　（注：更新クエリを実行した日＝実行日）
更新内容
　更新対象データの請求予定月に実行日の月に＋１をセット。
　意味：上記の更新対象の受注データの請求予定月に翌月を上書きする。

データ例　（実行前＝請求処理前　実行日2006/03/31　のデータ状態）
　　受注番号　　　受注期間開始日　受注期間終了日　請求予定月
　　0001　　　　　2006/01/04　　　2006/02/27　　　2006/02
　　0002　　　　　2006/03/05　　　2006/05/05　　　2006/03
　　0003　　　　　2006/02/05　　　2006/03/20　　　2006/03
　　0004　　　　　2006/03/10　　　2006/04/20　　　2006/03
　　0005　　　　　2006/03/10　　　2006/05/20　　　2006/06
　　0006　　　　　2006/04/05　　　2006/05/05　　　2006/04

上記に従うと更新対象データは、以下の２レコード
　　0002、0004（翌月も請求の必要があるデータ）

　　以下は各理由のために更新対象とならない。
　　0001（既に終わってる受注）、0003（当月で請求が終了する受注）
　　0005（なんらかの理由で一括支払いのため、請求予定月が受注終了日の月に指定されている受注）
　　0006（まだ、始まっていない受注）

上記例のデータを更新内容に従って更新すると以下のようになる。
　　受注番号　　　受注期間開始日　受注期間終了日　請求予定月
　　0001　　　　　2006/01/04　　　2006/02/27　　　2006/02 （変化なし）
　　0002　　　　　2006/03/05　　　2006/05/05　　　2006/04 （請求予定月に翌月をセット）
　　0003　　　　　2006/02/05　　　2006/03/20　　　2006/03 （変化なし）
　　0004　　　　　2006/03/10　　　2006/04/20　　　2006/04 （請求予定月に翌月をセット）
　　0005　　　　　2006/03/10　　　2006/05/20　　　2006/06 （変化なし）
　　0006　　　　　2006/04/05　　　2006/05/05　　　2006/04 （変化なし）

と考えていいですか？ここが合ってない場合は以下は無視してください。

そうであれば、このときのクエリ（SQL）は、
UPDATE [受注]
SET [請求予定月]=DateAdd("m",1,[請求予定月])
WHERE
[受注期間開始日] <= DATE AND DATE <= [受注期間終了日]
AND FORMAT([請求予定月],"yyyymm") = FORMAT(DATE,"yyyymm")
AND FORMAT([受注期間終了日],"yyyymm") > FORMAT(DATE,"yyyymm")
になるかと思います。（検証してないので間違っている部分があるかも。）

試験として
SELECT * FROM [受注]
WHERE
[受注期間開始日] < DATE AND [受注期間終了日] > DATE
AND FORMAT([請求予定月],"yyyymm") = FORMAT(DATE,"yyyymm")
AND FORMAT([受注期間終了日],"yyyymm") > FORMAT(DATE,"yyyymm")
で思い通りの更新対象データが引っ張れているか試してくださいね。

ただし、これは、実行日が必ず請求月の末日に処理する前提です。
運用上、４月１日に３月の請求処理を行う場合には当てはまらないです。
そのときは別（請求処理日を実行日の前月の末日として）のＳＱＬを発行する。
UPDATE [受注]
SET [請求予定月]=DateAdd("m",1,[請求予定月])
WHERE
[受注期間開始日] <= DateAdd("m",(CInt(FOMAT(DATE,"dd"))+1)*-1,DATE) AND DateAdd("m",(CInt(FOMAT(DATE,"dd"))+1)*-1,DATE) <= [受注期間終了日]
AND FORMAT([請求予定月],"yyyymm") = FORMAT(DateAdd("m",-1,DATE),"yyyymm")
AND FORMAT([受注期間終了日],"yyyymm") > FORMAT(DateAdd("m",-1,DATE),"yyyymm")

どうでしょうか。

お礼

詳しい説明どうもありがとうございます！！！

まさにやりたいことを書いてくださって
いましたので、一行ずつ首をかしげながら
やっと理解できました！！
できました！！！

ただ、ひとつわからないところが
ありまして、更新クエリを実行する際、
日付を入力する画面が出てくるのですが、
これは、本日の日付を入力するものなのでしょうか？

何度もずうずうしくお聞きしてすみません。

これからまたじっくり見てみます。

よろしくお願いいたします。

回答No.4

mshr1962

#3のmshr1962です。すみません式を一箇所間違えました。
誤　フィールド欄「請求月:Format([請求予定月],"yyyymm")」抽出条件欄「<>Format(DateAdd("m",-1,Date),"yyyymm")」
正　フィールド欄「請求月:Format([請求予定月],"yyyymm")」抽出条件欄「Format(DateAdd("m",-1,Date),"yyyymm")」

<>があると請求予定月が先月でない場合になってしまいます。済みませんでした。

回答No.3

mshr1962

それだけだと条件が足りないのでは？
現状だと「[受注終了日]<Date」で「Format([請求予定月],"yyyymm")<>Foramt(Date,"yyyymm")」で「DateAdd("m",1,[請求予定月])」ということですが
受注終了日が2ヶ月以上前で請求済みの場合、請求予定月は更新してもいいのでしょうか？
また請求予定日が来月の場合、再来月になりますが．．．

「請求予定月が先月」かつ「受注終了日が今月以降」の場合なら
フィールド欄「請求月:Format([請求予定月],"yyyymm")」抽出条件欄「<>Format(DateAdd("m",-1,Date),"yyyymm")」
フィールド欄「終了月:Format([受注終了日],"yyyymm")」抽出条件欄「>=Format(Date,"yyyymm")」
フィールド欄「請求予定月」レコードの更新欄「DateAdd("m",1,[請求予定月])」

回答No.2

process9

process9です。

たぶん、できると思いますけど、
えっと、ちょっと不明確な部分があるので・・・
いくつか補足してもらえますか？

１．受注テーブルに受注期間開始日、受注期間終了日、請求予定月があるのですよね。各フィールドの型はなんでしょうか。

２．
＞請求書発行は、受注期間中の毎月月末です。
＞3/1-4/30の契約だと、3/31と4/30の2回発行することになります。現状は月末請求後に
＞手入力で請求月を入力していますが、更新クエリで自動入力することはできるでしょうか？？

受注テーブルに請求月があるのでしょうか？あれば、フィールド型はなんでしょうか。
あるとすれば、
＞「更新クエリ実行日が受注終了日よりも前ならば、請求予定月に１月足す」
＞かつ、「請求予定日が今月となっているときはそのままにする」
が意味がわからないです。更新対象フィールドが請求予定月になっています。（請求予定日は請求予定月の間違いですよね・・・たぶん）

お礼

ご回答ありがとうございました！

お礼が遅くなり申し訳ございません。
もしよろしければまた教えてください。

１．受注テーブルに受注期間開始日、受注期間終了日、請求予定月があるのですよね。各フィールドの型はなんでしょうか
＞フィールドの型は、日付/時刻型です。
ただ、請求予定月は、書式にyyyy\年m\月と指定しています。

２．受注テーブルに請求月があるのでしょうか？あれば、フィールド型はなんでしょうか。
＞すみませんでした。請求月フィールドは、請求予定月の間違いでした。また、請求予定日も請求予定月の
誤りです。

わかりずらくてすみません。。

よろしくお願いいたします。
よろしくお願いいたします。

回答No.1

O_cyan

請求予定月の日付のフィールドの持ち方にもよりますがyyyy/mm/dd(日付(S))の書式で入力はyyyy/mmで入力されている場合は2006/03と入力されていてもテーブル上は2006/03/01となっています。その辺が不明ですが・・。
下記で行けると思います。更新クエリ実行日を今日(Date())として

IIf([受注終了日]<Date(),IIf(Format([請求予定月],"yyyy/mm")=Format(Date(),"yyyy/mm"),[請求予定月],DateAdd("m",1,[請求予定月])))

一度テストで選択クエリを作りで試してみてください。
受注終了日と請求予定月のフィールドで
日付:IIf([受注終了日]<Date(),IIf(Format([請求予定月],"yyyy/mm")=Format(Date(),"yyyy/mm"),[請求予定月],DateAdd("m",1,[請求予定月])))
とでもして確認お願いします。
日付の表示がOKなら更新クエリの請求予定月のレコードの更新に先の記述で出来ます。

お礼

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

返事が遅くなりまことに申し訳ございません。
請求予定月は、データ型→日付/時刻型
で、書式に、yyyy\年m\月としております。

上記で教えていただいたものを更新クエリの
請求予定月・レコードの更新に記述しましたら
請求予定月に１月たすことができました。

が、全ての対象データに１月たしてしまったので、
更に記述する必要がありました。（＾＾；
何をしたいか、を明確にしてからもう一度
お教えください。

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