Accessでの数字のデータ型

質問

お世話になります。Access初心者です。
Access2003　OS:WindowsXP
既存のオーダー入力DBに変更を加えているのですが、行き詰まってしまい質問させていただきました。
現在USDからYENへのレートが変わる度に価格表を差し替えていますが、
レートの部分を別のテーブルで期間指定して管理できないかと考えています。

構成：（関連する部分のみ）
[テーブル]
tbl_注文(注文番号*,日付)*は主キー
tbl_注文Sub(注文番号,ID,数量,変更後価格USD,変更後価格JPY)※注文番号でtbl_注文とリレーションシップを組んでいる
tbl_価格表(ID,型番,USD,JPY,RateID)
tbl_Rate（RateID,Rate,開始日,終了日)※今回新たに作成

[クエリ]
qry_注文Sub
現在の構成：
tbl_注文⇒tbl_注文Sub－tbl価格表
※tbl_注文Subとtbl_価格表はIDで連結させている
表示項目：
注文番号,ID,数量,USD,JPY,USDSubTotal:IIf([Currency]=2,IIf([変更後価格USD]>0,[変更後価格USD]*[Qty],[USD]*[Qty]),0),JPYSubTotal:IIf([Currency]=1,IIf([変更後価格JPY]>0,CCur([変更後価格JPY]*[Qty]),IIf(IsNumeric(Format$([JPY],"数値")),CCur([JPY]*[Qty]),CCur(0))),CCur(0))

考えている変更：
qry_Rate
Rateを入力(日付のコントロールはとりあえず後回しに）
qry
qry_注文Sub
tbl_注文⇒tbl_注文Sub－tbl価格表－tbl_Rate
表示項目：
注文番号,ID,数量,USD,JPY:IIf(IsNumeric(Format$([USD],"数値")),[USD]*[Rate],0),USDSubTotal:IIf([Currency]=2,IIf([変更後価格USD]>0,[変更後価格USD]*[Qty],[USD]*[Qty]),0),JPYSubTotal:IIf([Currency]=1,IIf([変更後価格JPY]>0,CCur([変更後価格JPY]*[Qty]),IIf(IsNumeric(Format$([JPY],"数値")),CCur([JPY]*[Qty]),CCur(0))),CCur(0))
※USDはNAの場合もあり、フォーマットはテキストです。
※Rateは数値型です。

上記の変更で、qry上では正しく計算が出ますが、formになると表示されません。
Subフォームで型番を選択して数量を入力すると価格*数量が表示される設定ですが、そもそもSubフォームに何も表示されないようになってしまいます。

仮に[Rate]ではなく、[USD]*120とすると、フォームも問題なく動きます。
何かデータ型の問題ではないかと推測しているのですが、
CCurをつけてみるなど、いろいろ試してもうまくいきません。

どなたかアドバイスをいただけるとありがたいです。
どうぞよろしくお願い致します。

回答No.1

noname#140971

一言で言えば、ステップバイステップ。

先ずは、

JPY:IIf(IsNumeric(Format$([USD],"数値")),[USD]*[Rate],0)

の式から。

[イミディエイト]
？Format$(11,"数値")
数値
? IsNumeric(Format$(11,"数値"))
False

式は、イミディエイトウインドウで確認したがいいです。
そうすれば、式の無意味さが確認できます。

<tab1>
ID__USD___RATE_______数量
1____113____\120.11_____10
2____119____\120.22_____10
3____null____\120.33_____10

<クエリ1>
JPY
13572.43
14306.18
0.00

こういう結果を得たいのだと推察します。

SELECT Format(CCur(Nz([USD]))*[Rate],"0.00") AS JPY FROM tab1;

ここで使用している Format は無駄の無駄。

SELECT CCur(Nz([USD]))*[Rate] AS JPY FROM tab1;

まさか、列[USD] に 'ABC' と入力することはないでしょう。

SELECT ＩＩf(IsNumeric([USD]),CCur([USD])*[Rate],0) AS JPY FROM tab1;

ならば、このようにクエリを複雑化する必要はないのでは？
重たいクエリは、環境によっては、その動作は保障されていませんよ。

お礼

Husky2007さん、
早々にアドバイスいただきありがとうございます。
お教えいただいたように変更してみましたが結果は同じでした。
やはり[Rate]にするとformが正しく表示されません。qryのビューでは問題なく計算されています。
イミディエイトというものがあるのですね。初めて知りました。。

出したい結果はご察しのとおりです。ただ、月々レートが変わるので、
＜tbl_Rate＞
RateFlag＿ID＿Rate＿BeginDate＿EndDate
R＿1＿120.22＿2007/9/1＿2007/9/30
R＿2＿120.33＿2007/10/1＿null

このように管理したいです。
もし同じレートが2ヶ月続いた場合には期間が2ヶ月になります。
過去のレートを保持したい理由は、過去のオーダーを検索した場合でも注文日でそのときのレートで換算された価格が出るようにするためです。

frm_注文Subで読み込んでいるqry_注文Subは以下のとおりです。
SELECT tbl注文Sub.注文番号, tbl注文Sub. ID, tblPrice.Description, tblPrice.[USD], tblPrice.型番, tbl注文Sub.Qty, tbl注文Sub.変更後価格, tbl注文.Currency, tbl注文Sub.AdjustPriceUSD, IIf([Currency]=2,IIf([変更後価格USD]>0,[ 変更後価格USD]*[Qty],[USD]*[Qty]),0) AS SubTotalUSD,
IIf([Currency]=1,IIf([変更後価格JPY]>0,[ 変更後価格JPY]*[Qty],[JPY]*[Qty]),0) AS SubTotalJPY, tbl注文, CompanyID, IIf(IsNumeric(Format$([USD],"General Number")),CCur(Nz([USD]))*120,0) AS JPYFROM (tblCustomer INNER JOIN tbl注文 ON tblCustomer.CompanyID = tblPOLog.CompanyID) INNER JOIN (tblPrice INNER JOIN tbl注文Sub ON tblPrice.ID = tbl注文Sub. ID) ON tbl注文. 注文番号= tbl注文Sub.注文番号
ORDER BY tbl注文Sub. 注文番号, tbl注文Sub. ID;
※実際は価格が文字列だった場合の処理なども書かれていますがここでは割愛しています。

上記だとfrm_注文Subはちゃんと表示されますが、
IIf(IsNumeric(Format$([USD],"General Number")),CCur(Nz([USD]))*[Rate],0) AS JPY
とすると表示されません。
SubフォームのIDのコンボボックスが出てこないのです。

補足

すみません。入力が足りなかったのでこちらに続きを書かせていただきます。
「お礼」の続きとして読んでください。

なお、frm_注文Subでは下記のようにしてIDで他の情報をひっぱっています。
SELECT tblPrice.ID, tblPrice.型番, tblPrice.BginDate, tblPrice.[Expiration date] FROM tblPrice WHERE (((tblPrice.BginDate)<=Forms!frm注文!注文日) And ((tblPrice.[Expiration date]) Is Null)) Or (((tblPrice.BginDate)<=Forms!frm注文!注文日) And ((tblPrice.[Expiration date])>=Forms!frm注文!注文日)) ORDER BY tblPrice.型番;

また、Formatですが、最初にこのDBを作成された方がそのように記述しており、現状そのまま使い続けているので変更していません。
不要なものは削除していきたいので、
もう少しAccessに詳しくなってきたらその辺の簡素化も図りたいと思っています。ご指摘ありがとうございます。
ちなみに価格表にはnullではなく"NA"とう文字存在します。

なぜ*120は問題なくて*[Rate]だとだめなのでしょうか。。
これらの説明で何かわかることがあればご教授いただければありがたいです。

どうぞよろしくお願いいたします。