Newbie Help!

[BoB]

Appreciate any help to get me in the right direction!

I am writing an Invoiced Sales Summary report. Invoices are created in
both USD and CAD but I want to calculate the USD to equivalent CAD.

I have created a make table query [qmtbExRateLookup] that creates a lookup
table [tblExRateLookup] with the exchange for that month in the invoice
line date. I am trying to calculate the equivalent CAD in the report by
using a text box with this as the control source,
=IIf([SH_ACCT_ID]="1901.1",[IV_SUB_TOTAL]*[tblExRateLookup]![Exch],[IV_SUB_T
OTAL]*1).
I don't get the calculated field.

Can anyone recommend another solution?

Dave

 

[Larry Linson]

Did you expect it to retrieve a value from a Field named "Exch" in a Table
named "tblExRateLookup" because you used the expression
"[tblExRateLookup]![Exch]"?

If so, sorry, you can't read a table that way. In an expression, you could
use the DLookup domain aggregate function for that purpose. If that is
intended to mean something else, please clarify here. (And, it wouldn't hurt
if you explained what you mean by "creates a lookup table [tblExRateLookup]
with the exchange rate for that month in the invoice line date" because
that, in itself is confusing to me. Further, I don't see where you are
selecting a particular record in the exchange rate table, even if that form
of reference would work.

Larry Linson
Microsoft Access MVP