取引先の消費税の計算方法を四捨五入と切り捨てに区別

MicrsoftAccess2000「見積請求納品システム」を使用しています。
取引先の消費税の計算方法を四捨五入と切り捨てに区別したいです。
以前にも同じ質問をしたのですが、難しい為諦めました・・・。
ですが、やはりシステムの変更が必要なので教えてください！

Module1に以下の式があります。
Public Function funcSG(xCy As Currency) As Currency
If xCy > 0 Then
xCy = Int(xCy + 0.55)
Else
xCy = Int(xCy - 0.55)
End If
funcSG = xCy
End Function

請求書コマンドマクロは以下のようになっています。
マクロ名　計算
アクション　値の代入

アイテム　[Forms]![請新フォーム]![消費税額]
式　funcSG([Forms]![請新フォーム]![税抜金額]*0.05)

アイテム　[Forms]![請修フォーム]![消費税額]
式　funcSG([Forms]![請修フォーム]![税抜金額]*0.05)

今は全て四捨五入されるようになっていると思うので、切り捨てもできるようにしたいです。
得意先テーブルに消費税区分を設けて区別したいです！
宜しくお願い致します！

No.11 回答者： piroin654 回答日時： 2010/04/20 08:30

ボタンクリックの機能はしているようなのですが、

テーブルの指定、得意先コード、請求先コード
のコンビネーションが取れていないようなので
それを解消すればと思います。もう、ちょいと
のところまで来ているのですが。

えーっと、請新フォームと請修フォームの違いは
よくわからないのですが、請新フォームと請修フォーム
のレコードソースはそれぞれ違うようで、少し
教えてください。

(1)
請新フォームのレコードソース(テーブル名ORクエリのそれぞれの内容)
と、そのレコードソースに税区分のフィールドの有無。
請新フォームにあるのは得意先IDまたは請求先ID？
請新フォームの役割は？


(2)
同じく、請修フォームについて。


(3)
ボタンを請新フォームに取り付けコードを貼り付け、
税計算_Click()のコードの一部を修正するとき、

>Set rs = db.OpenRecordset("T得意先", dbOpenDynaset)

>If IsNull([Forms]![請修フォーム]![得意先ID]) Then
>MsgBox ("得意先IDが入力されていません")

>rs.FindFirst "得意先ID=" & [Forms]![請修フォーム]![得意先ID]

などの、T得意先、得意先ID、請修フォームは、得意先テーブル、得意先コード
請新フォームなどに変更しますが、そのとき請新フォームには得意先コード
が表示されていればOKです。そして、得意先テーブルに税区分フィールドが
あれば機能するはずです。


(4)
ボタンを請修フォームに取り付けコードを貼り付け、
税計算_Click()のコードの一部を修正するとき、

>Set rs = db.OpenRecordset("T得意先", dbOpenDynaset)

>If IsNull([Forms]![請修フォーム]![得意先ID]) Then
>MsgBox ("得意先IDが入力されていません")

>rs.FindFirst "得意先ID=" & [Forms]![請修フォーム]![得意先ID]

などの、T得意先、得意先IDは得意先テーブル、得意先コードにすれば
いいのですが、もし請修フォームのレコードソースが得意先テーブル
ではなく、請求先テーブル(あるいは別名)になっていて請修フォーム
には得意先コードではなく請求先コードがあるのならばフォームを変更
するのは大変なので、請求先テーブルに税区分フィールドを作り、同じように
四捨五入と切捨てを入れると同じように機能します。
その場合は、コードはT得意先が請求先テーブル、得意先IDは請求先コードに
変更します。この状況はあくまで推測なのでフォームのレコードソース
、フォームのテキストボックスなどの状況により変更することになります。

得意先テーブルと請求先テーブル(あるいは別名)の関係とそれぞれの
フィールド(特に得意先コード、請求先コードがそれぞれのテーブルに
どのように配置されているか)が判ればフォームを手直しするか、
あるいはテーブルに税区分を追加するかで対応ができます。

将来を見据えれば、あまり小手先の変更は禍根を残すので、
そのあたりはよく考えます。

それと、
(5)
四捨五入、切捨てのデータはどのフォーム、
で出すのがいいのでしょうか。

請新フォーム、請修フォームそれぞれで必要？

あるいはどちらかで四捨五入、切捨ての
データが取得できればいい？

この回答へのお礼

piroin654さんありがとうございます！
以下のことを確認しましたのでご協力お願いします。

（1）請新フォーム
　　 レコードソース　請求書明細クエリー 税区分フィールドを追加しました。
請求先コードがあります。
役割　請求書新規作成
（2）請修フォーム
　　 レコードソース　請求書指定明細クエリー 税区分フィールドを追加しました。
請求先コードがあります。
役割　請求書修正
（3）T得意先→得意先名テーブル
得意先ID→請求先コード
　　 請新フォームに得意先コードは表示されていません。
　　 得意先テーブルには税区分フィールドがあります。
（4）請求先コードがある請求明細テーブルの主キーが請求書番号なので税区分フィールドを作っても
会社ごとに区別することができません。

請求書明細クエリーと請求書指定明細クエリーの中身は（振込先名テーブル、
得意先名テーブル、請求明細テーブル、担当者名テーブル、請求内容テーブル）

得意先コードは得意先名テーブルの中にあります。
請求先コードは請求明細テーブルの中にあります。

（5）データはそれぞれのフォームで必要です。

初心者なので請求先コードをどうして作ったのかわかりません。
宜しくお願いします！！！！

お礼日時：2010/04/20 16:21

No.12 ベストアンサー 回答者： 30246kiku 回答日時： 2010/04/21 13:33

＃１０です

以下２つの関数を標準モジュールに用意したとします。

Public Function TaxInterPre(iNum As Variant, cVal As Variant) As Currency
　　On Error Resume Next

　　TaxInterPre = 0
　　If (IsNull(iNum) Or IsNull(cVal)) Then Exit Function

　　Select Case iNum
　　　　Case 1
　　　　　　　　TaxInterPre = Int(cVal)
　　　　Case 2
　　　　　　　　TaxInterPre = funcSG(cVal)
　　End Select
End Function

Public Function funcSG(ByVal xCy As Currency) As Currency
　　If xCy > 0 Then
　　　　xCy = Int(xCy + 0.5)
　　Else
　　　　xCy = Int(xCy - 0.5)
　　End If
　　funcSG = xCy
End Function


使い方は、式のところに、TaxInterPre(消費税区分, 消費税額) を記述します。

TaxInterPre(Null, 1234.56)　や　TaxInterPre(1, Null) の時には、0 が返ります。
TaxInterPre(1, 1234.56) の場合は、1234 が返ります。
TaxInterPre(2, 1234.56) の場合は、1235 が返ります。

さて、ここからですが、
TaxInterPre(消費税区分, 消費税額)　を利用する時に、
何を参照して何を使って利用できるか・・・を考えていきます。

【消費税区分】
・フォームに参照できるものがあったとき
　それを参照するようにします。
　　例えば、フォーム「F_親」に「税区分」名のテキストボックスがあったら、
　　TaxInterPre([Forms]![F_親]![税区分], 消費税額)

・フォームに参照するものが無く、得意先名しかわからないとき
　その得意先名の税区分を DLookup 等で求めます。
　消費税率の様なテーブル構成ではなく、単に得意先名テーブルに項目を追加しただけであれば、
　　DLookup("税区分","得意先名テーブル","得意先名='" & [Forms]![F_親]![得意先名] & "'")
　　この DLookup の記述を ※１　とした場合、
　　TaxInterPre(※１, 消費税額)

　　フォーム上に参照できるものがあれば、・・・・と思います。

【消費税額】
・参照されるものがあるようですので、それを記述します。
　TaxInterPre(消費税区分, [Forms]![請新フォーム]![税抜金額]*0.05)　

　0.05 即値を使った例ですが。


後は、状況に応じて、何を参照して、何を使って・・・・を考えられればと思います。

関数名は適宜変更してください。

この回答へのお礼

30246kikuさんありがとうございます！

得意先名テーブルに消費税区分フィールドを作りデータ型は数値型（整数型）にしました。
その後以下のようにモジュールを作りました。

Public Function TaxInterPre(DLookup("消費税区分","得意先名テーブル","得意先名='" & [Forms]![請修フォーム]![得意先名] & "'") As Variant, [Forms]![請修フォーム]![税抜金額]*0.05 As Variant) As Currency
On Error Resume Next

TaxInterPre = 0
If (IsNull(DLookup("消費税区分", "得意先名テーブル", "得意先名='" & [Forms]![請修フォーム]![得意先名] & "'")) Or IsNull([Forms]![請修フォーム]![税抜金額] * 0.05)) Then Exit Function

Select Case DLookup("消費税区分", "得意先名テーブル", "得意先名='" & [Forms]![請修フォーム]![得意先名] & "'")
Case 1
TaxInterPre = Int([Forms]![請修フォーム]![税抜金額] * 0.05)
Case 2
TaxInterPre = funcSG([Forms]![請修フォーム]![税抜金額] * 0.05)
End Select
End Function

Public Function TaxInterPre(DLookup("消費税区分","得意先名テーブル","得意先名='" & [Forms]![請新フォーム]![得意先名] & "'") As Variant, [Forms]![請新フォーム]![税抜金額]*0.05 As Variant) As Currency
On Error Resume Next

TaxInterPre = 0
If (IsNull(DLookup("消費税区分", "得意先名テーブル", "得意先名='" & [Forms]![請新フォーム]![得意先名] & "'")) Or IsNull([Forms]![請新フォーム]![税抜金額] * 0.05)) Then Exit Function

Select Case DLookup("消費税区分", "得意先名テーブル", "得意先名='" & [Forms]![請新フォーム]![得意先名] & "'")
Case 1
TaxInterPre = Int([Forms]![請新フォーム]![税抜金額] * 0.05)
Case 2
TaxInterPre = funcSG([Forms]![請新フォーム]![税抜金額] * 0.05)
End Select
End Function

Public Function funcSG(ByVal xCy As Currency) As Currency
If xCy > 0 Then
xCy = Int(xCy + 0.5)
Else
xCy = Int(xCy - 0.5)
End If
funcSG = xCy
End Function

そうすると
Public Function TaxInterPre(DLookup("消費税区分","得意先名テーブル","得意先名='" & [Forms]![請修フォーム]![得意先名] & "'") As Variant, [Forms]![請修フォーム]![税抜金額]*0.05 As Variant) As Currency
と
Public Function TaxInterPre(DLookup("消費税区分","得意先名テーブル","得意先名='" & [Forms]![請新フォーム]![得意先名] & "'") As Variant, [Forms]![請新フォーム]![税抜金額]*0.05 As Variant) As Currency

の部分が赤くなり両方とも

"消費税区分"

の部分が反転し、

コンパイルエラー：
修正候補：）

となります。請修・請新フォームに消費税区分テキストボックスを追加できずに上記の方法にしました！
宜しくお願いします！

お礼日時：2010/04/22 10:13

No.13 回答者： piroin654 回答日時： 2010/04/23 18:15

遅れてすみません。

補足ありがとうございます。提案したプログラムは
あくまでも得意先テーブルを参照する構造になって
いるのでフォームに税区分のフィールドを追加する
必要はありません。フォームに必要なのは得意先
テーブルに登録されている得意先コードです。
この得意先コードをたよりに得意先テーブルを
検索する構造になっています。税区分のデータは
得意先テーブルに登録されているからです。


※得意先と請求先の区別について
販売管理の現場ではときには得意先から注文を
受け取り、請求先は得意先ではなく別の請求先
にしてくれ、ということはあります。たとえば、
支社から注文を受け取り、請求は本社へ、その
場合、受注確認は支社名と本社名２通、請求書は
本社名で、というようなことはあります。一概に
得意先と請求先を区別したということは間違いでは
ありません。

このような場合は既存の得意先テーブルに得意先と
同じレコードに請求先を登録し、請求先は同じ
得意先テーブルに登録し、得意先と請求先が
同じ場合は請求先コードのフィールドに同じ
得意先コードを登録する。

あるいは同じレコードに別請求先というフィールドを作り、
有り、無しを登録し、有りならば別請求先テーブルに
得意先コードに対し複数の請求先が存在するならば
そのように登録するテーブル設計にし、運用する
というような方法もあります。

ただ、必ず得意先コードと請求先コードが関連付け
られるようにしておきます。

いずれにしろ得意先と請求先を作ったことは
いいのですが、今回の場合はそのことを残して
改良するか、あるいは請求先コードを
得意先コードに一元化するかのどちらかです。


いただいた補足をもとにいろいろシュミレーションを
してみました。シュミレーションの内容をすべて
書くと混乱するので、まず一つだけします。
その前に、以下を確認をさせてください。
(1)
請求先コードは請求テーブルのみにあるのでしょうか。

(2)
得意先コードと請求先コードが一緒に書かれている
テーブルはありますか。たとえば請求明細をつくる
とき、請求明細の請求先は得意先コードから
得意先名を割り出し、それを請求先名にしているのか。
あるいは名前そのものを比べているのか。

(3)
(2)がない場合、請求先と得意先を結びつける
のはどのようにしておられますか。
たとえば、請求先コードと得意先コードを何処で
結び付けてるのか。
たとえば請求明細をつくるとき、請求明細の
請求先は得意先コードから得意先名を割り出し、
それを請求先名にしているのか。
あるいは名前そのものを比べているのか。
請求先コードから得意先コードをたどる方法は
ありますか。

(4)
請求明細は納品明細を兼ねているのでしょうか。
請求明細は一時的にデータを格納するものですか。


前回の質問と重複すかもしれませんが、
以上を確認させてください。

この回答への補足

piroin654さんありがとうございます！
(1)
請求先コードは請求明細テーブルのみにあります。

(2)
得意先コードと請求先コードが一緒に書かれているテーブルはありません。
請新（請求書新規作成）フォームと請修（請求書修正）フォームで
操作する時は得意先名テーブルから選び請求明細テーブルにレコードが保存されます。
以下のようになっていました。

コントロールソース：請求先コード
値集合ソース：得意先名テーブル

(3)
(2)の答えで大丈夫ですか？

(4)
納品明細テーブルは他にあります。
請求明細はデータを格納していますが、一時的にとはどうゆうことでしょうか
（＞＜）

的を得た答えでなかったら申し訳ありません。
宜しくお願いします！

補足日時：2010/05/12 16:24

No.14 回答者： 30246kiku 回答日時： 2010/04/23 21:28

piroin654さん　お疲れ様です。

今現在(4/23 21:25)、教えて!goo では回答13を見ることはできません。
OKWave からだと、18:15:14 の投稿（回答）があったとわかります。

質問者さんは、コピペで動くものを欲していると思われ・・・

消費税率のテーブルはどうなった。
消費税区分情報を、
どこに、
どう持った
か・・・を聞き出さないことには先に進まないような気がします。

フォームの構成についてもしかりです。


※
提案の階層こそ違いますが、私も勉強させてもらってます。
ただ、途中で Exit するのなら、以前で OpenRecordset は意味無いような気がします。

No.15 回答者： piroin654 回答日時： 2010/04/24 22:05

【ekusenさんへ】

この場をお借りして30246kikuさんと相談させてください。


【30246kikuさんへ】
(1)
消費税率テーブルを作成。
フィールドは税率だけで数値型。
(2)
消費税区分は得意先テーブルのみに設定し、
請求明細テーブルには必要ない。
(3)
得意先IDを各フォームに表示、または
各フォームで取得できるようにする。

ということができれば視界良好ということ
ですね。


流れを整理しておかないといけなかったのですが。
請新フォームと請修フォームはサブフォームを
もっているのかあるいは帳票形式なのかによって
提案するプログラムが変わるかもしれません。
ついつい聞きそびれていました。
それで取り合えずボタンクリックでテキスト
ボックスに消費税を出すようにしておこうと、
考えていました。
それがうまくいけば、ボタンクリックを消費税を
返す関数に変更すれば自動計算ができ、ボタン
その他いらないものは削除して、関数のみを
残すというシナリオです。

見られたらおわかりのようにボタンクリックを
関数に変更した場合必要な引数は得意先IDと
各フォームでの税抜金額だけです。
税率は30246kikuさんが言われるよう税率テーブル
で管理し税率変更時にはテーブルの数値を変更
するだけにするというのがベストで、変更時に
プログラムをいじらなくてすみます。
30246kikuさんが言われるようコピペですむように
しようとした次第です。


その前提となる得意先テーブルから得意先コードを
各フォームに手入力ではなく自動的に表示する
ことを模索していたところです。
たぶん、30246kikuさんが考えていた方法もこれがクリア
できれば特に複雑な模索をしなくともそのまま
出来たのではと思います。ですから何が何でも
得意先IDがほしい、のです。

コード先頭のExitの部分ははこのような次第で
最初は得意先IDをテキストボックスに手入力で行い、
データが表示されることを確認してもらおうと
思っていたこと、最悪手入力しか方法がないの
ならということの両方の理由でExitをおいておきました。
得意先IDが取得できるようになれば当然必要ありません。

それからOpenRecordsetの位置に関しては
判りやすい位置にということで最初に宣言
していますが、30246kikuさんが
言われる通り順序から言えばExitの後が自然です。
いきなりExit Subをしてオブジェクトを削除して
いませんからね。

直接、得意先IDが取得できなくとも請求先IDから
取れればということでekusenさんに質問をした
ところです。どちらかというと請求先IDも
得意先IDに統一したほうが混乱がなくなり
いいのですが。フォームのレコードソースから
取れると思うのですが。

以上をいろいろな中で説明すると大変混乱するので
後回しにしていました。

ということで、わたしの拙コードの改変、改造も
含めて何か案があれば是非提案してください。
説明がなんとなくばらばらですがよろしくお願い
します。

それから、超速、かつ、すべて、今だ、旧形式は
以下です。頭にhttpとコロンと//をくっつけてください。
hiroba.hoiku-plus.jp/list.php3
変更レスポンスは即です。専用を開いておいて
確認に使用できます。いつまで同じかは
わかりませんが。

No.16 回答者： 30246kiku 回答日時： 2010/04/25 14:28

＃１４です（長くなったので２つに分けます）

本日 7:30 に piroin654 さんが回答したメールが届いていたので、
メール本文内のリンクから記述を見ようとしましたが見れませんでした。
今だと見えるみたいです。

プロの方からみればおかしなところがあると思います。
我流の私の中でのストーリーは以下です（一気に書きます）。


＃１２に提示した TaxInterPre を使うものとします。
（何故そのフォーム/限定した場面に限定したものを作ろうとするのか？ですが）
（限定するのであれば引数は不要と思います）
（funcSG は使えていたので、TaxInterPre も使えるかと思っていましたが・・・）

「T消費税」テーブルがあるとします。（内容が正しいかは別にして）

適用日　　　　税率
1989/04/01　　3.00%
1997/04/01　　5.00%

税率部分は、通貨型（書式：パーセント）

日付を元に税率を求める関数 GetTax を用意します。

Public Function GetTax(dt As Variant) As Currency
　　Dim rs As New ADODB.Recordset

　　On Error Resume Next
　　GetTax = 0
　　If (Not IsDate(dt)) Then Exit Function

　　rs.Source = "SELECT TOP 1 税率 FROM T消費税 WHERE 適用日 <= #" & dt & "# ORDER BY 適用日 DESC;"
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
　　If (Not rs.EOF) Then GetTax = rs("税率")
　　rs.Close
End Function

「T得意先」テーブルが、履歴を管理しない税区分の持ち方と仮定します。

得意先ID　　得意先名　　税区分
1　　　　　　AAAA　　　　　1
2　　　　　　BBBB　　　　　2
3　　　　　　CCCC　　　　　1


売上を管理するテーブル「T売上」が以下のようになっていたとします。
説明上、商品名等は省略

得意先ID　　日付　　　単価　　数量
1　　　　1989/03/31　　?100　　100
2　　　　1989/04/01　　?100　　100
2　　　　1997/04/01　　?100　　100
1　　　　1997/03/31　　?100　　100

で、このデータを表示するフォームを考えます。（単票でも/帳票でも）
「合計」「消費税」のテキストボックスも追加表示したいとします。
税率用に不可視のテキストボックス「txtax」を配置します。
（それぞれ詳細部分に配置します）

「合計」のコントロールソースに、 =[単価]*[数量]
「txtax」のコントロールソースに、 =GetTax([日付])

「消費税」のコントロールソースに、
単純に計算するだけなら、 =[合計]*[txtax]

得意先別に処理を切り分けるため、工夫します。
不可視のコンボボックス（もしくはリストボックス）「cbx1」を配置します。
（これも詳細部分に配置します）
「cbx1」のコントロールソースを、得意先ID にしておき、
値集合ソースを　　SELECT 得意先ID, 得意先名, 税区分 FROM T得意先;
列数：　３
連結列：　１

としておきます。
得意先ごとの税区分は、　cbx1.column(2) で得ることができるので、
「消費税」のコントロールソースは、
　=TaxInterPre([cbx1].[column](2),[合計]*[txtax])

また、得意先名を表示したいのなら、コントロールソースに、
　=[cbx1].[column](1)
とすればよいと思います。

DLookup では基本で１項目しか得られないので、
複数の場合（今回の例では、得意先名、税区分）
私はコンボボックスを使用しています（不可視/可視は状況に応じて）

税区分だけを得られればいい、のなら以前に示したように　DLookup を使用し、
　=TaxInterPre(DLookup("税区分","T得意先","得意先ID=" & [得意先ID]),[合計]*[txtax])
この場合、コンボボックスは必要ありません。

【つづく】

この回答への補足

30246kikuさんありがとうございます！

＃１２の TaxInterPre をコピーして使いました。

「T消費税」テーブルを以下のように作成しました。

ID　適用日　　　　税率
1　1989/04/01　　3.00%
2　1997/04/01　　5.00%

GetTax をモジュールに作成しました。

「得意先名テーブル」に税区分フィールドを追加し、
切捨ての場合は１、四捨五入の場合は2を入力しました。

売上を管理するテーブル「請求明細テーブル」には
以下のフィールドが含まれています。

請求先コード　　日付　　税抜金額　消費税額
0201　　　1989/03/31　　100　　0
0040　　　1989/04/01　　100　　3
0040　　　1997/04/01　　100　　5
0610　　　1997/03/31　　100　　5

このデータを表示する「請修フォーム」があります。
「税抜金額」は既に合計された金額です。
詳細は請求内容テーブルに保存されていきます。

「txtax」も作成しました。
コントロールソース、 =GetTax([日付])

「税抜金額」のコントロールソースは税抜金額で
以下のイベントプロシージャとマクロがありました！

Private Sub 税抜金額_BeforeUpdate(Cancel As Integer)
End Sub

アイテム　[Forms]![請修フォーム]![税抜金額]
式　[１５]+[６１０]
（[１５]+[６１０]が何なのか探してみましたがまだわかりません・・・）

コンボボックス「cbx1」を以下のように作成しました。
「cbx1」のコントロールソースを、請求先コード
値集合ソースを　　SELECT 得意先コード, 得意先名, 税区分 FROM 得意先名テーブル;
列数：　３
連結列：　１

「消費税」のコントロールソースはコピーしました。

この状態で請修フォームを開くと
Public Function GetTax(dt As Variant) As Currencyが黄色く塗りつぶされ
rs As New ADODB.Recordsetが青く反転し、
以下のエラーになります。

コンパイルエラー：
ユーザー定義型は定義されていません。

勉強していきますので宜しくお願いします！

補足日時：2010/05/13 09:57

No.17 回答者： 30246kiku 回答日時： 2010/04/25 14:31

【つづき】

また、フォームの構成において、親子になっていた時、
親側が「T得意先」、子側が「T売上」、リンク親子フィールドが「得意先ID」とすると、
子側の「消費税」部分は、
　=TaxInterPre([parent].[税区分],[合計]*[txtax])
になると思います。

クエリで、得意先IDで INNER JOIN / LEFT JOIN して得意先名を得ているのなら、
税区分も得るようにしておいても・・・。
税区分を追加したクエリをフォームのレコードソースに使用しているのなら、
特にフォームに税区分用のコントロールを配置しなくても、[税区分] で参照できるので、
=TaxInterPre([税区分],[合計]*[txtax])

「合計」「消費税」をフォーム上だけで追加してみましたが、
クエリ内で求めて、表示しても良いと思います。

また、他のクエリにおいても、得意先IDで INNER JOIN / LEFT JOIN して・・・
で、TaxInterPre([xxxx].[税区分],YYYY) AS ZZZZ　とか記述できると思います。
この時、TaxInterPre　内部で Recordset を得て・・・・していると、
それだけでも遅くなるような気がします。

本当に　Recordset を得ないと・・・の場合には、
現状の TaxInterPre に皮をかぶせた形で関数を定義するものかと思います。


税区分を消費税のように履歴を持てる形（「T税区分」テーブル新設）とすると、
（an はオートナンバ）

an　　　得意先ID　　適用日　　　税区分
1　　　　2　　　　　2010/04/01　　2

Public Function GetTaxDiv(id As Variant, dt As Variant) As Long
　　Dim rs As New ADODB.Recordset

　　On Error Resume Next
　　GetTaxDiv = 1
　　If ((IsNull(id)) Or (Not IsDate(dt))) Then Exit Function

　　rs.Source = "SELECT TOP 1 税区分 FROM T税区分 WHERE 得意先ID = " & id _
　　　　　　　　　　& " AND 適用日 <= #" & dt & "# ORDER BY 適用日 DESC;"
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
　　If (Not rs.EOF) Then GetTaxDiv = rs("税区分")
　　rs.Close
End Function

これだと、GetTaxDiv([得意先ID],[日付])　でその時の税区分が得られます。


TaxInterPre と同じ戻り値を返す関数を数例

これらに皮をかぶせて、
TaxInterPre2([得意先ID],[日付],[合計]*[txtax]) の様なものを作ると

Public Function TaxInterPre2(id As Variant, dt As Variant, cVal As Variant) As Currency
　　On Error Resume Next
　　TaxInterPre2 = TaxInterPre(GetTaxDiv(id, dt), cVal)
End Function

また、TaxInterPre3([得意先ID],[日付],[合計]) の様なものを作ると

Public Function TaxInterPre3(id As Variant, dt As Variant, cVal As Variant) As Currency
　　On Error Resume Next
　　TaxInterPre3 = TaxInterPre(GetTaxDiv(id, dt), cVal * GetTax(dt))
End Function


なので、TaxInterPre は低階層部分に使うものと考えます。
クエリからでは直接でも・・・（直接の方が多いのかも）


今回提示したテーブルサンプルが質問者の環境に合致していることはないでしょうから、
小出しにしていた部分はありますが・・・。

回答欄に、「アドバイス」が無くなったのですね。
強制する気は毛頭ありません。


関数名は適宜変更してください。


おかしなところがありましたら、ご指摘いただければと思います。
（消費税額合計など機能追加では、また工夫が必要かと・・・）

この回答への補足

30246kikuさんありがとうございます！

請修フォームのレコードソースは請求書指定明細クエリーで、
「得意先テーブル」の「得意先コード」が1、
「請求明細テーブル」の「請求先コード」が∞
で繋がっているので「消費税額」のコントロールソースはどうなるのでしょうか・・・

他の部分は能力不足で難しいので、
ゆっくり勉強していこうと思います。

回答欄の、「アドバイス」は私が消してしまったのでしょうか？

宜しくお願いします！

補足日時：2010/05/13 10:33

No.18 回答者： 30246kiku 回答日時： 2010/04/30 23:14

＃１６，１７の回答投稿メールが昨日（4/29 19:30）に私に届きました。

（4/29 15:30 ごろに送信されたもののようですが）
使い勝手としてはどうなのでしょう。


質問者さんへ

既に解決されているのでしょうか。（不要なら、スルーしてください。）


まだなのであれば、私のできる限りお付き合いしますので、補足なり入れてください。

なお、具体的な環境がわからないと回答はできません。

※
＃１１のお礼欄に書かれている内容では、漠然とした回答しかできません。
その結果が、＃１６，１７の記述になっています。

＃１６，１７に記述した内容はわかりましたでしょうか。
その記述の中で提示したテーブルの項目、サンプルの記述がなかったら、
同じように読み進むことはできたでしょうか。


計算させたいのは、どのテーブルのどの項目名、
税区分を追加したテーブルはどれで、
それらのテーブルは、何（項目名）で結合されていて、
どうすれば辿っていける？

(1)(2)で税区分を追加した、と言ってるけど、(4)で区別できないとはどういうこと？
１つの請求書を複数の会社に・・・？？？

フォームはサブフォームあり？なし？単票？帳票？

各テーブルの項目名と、データサンプル各２件程度、
クエリの内容、
フォームに表示している項目、表示していない項目、、、

これらを具体的に提示してもらえませんか。

この回答への補足

30246kikuさんありがとうございます！

まだ解決していないので引き続き宜しくお願いします。
具体的な環境を伝えていきます。
記述不足で申し訳ございません。
以下の請求明細テーブルの消費税額を計算させたいです。

(請求書番号 納品書区分 日付 請求先コード 担当者コード）
S-00004 1 2000/03/10 0201 　　 01
S-00005 1 2000/03/15 0040 　　 01
(金額　　　 税抜金額 消費税額 税込金額 交通費 その他経費)
5,108,250 4,865,000 243,250 5,108,250 0 0
1,013,591 965,325 48,266 1,013,591 0 0
(備考１備考２備考３備考４備考５売上入力済)

税区分は以下の得意先名テーブルに追加しました。

(得意先コード 得意先名 略称 郵便番号 住所１)
　0010　　　　株式会社○ ○ 〒221- 横浜市
　0020　　　　△株式会社 △ 　愛知県
(住所２　　電話番号　　　振込先コード　税区分)
○○ビル　(045 )-??? -????　02　　　　切捨て
　　　　　　　　　 01 四捨五入

上記が請求先コード（∞）と得意先コード（1）で結合されています。
どうやって辿るかは何を答えたらいいでしょうか・・・

(1)(2)でクエリに税区分と得意先コードを追加したけれど、
フォーム上に得意先コードが無いためエラーになるので、
フォーム上にある請求先コードで進めようと考え直しました
が・・・請求明細テーブルは請求書1枚を１レコードとしているので、
請求書番号に対応する税区分になってしまうので、
(4)で請求先毎に区別できないと記述しました。

フォームにサブフォームはなく単票です。
請新フォームと請修フォームのレコードソースは
以下の請求書明細クエリーと請求書指定明細クエリーで、
違いは請求書指定明細クエリーにのみ、最後に

フィールド：Mid([請求明細テーブル]![請求書番号],3,5)
抽出条件　：[請求書番号を入力してください　（S-*****）]

があることだと思います。

フィールド　　　テーブル　　　　　並べ替え　表示
請求書番号　　　請求明細テーブル　昇順　　　レ
納品書区分　　　請求明細テーブル　　　　　　レ
日付　　　　　　請求明細テーブル　　　　　　レ
請求先コード　　請求明細テーブル　　　　　　レ
得意先名　　　　得意先名テーブル　　　　　　レ
振込先コード　　振込先名テーブル　　　　　　レ
担当者コード　　請求明細テーブル　　　　　　レ
担当者名　　　　担当者名テーブル　　　　　　レ
銀行名　　　　　振込先名テーブル　　　　　　レ
支店名　　　　　振込先名テーブル　　　　　　レ
預金種類　　　　振込先名テーブル　　　　　　レ
口座番号　　　　振込先名テーブル　　　　　　レ
金額　　　　　　請求明細テーブル　　　　　　レ
税抜金額　　　　請求明細テーブル　　　　　　レ
消費税額　　　　請求明細テーブル　　　　　　レ
税込金額　　　　請求明細テーブル　　　　　　レ
交通費　　　　　請求明細テーブル　　　　　　レ
その他経費　　　請求明細テーブル　　　　　　レ
内容１　　　　　請求内容テーブル　　　　　　レ
・
・
・
内容１０　　　　請求内容テーブル　　　　　　レ
工数・数量１　　請求内容テーブル　　　　　　レ
・
・
・
工数・数量１０　請求内容テーブル　　　　　　レ
単位１　　　　　請求内容テーブル　　　　　　レ
・
・
・
単位１０　　　　請求内容テーブル　　　　　　レ
単価１　　　　　請求内容テーブル　　　　　　レ
・
・
・
単価１０　　　　請求内容テーブル　　　　　　レ
金額１　　　　　請求内容テーブル　　　　　　レ
・
・
・
金額１０　　　　請求内容テーブル　　　　　　レ
交通費用　　　　請求内容テーブル　　　　　　レ
その他経費用　　請求内容テーブル　　　　　　レ
備考１　　　　　請求明細テーブル　　　　　　レ
・
・
・
備考５　　　　　請求明細テーブル　　　　　　レ
税区分　　　　　得意先名テーブル　　　　　　レ(フォーム上には無い)
得意先コード　　得意先名テーブル　　　　　　レ(フォーム上には無い)

以上です。
宜しくお願いします！

補足日時：2010/05/14 17:44

No.19 回答者： piroin654 回答日時： 2010/05/04 00:02

【30246kikuさんへ】

回答番号：No.18 の最初の部分を見逃していました。
　　　＆
関数等をこちらで作成したフォーム等に入れ込んで
確認は終わっていましたが、結構問答数が多くなっ
ているので控えていました。使い勝手はラクチン
そのものです。問題なく動きます。

私には十分過ぎる説明ですが、
関数を利用するインフラ整備が30246kikuさんの
説明通りにうまくekusenさんに伝われば、と思います。
お二人ともレスポンスと気概がいいのでうまく
いくと思いますが。

No.20 回答者： 30246kiku 回答日時： 2010/05/15 11:44

＃１８です

順に記述していきます。

> Private Sub 税抜金額_BeforeUpdate(Cancel As Integer)
> End Sub

これは「税抜金額」の更新前処理に何かを記述しようとしたと思われますが、
何もしていないので、削除しておいた方が、後々混乱することはないと思います。

> アイテム　[Forms]![請修フォーム]![税抜金額]
> 式　[１５]+[６１０]

＃１８に記述されたクエリを見る限り、
[１５]、[６１０]はフォーム上に配置されたものの名前だと思われます。
フォームをデザインビューで表示し、
「税抜金額」を求めている箇所を確認してみてください。

ここと、ここを加算したのを「税抜金額」にしている・・・というものがあると思います。

> コンボボックス「cbx1」を以下のように作成しました。
＃１８に記述されたクエリ内で「税区分」を得ているので、要らないと思います。

> 以下のエラーになります。
＃９の回答にありますが、DAO / ADO での記述の違い、参照の違いに起因しています。
piroin654さん提示が DAO、私提示のものが ADO になっています。
DAO 記述でエラーとなっていないようですので、DAO 用の以下を使って見てください。

Public Function GetTax(dt As Variant) As Currency
　　Dim db As Database
　　Dim rs As Recordset
　　Dim sSql As String

　　On Error Resume Next
　　GetTax = 0
　　If (Not IsDate(dt)) Then Exit Function

　　sSql = "SELECT TOP 1 税率 FROM T消費税 WHERE 適用日 <= #" & dt & "# ORDER BY 適用日 DESC;"
　　Set db = CurrentDb
　　Set rs = db.OpenRecordset(sSql, dbOpenSnapshot)
　　If (Not rs.EOF) Then GetTax = rs("税率")
　　rs.Close
　　Set rs = Nothing
　　Set db = Nothing
End Function


> 回答欄の、「アドバイス」は私が消してしまったのでしょうか？
いえいえ、
一ヶ月前までは、投稿する際に「回答」「アドバイス」「補足要求」「自信あり」等々を選べました。
質問者さんの環境に適した内容で回答できているとは思っていないので、
実際に試されて、使えるものなら・・・・
ということで、今まで「アドバイス」として投稿していたので。。


請求先コード = 得意先コード で結合されて、クエリで求めているようですので、
＃１７の２つ目に相当します。
指定できるかの確認は以下の手順で行います。
・フォームをデザインビューで開きます
・連結してあるテキストボックスのプロパティを表示します。
・コントロールソース欄右側の下▼ボタンをクリックします。
・「税区分」があるか確認します。
ここに表示されるのであれば、フォームに表示していなくても、[税区分]で参照できます。

ただ、「税区分」の内容が、文字列の様なので　TaxInterPre　記述は

Public Function TaxInterPre(sNum As Variant, cVal As Variant) As Currency
　　On Error Resume Next

　　TaxInterPre = 0
　　If (IsNull(sNum) Or IsNull(cVal)) Then Exit Function

　　Select Case sNum
　　　　Case "切捨て"
　　　　　　　　TaxInterPre = Int(cVal)
　　　　Case "四捨五入"
　　　　　　　　TaxInterPre = funcSG(cVal)
　　End Select
End Function

に変更しないと動かないと思います。（未検証）

【つづく】

この回答への補足

30246kikuさんありがとうございます！

請新フォームと請修フォームの以下の部分削除しました。
> Private Sub 税抜金額_BeforeUpdate(Cancel As Integer)
> End Sub

フォーム上に不可視のテキストボックス[１５]と[６１０]があり、
以下のマクロがありました。

アクション：値の代入
アイテム：[Forms]![請新フォーム]![１５]
式：[Forms]![請新フォーム]![金額１]+[Forms]![請新フォーム]![金額２]+[Forms]![請新フォーム]![金額３]+[Forms]![請新フォーム]![金額４]+[Forms]![請新フォーム]![金額５]

アクション：値の代入
アイテム：[Forms]![請修フォーム]![１５]
式：[Forms]![請修フォーム]![金額１]+[Forms]![請修フォーム]![金額２]+[Forms]![請修フォーム]![金額３]+[Forms]![請修フォーム]![金額４]+[Forms]![請修フォーム]![金額５]

GetTaxをコピーしてモジュールを作成しました。
「cbx1」「txtax」を削除して以下のようにしました。
アクション：値の代入
アイテム　：[Forms]![請新フォーム]![消費税額]
式　　　　：TaxInterPre([税区分],[Forms]![請新フォーム]![消費税額]*GetTax([日付]))
アクション：値の代入
アイテム　：[Forms]![請修フォーム]![消費税額]
式　　　　：TaxInterPre([税区分],[Forms]![請修フォーム]![消費税額]*GetTax([日付]))

「税区分」の内容は数字に修正したので回答番号：No.12の TaxInterPreを
コピーしてモジュールを作成しました。

以上の様にして試しに請求書を作成しようと金額を入力すると、
指定した式に、MicrosoftAccessが見つけることができない関数名が含まれています。
となり、アクションの実行エラーの引数は
TaxInterPre([税区分],[Forms]![請新フォーム]![消費税額]*GetTax([日付]))
で、請修フォームでも同じようになります。

宜しくお願いします！

補足日時：2010/05/17 17:53

この回答へのお礼

30246kikuさんありがとうございます！（5/21　17：08）
入力する場所が無いのでさかのぼって追記しています。

理由が見つからないのでもう一度最初からやり直しました。

1. T消費税を(#16)作成
2.標準モジュールTaxInterPre/funcSG(#12)GetTax(#20)を作成
3.得意先名テーブルに税区分追加後、1(切捨て)か2(四捨五入)を入力
4.請求書指定明細クエリーと請求書明細クエリーに税区分追加
5.マクロ作成(#21)

上記のようにしてから請新フォームを開き、(コマンドボタン)複製を
クリックすると先月分を参考に請求書を新規作成することができました。
が・・・税抜金額が100円でも1000円でも消費税額は－1円になります。
請修フォームでも試しましたが同じです。マクロは
アクション：値の代入
アイテム：[Forms]![請新フォーム]![消費税額]
式：TaxInterPre([税区分],[Forms]![請新フォーム]![税抜金額]*GetTax([Forms]![請新フォーム]![日付]))
と
アクション：値の代入
アイテム：[Forms]![請修フォーム]![消費税額]
式：TaxInterPre([税区分],[Forms]![請修フォーム]![税抜金額]*GetTax([Forms]![請修フォーム]![日付]))
です。Module1に
Public Function TaxInterPre(iNum As Variant, cVal As Variant) As Currency
On Error Resume Next

TaxInterPre = 0
If (IsNull(iNum) Or IsNull(cVal)) Then Exit Function

Select Case iNum
Case 1
TaxInterPre = Int(cVal)
Case 2
TaxInterPre = funcSG(cVal)
End Select
End Function

Public Function funcSG(ByVal xCy As Currency) As Currency
If xCy > 0 Then
xCy = Int(xCy + 0.5)
Else
xCy = Int(xCy - 0.5)
End If
funcSG = xCy
End Function
Public Function GetTax(dt As Variant) As Currency
Dim db As Database
Dim rs As Recordset
Dim sSql As String

On Error Resume Next
GetTax = 0
If (Not IsDate(dt)) Then Exit Function

sSql = "SELECT TOP 1 税率 FROM T消費税 WHERE 適用日 <= #" & dt & "# ORDER BY 適用日 DESC;"
Set db = CurrentDb
Set rs = db.OpenRecordset(sSql, dbOpenSnapshot)
If (Not rs.EOF) Then GetTax = rs("税率")
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
が入っています。T消費税は
ID適用日　税率
11989/04/013.00%
21997/04/015.00%
のようになっています。
宜しくお願いします！

お礼日時：2010/05/21 17:12