Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Currency Conversion (2000)

    I have two fields, one a Number field called Asset and another a text field called AssetCurrency, which assumes the values of either Dollars, Euros or Pounds. I want to be able to readily convert the Asset field between the different currencies. How would you build a link table that stores the currency conversion rates? Should it be some sort of Crosstab or is that too complicated? Cheers, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Currency Conversion (2000)

    I would store all amounts in one currency. Let's say in Dollars.
    The conversion table tblRates can be very simple (exchange rates are fictitious)

    <table border=1><td>Curr</td><td>Rate</td><td>Dollar</td><td align=right>1</td><td>Euro</td><td align=right>1.1</td><td>Pound</td><td align=right>1.6</td></table>
    On the form used for data entry, use an unbound text box txtAssetEntry. Use code to fill the Asset field:

    Private Sub txtAssetEntry_AfterUpdate()
    If IsNull(Me.txtAssetEntry) Then
    Me.Asset = Null
    Else
    Me.Asset = Me.txtAssetEntry * DLookup("Rate", "tblRates", "Curr=" & Chr(34) & Me.AssetCurrency & Chr(34))
    End If
    End Sub

    You also need code in the On Current event of the form and in the After Update event of AssetCurrency to set the displayed value.

    Private Sub Form_Current()
    If IsNull(Me.Asset) Then
    Me.txtAssetEntry = Null
    Else
    Me.txtAssetEntry = Me.Asset / DLookup("Rate", "tblRates", "Curr=" & Chr(34) & Me.AssetCurrency & Chr(34))
    End If
    End Sub

    Private Sub AssetCurrency_AfterUpdate()
    If IsNull(Me.Asset) Then Exit Sub
    Me.txtAssetEntry = Me.Asset / DLookup("Rate", "tblRates", "Curr=" & Chr(34) & Me.AssetCurrency & Chr(34))
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •