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

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.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.24 回答者： 30246kiku 回答日時： 2010/05/22 21:24

＃２３です

クリックしますか・・・なんて、そのものを記述していないと気味悪いですよね。

ってことで、クリックされるかはご自身で判断してください。
（サイト内には、もろ広告があるようです）
（っていうか、後、何回書けるのでしょうか？？？）
（今後、削除対象になるかも・・・、、いや、今回のも削除対象でしょうか？？？）


実行時エラー、あわてないでデバッグを押す、デバッグ方法解説
http://www.ken3.org/vba/debug.html

その記事と合わせて、先頭にある
[Debug.Printを使い値の確認]/ [ウォッチ式とSTOP]
なども参考になると思います。

この回答へのお礼

能力不足なので消費税を日付から求めることを諦めました・・・
長く教えていただいたので申し訳ないです。
以下でできました。

1.標準モジュールを＃12を参考に作成
2.得意先名テーブルに税区分追加（1or2入力）
3.それぞれクエリに税区分追加
4.＃12を参考にマクロ作成

まだまだ変更すべき点がありそうですのでまたよろしくお願い致します！

お礼日時：2010/05/24 17:46

No.23 回答者： 30246kiku 回答日時： 2010/05/22 12:09

ご迷惑をおかけしています。

＃２２です

回答していて、個人的に考えさせられることがあったので、しばらく離れていました。
解決したい、っていう質問者さんには、不利益なことと思ってしまいます。
経緯は、私の最近の回答履歴を参照されるとわかると思います。


> 消費税額は－1円になります

この現象は、TaxInterPre を呼び出す時に、TaxInterPre( 2, 0 ) のように、
四捨五入ありの区分で、消費税部分が 0円 の場合に発生します。

これは、内部の処理については言及していませんでしたが（書き方だけ）、
（どのような処理内容・・・は、客先が提示することだったような？？）
funcSG の判別部分に起因します。

If xCy > 0 Then

により、結果的に　-1　となる処理となります。

If xCy >= 0 Then

にすると、0 は 0 のままとなります。

-1　になるのは、この部分を修正することで対処できますが、
> 税抜金額が100円でも1000円でも
より、本質的な解決にはなりません。


値がセットされていて、チャンと　TaxInterPre　を呼び出しているはず!!!
と思われているものと察します。
こういった時には、デバッグ作業をしていくことになります。
（細かいところは私にはわからないので、自分でするしかありません）

ボタンをクリックする前に、module1 を開いて、
TaxInterPre　内の先頭　TaxInterPre = 0　部分をクリック後「F9」を押し、
ブレークポイントを設定します。
（もう一度「F9」を押すと解除になります）
設定/解除は、その行が反転表示されるのでわかると思います。
（メニューのデバッグから辿って選択しても同様です）

どういう値が設定されているの・・・
を確認するには、メニューの表示から「イミディエイト」「ローカル」「ウォッチ」
などで行います。

今回、その値だけを確認したいので「イミディエイト」を使ってみます。

ボタンをクリックすると、ブレークポイントに設定した　TaxInterPre = 0　で止まります。
止まっている状態で、マウスのポインタを変数名のところに持っていくと
今の値は何ですよ・・・って簡単に確認することもできます。

cVal が 0 だったら、イミディエイトウィンドウに以下を入力して、元々の値を確認してみます。

? [Forms]![請新フォーム]![税抜金額]
? [Forms]![請新フォーム]![日付]
? GetTax([Forms]![請新フォーム]![日付])

起動していたフォームが「請修フォーム」だったら
? [Forms]![請修フォーム]![税抜金額]
? [Forms]![請修フォーム]![日付]
? GetTax([Forms]![請修フォーム]![日付])

行の頭に記述している ? は、教えてよ、って意味になります。

どこが 0 になっているの・・・っていう感じで、追いかけていきます。

値だけではなくて、付随するプロパティとかも確認していきたいって時には、
「ウォッチ」を使ったりします。


現在、「請新フォーム」「請修フォーム」の２つがあるようですが、
代入先、計算過程のコントロール/コントロールソース名が同じであれば、
マクロでの記述は、フォーム名を指定しない以下で共通で使えるような気がします。
（マクロはあまり得意ではないので検証はしていませんが）

アクション：値の代入
アイテム：[消費税額]
式：TaxInterPre([税区分],[税抜金額]*GetTax([日付]))

これを確かめられるのであれば、まずは先の問題が解決してからということに・・・


※　もしここで、参考URLの記述があったとしたらクリックされますか。
（解決されるための方法とかが記述されているところに誘導するのが目的ですが）

No.22 回答者： 30246kiku 回答日時： 2010/05/17 18:32

＃２１です

なんか追記があるんじゃ・・・と見てみたら、ありましたね。
（さて、通知のメールはいつ届くのでしょう・・・）


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

＃１２のものですか？
「税区分」の内容が文字列であれば、＃２０のものになると思います。


> 指定した式に、MicrosoftAccessが見つけることができない関数名が含まれています。

TaxInterPre / GetTax 関数の記述は、標準モジュールにしていますでしょうか。

各フォームに記述しているのなら、標準モジュールの方に記述を移し、
各フォーム内の記述は削除します。
（四捨五入部分の関数も同様に）

標準モジュールの挿入は、
VBE（VBAを記述していく画面）のメニューに挿入があったと思います。

まず、この変更をやってみてください。
（仮定で記述していますので、既に実施済みなら・・・）


また、わからなかったら記述してください。

この回答への補足

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

「税区分」の内容を数字に修正したので＃１２のTaxInterPreを
コピーしてモジュールを作成しました。

TaxInterPre/GetTax/funcSG関数の記述はデータベースウィンドウの
オブジェクトからモジュールを選択して作成していましたので
挿入から標準モジュールを選択してそこに貼り付けて、
それぞれ別にして再度作成しました。

上記のようにして使ってみましたが
指定した式に、MicrosoftAccessが見つけることができない関数名が
含まれています。
となり前回と同じです。

ちなみに(TaxInterPre関数の画面→ツール→)参照設定は
Visual Basic For Applications
Microsoft Access 9.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensbility 5.3
にチェックが入っています。

宜しくお願いします！

補足日時：2010/05/18 14:33

この回答へのお礼

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

ちなみにバージョンは
Microsoft Windows XP
Microsoft(R) Access 2000
です。

一つ気になるのがモジュールを選択しても実行ボタンを
選択できないことです。

宜しくお願いします！

お礼日時：2010/05/18 16:19

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

【つづき】

＃１６，１７では、コントロールソースへの書き方にしていましたが、
マクロで値を代入する式のところには、先頭の = を削除したものを指定してください。

例）
アクション：　値の代入
アイテム：　[消費税額]
式：　TaxInterPre([税区分],[税抜金額]*GetTax([日付]))

で良さそうですが・・・

>「txtax」も作成しました。
ということなら、
式：　TaxInterPre([税区分],[税抜金額]*[txtax])

でも。


※税区分を消費税のように履歴を持てる形ではない、のですよね。
「税区分」の内容が、文字列の様なので、GetTaxDiv　に変更が必要になります。

わからない点等は、また記述してください。

この回答への補足

30246kikuさんいつもありがとうございます！(5/20 15:50)
頭の整理をする為にもう一度以下のようにやり直しました。

T消費税の作成　＃16参考
↓
TaxInterPre/GetTax/funcSGという名前のモジュールを作成
（TaxInterPre funcSGは＃12、GetTaxは＃16参考）
↓
得意先名テーブルに税区分(数値型)追加
↓
請求書明細クエリーと請求書指定明細クエリーに税区分追加
↓
マクロ作成

アクション：値の代入
アイテム：[Forms]![請新フォーム]![消費税額]
式：TaxInterPre([税区分],[Forms]![請新フォーム]!
[税抜金額]*GetTax([Forms]![請新フォーム]![日付]))

アクション：値の代入
アイテム：[Forms]![請修フォーム]![消費税額]
式：TaxInterPre([税区分],[Forms]![請修フォーム]!
[税抜金額]*GetTax([Forms]![請修フォーム]![日付]))

以上の様にしたところ、請新フォームは得意先ごとに区別して
税計算をするようになりました！
次に請修フォームで値を入力してみると
「このオブジェクトに値を代入することはできません。
※指定したオブジェクトは、読み取り専用フォーム上のコントロールです。
※デザインビューで開いているフォーム上のオブジェクトです。
※指定した値が大きすぎるために、このフィールドに代入できません。」
となります。通常通りの値を入力しているので大きすぎることは無いです・・・

アクションの実行エラーは
請求書コマンドマクロ.計算2
条件：True
アクション名：値の代入
引数：[Forms]![請修フォーム]![税抜金額],[15]+[610]
です。

請新フォームではできたので、なかなか理由が見つかりません。
宜しくお願いします！

補足日時：2010/05/20 15:51

この回答へのお礼

30246kikuさんいつもありがとうございます！(5/20 16:18)

請新フォームにコピーというコマンドボタンがあり、
先月分の請求書をコピーして今月分の請求書を作成しています。
そのコピー（コマンドボタン）を押すと
「このフィールドに入力した値が不正です。
たとえば、数値型のフィールドに文字列を入力しました。」
となります。
アクションの実行エラーは
請求書コマンドマクロ.コピー
条件：True
アクション名：値の代入
引数：[Forms]![請新フォーム]![税抜金額],[Forms]![請修フォーム]![税抜金額]
です。
税抜金額(フィールド名)は通貨型(データ型)で書式は「\\#,##0;"-\"#,##0」です。

何を伝えたらいいのか分からないので足りない部分はおっしゃって下さい。
宜しくお願いします！

お礼日時：2010/05/20 16:19

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

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

【30246kikuさんへ】

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

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

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.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.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