MS ACCESSフォームで通貨の計算

質問

ACCESSのフォームで通貨の合計を求めようと思っています。

通貨の単位が変更になることがあるので、書式をテキスト型にして（IDR1,000,000.00などと記入）、Val関数で数値部分だけ取り出そうと考えました。

金額のテキストボックス名： Declared_Value_2

合計のテキストボックス名： T_Declared_Value_2　　　　

合計のコントロールソース： =Sum(Val([Declared_Value_2]))

としてみたのですが#Errorが出ます。
これではできないのでしょうか？

ちなみに、通常はドルの計算を行っていて、たまに他の通貨を入力する必要があるので（上記のインドネシアルピーのような）ドルとはテキストボックスを分けることにしました。
スマートな方法ではないですが、VBAや複雑な関数の知識がないので。。。

いい方法がありましたらアドバイス頂ければ幸いです。

回答No.5

hatena1989 · ベストアンサー

> =Format([Declared_Value],"""[Currency_Unit]""" & "#,##0.00")
>
> とすると（金額は$1,200.00）、
>
> [Currency_Unit]1,200.00
>
> といった出方になって意図した表示になりませんでしたし。。。

前にも書きましたが、式内の " で囲まれた部分は文字列となります。

"""[Currency_Unit]"""

とすると、「"[Currency_Unit]"」という文字列と判断されます。
つまり、書式プロパティに

"[Currency_Unit]"#,##0.00

と設定したのと同じ意味になります。よって、そのような結果になります。

式で文字列と文字列を結合するときは & を使います。

="""" & "$" & """" & "#,##0.00"

とコントロールソースに設定して確認してみてください。フォームビューにすると

"$"#,##0.00

と表示されます。

他にも、テキストボックスのコントロールソースに下記のように設定してみて、
それぞれのフォームビューにした時のそれぞれの表示の違いを確認してみて、
なぜそうなるのかよく考えてみてください。

="""[Currency_Unit]""" & "#,##0.00"

="""" & "$" & """" & "#,##0.00"

="""" & [Currency_Unit] & """" & "#,##0.00"

他にもいろいろ試してみて、どうなるか確認してみるといいでしょう。

理解するまで、ちょっと戸惑うと思いますが、
分かれば、あっ、なるほど、と納得できると思います。

お礼

初心者の私に長く付き合ってくださってありがとうございます！！
とても勉強になりました。教えていただいた知識をいろいろ活用していこうと思います。

回答No.4

hatena1989

> 一つ、"""" & [Currency_Unit] & """"　←ここの部分はtrim関数と同じ意味でしょうか？

表示書式を変更したい場合、通常は書式プロパティに設定します。

今回も、単位が固定なら、書式プロパティに

"USD"#,##0.00

と設定します。

Format関数は書式プロパティと同じ書式変換を関数でするものです。
例えば、

=Format([フィールド名],"#,##0.00")

と設定すれば、12345 が 12,235.00 と表示されます。
ここで、第２引数に注目してください。"" で囲んでいますね。これは、Format関数の第２引数は文字列で指定するという仕様で、式の中で文字列は""で囲む、というAccessのお約束事があるからです。

書式プロパティで、固定の文字列を表示したいという時も "" で囲むというお約束事があります。
そこで、

=Format([フィールド名],""USD"#,##0.00")

とすると構文が正しくないと怒られます。
式の文字列内で " を表現するときは、"" と重ねるという約束事があります。

=Format([フィールド名],"""USD""#,##0.00")

これでエラーはでなくなります。USD の部分を固定ではなくフィールドで指定したい場合は、
下記のように考えていきましょう。

まず、単位と数値書式を分割する。

=Format([フィールド名],"""USD""" & "#,##0.00")

単位文字列を分割する。

=Format([フィールド名],"""" & "USD" & """" & "#,##0.00")

単位文字列を、フィールドに変更する。

=Format([フィールド名],"""" & [Currency_Unit] & """" & "#,##0.00")

これで完成。
なんかややこしいですが、ご理解いただけたでしょうか。

お礼

早速のお返事ありがとうございます。

難しいですね～
確かに少しでも違うとエラーが出ますね。

文字列を分割する意味が分からなくて

=Format([Declared_Value],"""[Currency_Unit]""" & "#,##0.00")

とすると（金額は$1,200.00）、

[Currency_Unit]1,200.00

といった出方になって意図した表示になりませんでしたし。。。

回答No.3

hatena1989

> =[単位] & Format([金額],"#,##0")
>
> の式は、フォームではなくレポートのテキストボックスのコントロールソースに挿入するという意味でしょうか？

フォームでも、レポートでも、テキストボックスのコントロールソースに設定すれば有効です。

> 金額フィールドの既定値は空白、値要求の設定は「いいえ」にしてあります。
> （レポートの体裁を整えるためそうしてます。
> 一枚のレポートに10件レコードを印刷したいが、10件に満たないレポートでもテキストボックスの形を残したい為。No#だけ振って内容のないレコードが存在している感じです。）

金額が未入力なら、非表示にしたいということなら、
上記の式を、

=Format([金額], """" & [単位] & """" & "#,##0")

とすればいいですね。

これで、単位 の既定値を設定しておいても、金額が未入力なら空白になります。

フォーム上で、[単位]の入力テキストボックスにも表示させたくないというなら、VBAが必要になりますが、そこまで必要ですか。

蛇足ですが、10件に満たないレポートでも10行分の空白行を表示したいのな、
WEBで「Access レポート 罫線 最後まで」辺りをキーワードに検索すればいろいろ方法が見つかりますよ。

お礼

いろいろと教えて頂きありがとうございます！返事が遅くなりましてすみません。

上の式でできました！都合上小数点以下２桁まで必要だったので、

=Format([Declared_Value], """" & [Currency_Unit] & """" & "#,##0.00")

としました。やりたかったのはまさにこのことでした！！
フォームのテキストボックスには単位だけ表示されても大丈夫です。レポートで整ったので。
VBAを使わずにできてよかったです。
１から勉強しないといけないかと思いひやひやしてました。

一つ、"""" & [Currency_Unit] & """"　←ここの部分はtrim関数と同じ意味でしょうか？

「Access レポート 罫線 最後まで」の件、アドバイスありがとうございます！！
しかし、なかなか私にはハードルが高く、実際の使用者も数字の手打ちも手間でないということだったので今回は辞めておきます。

回答No.2

hatena1989

単位フィールドはコンボボックスするといいでしょう。
入力間違いを防ぐことができますので。

値集合ソースはマスターテーブルを作成しておいてそれを設定します。

テーブル設計例

通貨マスター
通貨コード / 記号 / 通貨名 / 国または地域
AUD / $ / オーストラリアドル / オーストラリア
CAD / $ / カナダドル / カナダ
CHF / S₣ / スイスフラン / スイス
CNY / 元 / 元 / 中国
DKK / kr / デンマーククローナ / デンマーク
EUR / € / ユーロ / EU
GBP / ￡ / 英ポンド / イギリス
HKD / $ / 香港ドル / 香港
HUF / Ft / ハンガリーフォリント / ハンガリー
ISK / kr / アイスランドクローナ / アイスランド
INR / Rs / インド・ルピー / インド
JPY / \ / 円 / 日本
MXN / $ / メキシコペソ / メキシコ
NOK / kr / ノルウェークローネ / ノルウェー
NZD / $ / ニュージーランドドル / ニュージーランド
PLN / zł / ポーランドズウォティ / ポーランド
SEK / kr / スウェーデンクローナ / スウェーデン
SGD / $ / シンガポールドル / シンガポール
TRY / ₤ / トルコリラ / トルコ
USD / $ / 米ドル / アメリカ
ZAR / R / 南アフリカランド / 南アフリカ

通貨単位コンボボックス設定例

コントロールソース　単位
値集合ソース　SELECT 通貨コード, 通貨名 FROM 通貨マスター;
連結列　1
列数　2
列幅　0.5cm;2cm
リスト幅　2.6cm

> 大半がドルなので、規定値として＄が表示されるようにしたいのです。しかも金額が入力されたときのみ表示されるように。

既定値プロパティに設定すると、金額未入力でも表示されてしまいますので、VBAが必要になりますね。
ところで、金額フィールドの既定値は 0 に設定してあるのでしょうか。それとも空白？
また、「値要求」の設定はどうなってますか。
それによってコードが異なってきますので情報提示してください。

お礼

詳しく書いてくださってありがとうございます！！
やはりコンボボックスにした方が良いのですね。

金額フィールドの既定値は空白、値要求の設定は「いいえ」にしてあります。
（レポートの体裁を整えるためそうしてます。
一枚のレポートに10件レコードを印刷したいが、10件に満たないレポートでもテキストボックスの形を残したい為。No#だけ振って内容のないレコードが存在している感じです。）

ちなみに前に言われた、

=[単位] & Format([金額],"#,##0")

の式は、フォームではなくレポートのテキストボックスのコントロールソースに挿入するという意味でしょうか？

回答No.1

hatena1989

Valは先頭から数値部分のみ取り出しますので、数字で始まる値でないとだめです。
また、カンマ区切りにすると、カンマまでしか取り出せません。

単位を後に、カンマ無しにすれば、取り出すことができます。

100000000IDR

と入力するようにすれば取り出すことはできます。

=Sum(Val([Declared_Value_2]))

しかし、通貨型のフィールドと単位のフィールドの２つに分けたほうがいいでしょう。

金額　通貨型
単位　テキスト型

そうすれば単純に、

=Sum([金額])

で集計できますし、処理も高速です。
単位込みで表示したい場合は、

=[単位] & Format([金額],"#,##0")

というような式でお好みの表示に変換できます。

別に通貨マスターテーブルを作成しておいて、そこに為替ルートを格納しておけば、
複数通貨が混在していても集計させることも可能だったりします。

お礼

分かりやすく教えて頂きありがとうございます。
アドバイスの通り単位と金額のフィールドを分けることにしました。

ですが、そこでさらに分からないことが出てきまして。。。

大半がドルなので、規定値として＄が表示されるようにしたいのです。しかも金額が入力されたときのみ表示されるように。

テキストボックスのコントロールソースに

=IIf([金額]>0,"$","")

と入力すると、コントロールソースにフィールド名を入れることができないので、テーブルに＄というデータを格納できないのでしょうか？