Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exchange Rates (A2K)

    Scenario:

    Goods received into inventory can be priced in US$ or Cdn$

    If received in US$, dollar value needs to be converted using current Exchange Rate

    Formula: qty * price * Xrate = converted value

    Entering most current Xrate in each record is not a viable approach.

    The Xrate should be in 1 place only and available to be changed as the economic gods see fit to do so

    I'd appreciate any suggestions with thanks in advance.
    Cheers,
    Andy

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

    Re: Exchange Rates (A2K)

    Create a table with an exhange rate field. The table will have only one record.

    You can create a very simple form based on this table in which the user can view/edit the exchange rate.

    You can add this table to existing queries, and use the exchange rate field in calculations.

    You can also use DLookup("XRate","TableName") (with the appropriate names substituted) to retrieve the rate.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exchange Rates (A2K)

    Hans,

    To be perfectly honest, I did:

    Create an Exchange Table with 1 record.
    Question: How do I force it to be 1 record only so some nit doesn't try to add more records to it?

    Create a single form reading the above table with only the one field in it so the user could update it

    My problem lay in when I try to "attach" the Exchange Table to an existing query which is being used to create new records in the form' I don't have a field to connect the two thereby negating my using the exchange field in a calculation.

    I know that this is where a I have a problem in grasping the usage of an information-providing query. If I try to attach it, there is no corresponding field and I'm unable to add records to the table.

    In the meantime, I'm going to try to humiliate myself further with the Dlookup concept.

    Thanks again,
    Cheers,
    Andy

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exchange Rates (A2K)

    If it were me, I would create the table with two records. One for the US$ and one for the CN$ (whose rate would the 1.0). That would make using it in a query calculated field easier.
    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exchange Rates (A2K)

    Legare,

    I appreciate your looking in on my problem. Based on the assumption that I get this working correctly, I'm not quite sure how it would make it easier to have both rates in the table. Although the calculations are going on in the background within the query where the one rate will be the one used, the Exchange Rate itself, will be displayed on the form so that the user knows exactly by how much they're being reamed at that point in time. Attempting to show both might cloud men's minds and only rub in the difference.
    Cheers,
    Andy

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

    Re: Exchange Rates (A2K)

    > How do I force it to be 1 record only so some nit doesn't try to add more records to it?
    Set the Allow Additions and Allow Deletions properties of the form used to edit the exchange rate to No.

    > I don't have a field to connect the two.
    That doesn't matter - since the Exchange Rate table has only one record, you don't need to link it to the main table. You can still use the field in calculations.

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

    Re: Exchange Rates (A2K)

    There certainly is something to be said for that. On the other hand, a table with only one record has the advantage that you don't need to join it to other tables in a query.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exchange Rates (A2K)

    I know it sounds like I keep poking myself in the eye with the same sharp stick, but, I assume I would somehow refer to the tbl Exchange containing the field Exchange within the calculation without actually putting that table in the design-mode panel. Because, if I just drop that table into the query in design mode and without attaching it, it disables my ability to enter new records. Sorry about my confusion,

    PS. I hope it wasn't my confusion that brought down the Forum! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Cheers,
    Andy

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

    Re: Exchange Rates (A2K)

    You can use a query with the exchhange rate table added for situations in which you don't need to edit records, for example as row source of a list box or combo box, or as record source of a report.
    In other situations you shouldn't add the exchange rate table to a query. You can use DLookup instead. DLookup("NameOfField","NameOfTable") with the appropriate names substituted will get the value.

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exchange Rates (A2K)

    Hans,

    I love the fact that hardly a day/week goes by when I don't learn something totally new and useful from yourself or some of the other contributors on this forum. My final formula in this case for those who want to see a working example was:

    CdnValue: IIf([IC_Recvd_Cdn]=-1,([IC_Recvd_Qty]*[IC_Recvd_CostPer]),([IC_Recvd_Qty]*[IC_Recvd_CostPer]*(DLookUp("IC_ExchangeRate","tbl Inventory_XChangeRate"))))

    Thanks again,
    Cheers,
    Andy

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exchange Rates (A2K)

    A final thought: how are you going to avoid changes in the exchange rate recalculating your USD values? You may wish to look at having effective dates for those (rare) occasions when "the economic gods" see fit to shake up the apple cart. You could then build your query to pick up the GRN date and select the Exchange Rate that covers the GRN period. HTH
    Gre

  12. #12
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exchange Rates (A2K)

    Thank you for raining on my parade and I really mean that in the best and kindest sense. I'd rather be sitting with egg on my face immediately as opposed to down the line when the horror of the exchange rate changes raised their ugly heads. By the way, the apple cart gets tipped on an almost daily basis, which I suppose is one way to make applesauce.

    Now back to my "New" problem. Albeit condemned by many, do you think that if I were to "write back" the rate on the day that the record is created, that I could then use the "written back" rate for later reporting/viewing? Basically and honestly, I'm not at all sure how to implement your just-in-time-to-save-me suggestion.

    Many thanks,
    Cheers,
    Andy

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

    Re: Exchange Rates (A2K)

    One possible solution would be to add two date/time fields to the exchange rate table: a start date and an end date. Each time the exchange rate changes, you'd add a record to the table.

    Another solution would be to keep the exchange rate table as is; it would always contain the current rate. You'd add an exchange rate field in the table or tables where you need it for calculations. You'd set the value of the field equal to the current rate from the exchange rate table when the record is created.

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exchange Rates (A2K)

    You can do two things:
    - Store the rate in the record
    - Store the calculated price (CndValue).
    This depending on what you want to keep as archive.
    Francois

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exchange Rates (A2K)

    As things stand, I'm not wholly clear as to how the client is going to pull data out of your application, but - experience being my best guide - I should think you're going to have to be prepared to have it used for things you'd never really intended!

    What I personally had in mind was a Start Date for each Exchange Rate. An End Date could be generated automatically each time you put in a new Start Date. You could then run a conversion Query which would use (air code) WHERE ([Goods Received Note date] >= StartDate) AND ([Goods Received Note date] <= EndDate) . (For accounting purposes, Inventory often has to be converted at both Period End (Balance Sheet) and Average (Income Statement) rates.)

    With all due respect for Hans, simplicity and preventing Table "corruption", having only the current rate in the DB is probably going to leave the Production/Inventory folks out on a limb when comparing their records with reports from the Finance folks.

    HTH
    Gre

Page 1 of 2 12 LastLast

Posting Permissions

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