![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?8acaa2e)
Access2003を使用しています。
受注一覧DBを作成しています。
受注入力から請求書発行までを行いたいとおもっています。
テーブル:受注
クエリ:請求書発行検索
フォーム:受注入力,請求書発行検索
テーブル受注には、受注期間(開始日:2006/3/1~終了日:2006/4/30)
請求予定月2006年3月 というフィールドがあります。
請求書発行検索フォームでは、検索テキストボックスに 2006/3 と入力すると対象データがを
抽出するようにしています。
請求書発行は、受注期間中の毎月月末です。
3/1-4/30の契約だと、3/31と4/30の2回発行することになります。現状は月末請求後に
手入力で請求月を入力していますが、更新クエリで自動入力することはできるでしょうか??
「更新クエリ実行日が受注終了日よりも前ならば、請求予定月に1月足す」
かつ、「請求予定日が今月となっているときはそのままにする」
といったイメージです。。
いろいろと調べたのですが、書き方がわからなかったのです。
どうぞよろしくお願いいたします。
No.7ベストアンサー
- 回答日時:
process9です。
蛇足かもしれませんが、もう1つやり方があったので。。。
****締め日を指定して更新するクエリ*****
UPDATE 受注 SET 請求予定月 = DateAdd("m",1,[請求予定月])
WHERE [受注期間開始日]<=CDATE([締め日]) And CDATE([締め日])<=[受注期間終了日] And FORMAT([請求予定月],"yyyymm")=FORMAT(CDATE([締め日]),"yyyymm") And FORMAT([受注期間終了日],"yyyymm")>FORMAT(CDATE([締め日]),"yyyymm");
****締め日を指定して更新するクエリの試験用SQL*****
SELECT * FROM 受注
WHERE [受注期間開始日]<=CDATE([締め日]) And CDATE([締め日])<=[受注期間終了日] And FORMAT([請求予定月],"yyyymm")=FORMAT(CDATE([締め日]),"yyyymm") And FORMAT([受注期間終了日],"yyyymm")>FORMAT(CDATE([締め日]),"yyyymm");
上記の更新SQL(選択SQLもですが)であれば、
締め日を実行日でなく、締め日を指定できます。
実行すると 締め日の入力を求められます。
そこで 2006/3/31(3月締め) や 2006/4/30(4月締め)
などの締め日を入力すると
その月の締め日に指定した月で更新されますよ。
必ず末日又は翌月中に実行するなら、前のSQLの方が入力がない分
簡便ですし、誤入力(締め日とは違う日付でも実行されちゃう)も防げます。
重ね重ねご丁寧に教えていただき、本当に
ありがとうございます!
教えていただきました、
「請求処理日を実行日の前月の末日とする」更新クエリを使用させていただきました。
これ、これです!!やりたかったことが(涙)
なんだかすきっとして気持ちいいいです(^^)
他のやりかたも教えていただき、感激しております。
何度もありがとうございました!!
No.6
- 回答日時:
process9です。
>日付を入力する画面が出てくるのですが、
>これは、本日の日付を入力するものなのでしょうか?
すみません。検証してなかったもんで・・・・。
単なるSQLの記述ミスです。
各SQLのDATEの後ろに()が抜けてました(DATE()は本日の日付を返す関数です。)
あと、請求処理日を実行日の前月の末日として処理する更新クエリ
(4月以降に3月の請求処理をする場合のSQL)にも間違いがあったので訂正してます。
各更新SQL毎に更新対象データの確認を行う試験用の選択クエリをつけてますので
確認してくださいね。
その選択内容であってなければ、更新対象データを決めるのに
条件が不足していること示してますので。。。。
なので、修正版SQL
***更新クエリ1****
UPDATE 受注 SET 請求予定月 = DateAdd("m",1,[請求予定月])
WHERE [受注期間開始日]<=DATE()
And DATE()<=[受注期間終了日]
And FORMAT([請求予定月],"yyyymm")=FORMAT(DATE(),"yyyymm")
And FORMAT([受注期間終了日],"yyyymm")>FORMAT(DATE(),"yyyymm");
****更新クエリで更新対象データを確認する試験用SQL***
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");
****別(請求処理日を実行日の前月の末日として)更新クエリで更新対象データを確認する試験用SQL***
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です。
リクエストがあったので回答しますね。問題を整理します。
受注期間、請求予定月には必ずデータがセットされているもの(受注登録時にセットされる)
という前提の上で、
更新対象データ
受注期間開始日<=実行日<=受注期間終了日で且つ請求予定月=実行日の月
且つ受注終了日の月>実行日の月の受注データ
意味:実行日が受注期間内の受注データのうち
請求予定月に実行日の月と同じ月が指定されていて且つ
受注期間終了日の月が実行日の月翌月以降に指定されている
受注データ
(注:更新クエリを実行した日=実行日)
更新内容
更新対象データの請求予定月に実行日の月に+1をセット。
意味:上記の更新対象の受注データの請求予定月に翌月を上書きする。
データ例 (実行前=請求処理前 実行日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
上記に従うと更新対象データは、以下の2レコード
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")
で思い通りの更新対象データが引っ張れているか試してくださいね。
ただし、これは、実行日が必ず請求月の末日に処理する前提です。
運用上、4月1日に3月の請求処理を行う場合には当てはまらないです。
そのときは別(請求処理日を実行日の前月の末日として)のSQLを発行する。
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
- 回答日時:
#3のmshr1962です。
すみません式を一箇所間違えました。誤 フィールド欄「請求月:Format([請求予定月],"yyyymm")」抽出条件欄「<>Format(DateAdd("m",-1,Date),"yyyymm")」
正 フィールド欄「請求月:Format([請求予定月],"yyyymm")」抽出条件欄「Format(DateAdd("m",-1,Date),"yyyymm")」
<>があると請求予定月が先月でない場合になってしまいます。済みませんでした。
No.3
- 回答日時:
それだけだと条件が足りないのでは?
現状だと「[受注終了日]<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です。
たぶん、できると思いますけど、
えっと、ちょっと不明確な部分があるので・・・
いくつか補足してもらえますか?
1.受注テーブルに受注期間開始日、受注期間終了日、請求予定月があるのですよね。各フィールドの型はなんでしょうか。
2.
>請求書発行は、受注期間中の毎月月末です。
>3/1-4/30の契約だと、3/31と4/30の2回発行することになります。現状は月末請求後に
>手入力で請求月を入力していますが、更新クエリで自動入力することはできるでしょうか??
受注テーブルに請求月があるのでしょうか?あれば、フィールド型はなんでしょうか。
あるとすれば、
>「更新クエリ実行日が受注終了日よりも前ならば、請求予定月に1月足す」
>かつ、「請求予定日が今月となっているときはそのままにする」
が意味がわからないです。更新対象フィールドが請求予定月になっています。(請求予定日は請求予定月の間違いですよね・・・たぶん)
ご回答ありがとうございました!
お礼が遅くなり申し訳ございません。
もしよろしければまた教えてください。
1.受注テーブルに受注期間開始日、受注期間終了日、請求予定月があるのですよね。各フィールドの型はなんでしょうか
>フィールドの型は、日付/時刻型です。
ただ、請求予定月は、書式にyyyy\年m\月と指定しています。
2.受注テーブルに請求月があるのでしょうか?あれば、フィールド型はなんでしょうか。
>すみませんでした。請求月フィールドは、請求予定月の間違いでした。また、請求予定日も請求予定月の
誤りです。
わかりずらくてすみません。。
よろしくお願いいたします。
よろしくお願いいたします。
No.1
- 回答日時:
請求予定月の日付のフィールドの持ち方にもよりますが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\月としております。
上記で教えていただいたものを更新クエリの
請求予定月・レコードの更新に記述しましたら
請求予定月に1月たすことができました。
が、全ての対象データに1月たしてしまったので、
更に記述する必要がありました。(^^;
何をしたいか、を明確にしてからもう一度
お教えください。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(データベース) accessでのフォームの連携について 2 2022/06/14 00:45
- Access(アクセス) access レポート 請求書について 2 2022/07/04 22:52
- その他(データベース) accessでの請求管理について 2 2022/06/13 21:51
- Visual Basic(VBA) Excel VBA ユーザーフォーム1のコンボボックスに別ブックの値を反映させたいです。 6 2023/03/21 16:12
- Visual Basic(VBA) VBA 請求書自動作成 3 2022/04/24 01:58
- Excel(エクセル) PDFファイルに日付を名前にして保存したい。 エクセル2019でワークシートに請求書のフォームを作り 2 2023/05/27 11:13
- 財務・会計・経理 業者間での注文書・請書の取り扱いについて 2 2022/06/27 15:53
- その他(税金) 適確請求書発行者登録番号を登録するか悩んでます。 今UberEATSの配達員をやっています。 10月 2 2023/06/06 19:09
- 離婚・親族 法的な離婚日(日割りで請求するものがある場合) は次のうちどちらになりますか。 裁判離婚が8月1日に 1 2022/08/25 17:58
- その他(ビジネススキル・経営ノウハウ) 建設特定技能受入申請書類について 回答お願いいたします。 12月頃特定技能で1名入国します。 申請書 1 2022/07/15 06:25
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
作番ってどういう意味でしょうか?
-
「直需」の意味を教えてください
-
access2003初心者です。データ...
-
「失注」の意味を教えてくださ...
-
お弁当の宅配業者向け業務アプ...
-
Excel 2019 のピボットテーブル...
-
エクセルVBAで5行目からオート...
-
Accessでテーブル名やクエリ名...
-
Oracle 2つのDate型の値の差を...
-
セルの右クリックで出る項目を...
-
ACCESSで和暦を西暦に・・・
-
Accessでテーブルの値をテキス...
-
Accessのフィールド数が255しか...
-
Excel→Accessへ貼り付けがおかしい
-
Accessで数値型にNULLをInsert...
-
3つの表を1つに縦に連結する
-
ACCESSのクエリで集計で、先頭...
-
ORACLEで一番最初の結果だけを...
-
Oracleのビュー作成時に「指定...
-
Accessレポートで特定条件で改...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
「直需」の意味を教えてください
-
作番ってどういう意味でしょうか?
-
「失注」の意味を教えてくださ...
-
ACCESSで、DMax関数の条件の書き方
-
Access:サブフォームのレコー...
-
ACCESS 選択した値を別...
-
アクセスで営業日を計算させる...
-
アクセスのクエリで、前回に入...
-
マクロ転記で指定した列の最終...
-
受注受付期間とはなんですか?...
-
Accessの受注発注について
-
フォーム入力、更新後処理で指...
-
会社 ファイルの収納
-
データシート形式のサブフォー...
-
更新クエリの日付抽出方法について
-
通販サイトの運用の問題点は?
-
EXCEL 最終行に入力するマクロ
-
VBA でvlookup エラーなどは削...
-
エクセル・スプレッドシートで...
-
Excelで在庫管理表を作ってます...
おすすめ情報