The error of my ways

  • Thread starter Thread starter TeeSee
  • Start date Start date
T

TeeSee

I have just spent the evening redoing an original database in order to
make all the tables "normalized" with referential integrity invoked
etc. All looks and works well except for one single field table which
contains the US/CAN exchange rate that is used in queries for
calculations. What to do and how to treat a value such as this that
fluctuates as does the exchange rate??

Thanks again.
 
Are you sure that you don't need a history of the exchange rate changes?

--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com







- Show quoted text -
Click to expand...

Thanks for the reply Arvin. You DID make me think about that but in
what I am doing I don't believe I have any requirement for history of
the exchange rate. The only thought I might have is to input a value
on load and store it as a gobal constant. Would that be a viable
option?
 
Problem with a global variable is that it is available only to VBA code, not
to queries. If all your calculations are done in code, then that is fine. But
it's usually more efficient to do that in queries, so the single-field,
single-row table is the way to go. On the main form of the app, put a text
box, say txtXRate, bound to the exchange rate field of this table. On form
load, put up an inputbox displaying the current rate and asking for an update:
txtXRate = InputBox("Enter current exchange rate", "InputBox Title", txtXRate)
 
You might also want to store the exchange rate in any sales table you have,
although I think a separate ExchangeRate table that tracks the exchange rate
history would be better. I'd put ID, EffDateTime, and Rate fields in the
table (if you have to deal with more than the US/Can rate, you would need
additional fields to keep track the two currencies. That would give you the
ability to go back and check the rate at the particular date/time of a
purchase, and allows for rates that my vary within a single day (although I
have no idea whether that is possible with currencies).

Dale
 
I think storing in a table is the best method, but to answer your post about
global variables. I try not to use to many of them because they have a way
of deinitializing if there are errors. You can easily make them available to
Access/Jet queries by wrapping them in a function, like (aircode):

Dim gMyString As String = "Arvin"

Public Function Arvin() As String
Arvin = gMyString
End Function

Now you can use:

Select Arvin() As Expr1 From AnyTable.
 
Back
Top