|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Convert Currency By Richard Rost 
5 years ago
How to Switch from USD to EUR, CAD, AUD, etc. In this video, I will show you how to convert currency from one type to another. We will learn how to convert US to Canadian dollars, but the same technique applies to Euros, Australian Dollars, etc. Rodney from Buffalo, New York (a Gold Member) asks: We are located near the Canadian border. We do a lot of business in Canada. Is there an easy way to display our prices in both USD and CAD without having to manually change them all the time, everywhere in the database? It would be nice to just go into a form in the database once a day and type in the current conversion rate. MembersMembers will learn how to create a currency conversion table so you can easily switch between multiple types of currency, and display more than one currency on your forms, reports, etc.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Links
Auto Update Rates!If you want to learn how to automatically update your currency conversion rates, I cover that in Access Developer 32. Currency SymbolsIf you want to use the actual currency symbols like $ € ¥ £ ¢ ﷼ ₽ etc. see this video: Currency Symbols
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, how do you change currency to dollars in access, microsoft access convery currency, round function, ccur function, convert to currency, bankers rounding, round, int, fix, dlookup, Currency Converter Template, Microsoft Access Currency Converter Database, convert values from one currency to another, calculate exchange rates
IntroIn this video, I will show you how to convert values between different currencies in your Microsoft Access database, such as US dollars to Canadian dollars, using a simple math formula, the DLookup function, and a settings table to store your conversion rate. We will cover best practices for storing prices, setting up a product table, creating a calculated query field to handle currency conversion, properly rounding results, and using a settings table for easy updates so your forms, queries, and reports always use the current rate.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.In today's video, I am going to teach you how to convert between multiple currencies in your Microsoft Access Database. I will show you how to switch from US dollars to Canadian dollars using simple math, a DLookup function, and a settings table. But this works with any type of currency, whether you are going to Mexican pesos, euros, Australian dollars, or whatever. Today's video comes from Rodney in Buffalo, New York, my old stomping grounds, one of my Gold Members. Rodney says we are located near the Canadian border. I know, I used to go over there all the time. We do a lot of business in Canada. Is there an easy way to display our prices in both US dollars and Canadian dollars without having to manually change them all the time everywhere in the database? It would be nice to just go into a form in the database once a day and type in the current conversion rate. Yes Rodney, converting between different currencies is just a matter of math. You find out the conversion rate, you do a little multiplication and there you go. There is the other price. But if you have got it in this query here and that form there and this report over here, it can be a pain. It would be nice to have one spot in the database, like a settings table for example, where you can put that number there, that currency conversion rate, and then the whole rest of the database just uses that to do the conversion. So let me show you how I would set this up. A couple of prerequisites before we get started. You are going to need to know how to use the DLookup function. That is how we are going to look up that conversion value once we save it in our table. So if you do not know how to use DLookup, go watch this video now. It is on my website. It is on my YouTube channel. Go watch it. You need to know how to use calculated query fields. That is where you take a value in a query and do something else to it and make a calculation on it and you get a whole new field. That is called a calculated query field. Someone recently asked me if you should do calculated fields in tables. No, tables are not for storing calculated values. Tables are for storing regular data. You do any calculations on that data in your queries or directly on your forms. But I like using query fields. Go watch both of those videos if you have not yet already and then come back. I will wait. Here I am in my TechHelp free template. This is a free download off my website. If you do not have a copy, go grab it. I am going to put a simple product table in here. So let's go to Create and then Table Design. Let's put in our ProductID. That will be our AutoNumber, the ProductName. That is a Short Text. Do not ever use just the word Name. That is a reserved word. Access does special things with the word Name. Let's do UnitPrice. That will be a currency value. I am going to assume if you are a US business, store your product prices in your database in US dollars and we will just convert them over to whatever other currencies you need. Let's save this as MyProductT, my product table, Primary Key, yes. Let's go and put some data in here. All right, what do we sell? We are selling Star Trek stuff. So we have got Phasers and let's say they are a hundred bucks a piece. We have got Photon Torpedoes. Those are 350. And let's say we have got a Shield Generator, i.e., Shield Generator, and that is 333.33. There is a reason why I am picking that number. Save it. Close it. Now let's make a query to convert that over to Canadian dollars. So let's create Query Design. I will bring in MyProductT. Close that. You can bring in the stars, you can see all the fields in them right over here. Let's make a field to do the math. Now we need to know what the conversion rate is. Let's go to the Google machine and I will just type in 1 USD to CAD. It will convert 1 USD to CAD. Right now the currency conversion rate is 1.27. That means 1 USD equals 1.27 CAD. It is pretty high right now. I have seen it as high as 1.35 and as low as 1.08. But I do not live up there anymore, so I am not dealing with Canadian dollars now. Let's put the calculation right down here. I am going to call this PriceCAD. It is going to be the UnitPrice times 1.27. Let's save this. I will call it MyProductQ, product query, and run it. There we go. Now, a couple of things. The reason why I picked this value here: look what it comes out to, 423.391. The first thing you might want to do is round that number off. We do not want any Superman 3 slash Office Space penny rounding problems. Do not want to have to look into money laundering. So let's round that number to two decimal places. Now, this is all on you whether you want to round it, chop it off, whatever. I have got a whole separate video. I will put it in the link section below that talks about the Round, Int, and Fix functions. They all do different things. I also have another video that talks about bankers rounding. Access does something called bankers rounding, where odd numbers get rounded one way, even numbers get rounded another. So I have got videos on all that stuff. But for today, in this video, we are just going to deal with simple rounding. If you care more about rounding and all that stuff, then go watch those videos. We are just going to Round this round to two decimal places. I will zoom in so you can see it better. There we go, nice and big. Round UnitPrice times 1.27, comma two. Now when I run it, there we go. Looks good. Finally, you can either format that as currency, or you can actually convert it to currency. There is another function you can use called CCur - C-C-U-R - convert it to currency. Now, when you run it, you get an actual currency value in there. By putting the calculation in here, you have got a query that you can use everywhere else now. So if you only want to put it in the query, then you can use this query in your other forms and reports, then that is fine. That is great. You really want to try to avoid putting this guy in multiple places. But let's say, hypothetically, you do that in several different places. You have to put that value. You have got some other form fields, you have got some report fields, and you cannot always rely on this ProductQ. I get it. So we can store this in a settings table. We will make a table over here called Setting. Then we will use DLookup to pull that value up wherever we happen to be. Let's create a table, Create Table Design. Now we are only going to have one record and one field in this table. You can add more fields if you want to later on. If you do multiple currency conversions, you can do that here too. Although I will cover that in more detail in the extended cut. For now, let's just put in here USDToCAD. That will be the conversion number. The value is going to be a Number. Make sure down here you change this to a Double. If it is just a Long Integer, you will not be able to put a fractional part in. I get that question a lot. People send me that question: "Hey, I am typing in 4.42 and it just puts in 4." Well, yeah, because you have to either use a currency value for that, or, if it is a number with a decimal point, you have to use Double. There are a couple of different types. I recommend you always stick with Double. Let's save this. That is the only field we are putting in here. Save this. I am going to call this MySettingT. It is a one-record table. Access wants to create a primary key? Say no. This is one of those rare instances where we do not need a key. We have got one value, one record. That is it. It is a quick place to store a value. Then let's put that value in here. USD to Canadian dollars: 1.27. That is it, just one record. Now, back to our query. Let's come over here and let's get that value. Let's call this the CADX, the Canadian dollar rate. We are going to say, I am going to zoom in so you can see this, Shift-F2. The Canadian dollar rate is going to be DLookup. What are we looking at? The USDToCAD field in the Setting table. I try to keep all my table names singular, by the way, so instead of SettingsT, it is SettingT. Normally with DLookup, you could put a criteria after this, but we only have one record, so that is all we need. Just get the USDToCAD value from that field, from the Setting table. That is it. Do not worry about what record it is, because there is only one record. There only should be one record. Hit OK. I am going to slide this to the left. You do not have to, but I like to put it here because this value is not going to rely on that. So right in here, we are going to replace that with the CADX. Right here, instead of putting that number there, CADX. That is what we looked up over here. That is our calculated value. Now you can use it over there. When I run this now, there we go. It pulls that up from the table and now calculates it here. The reason why we do this is because now you can use this technique everywhere in your database. If you have got form fields or report fields that you do not want to use this query for, you can just DLookup that value from the Setting table and use it in your calculations automatically. Then all you have to do, save changes, yes, is once a week, once a day, whenever you want, open up your Setting table. You can make a nice pretty little form for this if you want to. I am not going to bother. You know how to make simple forms. Just come in here. Let's say that tomorrow the rate changes. It is 1.28 now. Now, everywhere in your database where you have this query or where you are using this calculation, you get the new value in there. That is all you have to do. It is pretty straightforward, pretty simple. If you would like to learn more, in the extended cut for members, I will show you how to set up a table to track whatever currencies you want: Canadian dollars, New Zealand dollars, euros, Australia, whatever currencies you use, unlimited number of currencies. Then we will make a query where we can see all of them if you want to. For example, $100 here is 85 euros, etc. We will make a custom function in VBA so we just say "here's the dollar amount, here's which currency value I want, bring it back for me." Then we will put the preferred currency for each customer on their customer form. Ignore the beeps. Then we will add that currency that the customer wants to use onto their invoices. So all the prices will still show up as US dollars, and then at the very end we will say "in Canadian dollars for your convenience, here you go." That is all covered in the extended cut for members, fifteen minutes long. It is a pretty good one. Remember, Silver Members and up get access to all of my extended cut videos. Not just this one - all of them. There are like 200 of them now, getting close to it. Gold Members can actually download these TechHelp templates. How do you become a member? Click the Join button below the video. After you click the Join button, you will see a list of all the different types of membership levels that are available. Silver Members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold Members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault, where I keep tons of different functions that I use. Platinum Members get all the previous perks, plus access to my full Beginner courses, and some of my Expert courses. These are the full length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted. If you would like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It is over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just $1, and it is also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. Thanks for watching. I will see you next time. Bye. QuizQ1. What is the main purpose of the video tutorial?A. To teach how to create an Access database B. To demonstrate data import from Excel to Access C. To show how to convert between multiple currencies in Microsoft Access D. To explain relationships between Access tables Q2. Why is it recommended to store product prices in US dollars if you are a US business? A. Because Access only supports US dollar format B. To simplify conversion to other currencies as needed C. It is required by law for US businesses D. US dollars round automatically in Access Q3. What is the role of the settings table (e.g., MySettingT) in the database? A. To store all product information B. To keep a single place for the currency conversion rate C. To log user actions D. To track stock inventory Q4. Which Access function is used to retrieve a value, such as the currency conversion rate, from another table? A. DSum B. DAvg C. DLookup D. DLast Q5. What kind of field type should you use in the settings table to store a fractional conversion rate like 1.27? A. Short Text B. Long Integer C. Double D. Yes/No Q6. Why should you avoid creating calculated fields directly in tables? A. They take up more space B. Tables are for storing regular data; calculations should be in queries or forms C. Calculated fields are slower in tables D. Access does not support calculations Q7. How does DLookup help in the context of this video? A. It calculates the currency automatically B. It allows users to update prices directly C. It pulls the currency conversion rate from the settings table during calculations D. It converts all prices to Canadian dollars automatically Q8. What is the correct way to round a currency calculation to two decimal places in Access as shown in the video? A. Round(UnitPrice * ConversionRate, 3) B. Round(UnitPrice * ConversionRate, 1) C. Round(UnitPrice * ConversionRate, 2) D. Int(UnitPrice * ConversionRate) Q9. After setting up the DLookup function and the settings table, what do you need to do when the conversion rate changes? A. Update all queries manually B. Change the rate in the settings table, and all calculations update automatically C. Redesign all forms D. Delete the old conversion rate and make a new table Q10. What is the benefit of using a calculated field in a query for currency conversion? A. It stores the conversions permanently B. It allows dynamic updating based on the current conversion rate stored in the settings table C. It only works with US dollars D. It deletes the original prices Answers: 1-C; 2-B; 3-B; 4-C; 5-C; 6-B; 7-C; 8-C; 9-B; 10-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on how to convert between multiple currencies in your Microsoft Access database. I'll walk you through the process of switching from US dollars to Canadian dollars, using straightforward math, the DLookup function, and a settings table. While I will be using US and Canadian dollars as an example, this method is versatile and works with any currency - whether you're dealing with pesos, euros, or Australian dollars.This question actually came from a viewer who does business both in the United States and Canada and wanted an easy way to display prices in both US and Canadian dollars throughout their Access database. The goal is to avoid the tedious process of manually updating each spot where prices appear. Instead, it would be much simpler to type in the current exchange rate in just one place, perhaps once a day, and have that value update automatically everywhere else. When it comes to currency conversion, the foundation is simple math. You determine the current rate, multiply your values, and arrive at the new price. The challenge, however, is implementing this in such a way that you only have to change the conversion rate in one location, not in every single query, form, and report. To solve this, I recommend using a settings table to store the conversion rate. You can then use DLookup to retrieve that value wherever it's needed. Before getting started, there are a couple of important concepts to be familiar with. First, you should know how to use the DLookup function. This function will allow you to pull the conversion value from your settings table. If you haven't worked with DLookup before, there are resources available on my website and YouTube channel that cover it in detail. Second, you should understand how to create calculated query fields. These allow you to perform calculations in your queries and output new fields based on those calculations. It's important not to store calculated values in your tables - tables should only hold raw data. Calculations should be handled in queries or directly on your forms. This keeps your data model clean and your calculations dynamic. To get started, I begin with a simple product table. For this example, I've added an AutoNumber ProductID, a ProductName field (using a name that doesn't conflict with reserved words), and a UnitPrice field with currency data type. Imagine we're selling Star Trek themed items, such as Phasers, Photon Torpedoes, and Shield Generators, each with its own price in dollars. With the product data set up, I then create a query that can convert the prices from US dollars to Canadian dollars. The first step is to look up the current conversion rate, which, for the sake of example, let's say is 1.27. That means one US dollar equals 1.27 Canadian dollars. In the query, I add a calculated field that multiplies the UnitPrice by 1.27. This gives us the price in Canadian dollars. It's important to handle rounding at this stage to avoid any issues related to floating point arithmetic or penny-level discrepancies. Access provides different functions for rounding values, such as Round, Int, and Fix, and there's also something called bankers rounding to consider. But for this demonstration, a simple rounding to two decimal places will suffice. Once that's set up, you can also format the result as currency, or convert it to a currency data type using the CCur function, which ensures the output is displayed properly in forms and reports. Now, ideally, you don't want the conversion rate hard-coded in multiple places throughout your database. To make things more manageable, I recommend creating a settings table to store the conversion rate. This table might have just one field, such as USDToCAD, and only one record, holding the current rate. Be sure to use the Double data type for this field to allow for decimal points. It's a common mistake to use an Integer type and then wonder why Access chops off the decimals. With the settings table in place, you can now update the conversion rate in just one spot. Anywhere you need the conversion rate, such as in a query, you simply use DLookup to grab the value from the settings table. This approach ensures that any part of your database that needs the current exchange rate will always use the most recent value. This method streamlines the process for everyone who regularly deals with fluctuating exchange rates. Whether you need to update the rate daily, weekly, or as needed, it takes only a moment to change the value in the settings table (or through a simple form if you choose to make one), and the rest of your database will follow suit. For those interested in expanding on this, in the Extended Cut for members, I show how to track multiple currencies at once. You can build out the settings table to include as many currencies as you want, create queries to see different conversions side by side, and even develop a VBA function to handle conversions automatically. Additionally, I cover setting a preferred currency for each customer, reflecting those preferences in invoices, and displaying dual currency values for convenience. Extended Cut videos like this one are available for Silver Members and above. Gold Members also get access to all the sample databases, while Platinum Members have access to complete beginner and expert courses for Access and other Microsoft Office applications. If you would like to learn more, including step-by-step instructions and demonstrations for everything discussed here, you can find a complete video tutorial on my website at the link below. Live long and prosper, my friends. Topic ListCreating a product table with currency valuesEntering sample data in the product table Building a query to convert currency values Using a static conversion rate in a query calculation Rounding calculated currency values to two decimals Applying the CCur function to format as currency Creating a settings table to store the conversion rate Setting the correct field type for decimal conversion rates Using DLookup to retrieve the conversion rate from the settings table Referencing the DLookup result in query calculations Updating the settings table to reflect new exchange rates Utilizing the settings table for centralized currency management |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access how do you change currency to dollars in access, microsoft access convery currency, round function, ccur function, convert to currency, bankers rounding, round, int, fix, dlookup, Currency Converter Template, Microsoft Access Currency Conv PermaLink Convert Currency in Microsoft Access |
