Currency Data Type and Windows Region Settings

Locked lesson.

Upgrade

About this lesson

Change the format of a Currency field and define the currency symbol in Windows Region settings.

Exercise files

Download this lesson’s related exercise files.

Services_14__start.accdb
1.3 MB Services_14__CurrencyDataType.accdb
1.3 MB

Quick reference

Currency Data Type, Windows Region Settings

Application Terminology

Currency

Currency is the most accurate numeric data type in Access, that VBA can also handle, which stores decimal places.  When you need precision and have no more than 4 decimal places, choose the Currency data type. Currency can hold up to 15 digits before the decimal point, and 4 digits after the decimal point.

Currency is a fixed-point number that is displayed with the currency symbol specified in the Windows Region settings.

Currency can range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807, which means that you are not always guaranteed 15 places before the decimal point.  You ARE guaranteed to have 14.

The Format property can be used to not show a currency symbol for cases where you need the accuracy of this data type but what you are storing is not monetary.

Format

Use the Format property to change the way data is displayed.  If you are using the Currency data type for its accuracy but not storing financial information, a good choice for Format is Standard – and set the Decimal Places property too.

Windows Region Settings

The Windows Region Settings control how numbers and date/times are displayed.  Here, you can also define the currency symbol that will be used.

To quickly get to the Control Panel, press Win-X

This displays the following menu in the lower left:

From here, you can click Control Panel (notice other useful options on this menu), which enables you to set many things:

then choose Region:

 

Steps

Change Format of a Currency Field

  1. To change the Format of a currency field, go to the Design View of a Table
  2. Choose a Format from the drop-down in the lower pane.
  3. Change the number of decimals using the Decimal Places property.
     
  4. Alternately, you may enter a custom format code such as #,###

Change Currency Symbol for Windows

To change the Currency symbol in Windows:.

  1. Press Windows-X  to go to the Control Panel
  2. Choose Control Panel from the shortcut menu
  3. Choose Region.
  4. On the Formats tab, choose Additional settings ...
  5. Click on the Currency tab.
  6. The choices displayed in the dropdown for Currency symbol change based on what you have chosen for your Home location.
Login to download
  • 00:04 This is Access 2013, Lesson 14, and I am Crystal.
  • 00:10 Currency is a special data type.
  • 00:13 You might think it would fall under the Number classification
  • 00:17 with Long Integer and Double Precision but it doesn't.
  • 00:21 Currency is a number with accuracy and decimal places.
  • 00:27 How currency displays is affected by
  • 00:29 Windows Regional Settings (Win-X, Control Panel, Region)
  • 00:35 We will create and enter data for the Currency Data Type.
  • 00:39 Open the start version of the exercise file.
  • 00:43 Each of these lessons has an exercise folder
  • 00:47 with a start and final version of the database.
  • 00:50 If you follow along and do what I do, you will retain a lot more.
  • 00:56 You can start now.
  • 00:58 Go to the Design View of MyDataTypes.
  • 01:02 Create 2 fields with the Currency data type.
  • 01:06 Name the first field cur. Remove the Default Value of 0.
  • 01:11 Name the second field curComma and
  • 01:15 set the format to be Standard with 4 Decimal Places.
  • 01:20 Format should be set on Forms not in Tables.
  • 01:24 Just because we are covering Format
  • 01:27 does not mean it belongs here.
  • 01:30 Switch to DataSheet View.
  • 01:32 Hide the columns we created last time by clicking
  • 01:35 in each Column Header and choosing Hide Field.
  • 01:38 Or click in the column header for any column with a field
  • 01:43 and choose Unhide Fields to show a list
  • 01:46 with checkboxes to show or not show every field.
  • 01:52 Enter 123456789012345.1234 into both of the currency fields.
  • 02:04 The Currency data type carries 4 decimal places
  • 02:07 after the decimal point and 15 places before.
  • 02:12 Aside from 'decimal' which creates problems in Access,
  • 02:15 Currency is the most accurate numeric data type
  • 02:19 that has decimal places.
  • 02:22 If #s show up in a field instead of a value, that means the cell
  • 02:27 is not big enough to display the information.
  • 02:30 Unlike text, which simply gets chopped,
  • 02:33 a partial number will not display.
  • 02:37 Resize columns and rows.
  • 02:39 Like Excel, double-click to best-fit a column
  • 02:42 On the curComma field,
  • 02:44 choose Standard for the format from the Fields ribbon.
  • 02:48 The only difference between these 2 fields is the format.
  • 02:53 Internally, the data is the same.
  • 02:57 For this reason, sometimes you may choose Currency
  • 03:01 for data that needs accuracy and has decimal places.
  • 03:05 In these cases, you would not want
  • 03:07 the Currency symbol to be displayed.
  • 03:11 Change the Currency symbol in Windows by going to
  • 03:14 the Control Panel and choosing Region.
  • 03:17 On the Formats tab, choose Additional settings ...
  • 03:20 and click on the Currency tab.
  • 03:23 The choices displayed in the dropdown for Currency symbol
  • 03:26 change based on what you have chosen for your Home location.
  • 03:31 A quick way to the Control Panel is to press Windows-X
  • 03:36 and choose Control Panel from the shortcut menu
  • 03:40 I changed my Currency symbol to the British pound
  • 03:44 but my data is still showing up with a dollar sign.
  • 03:48 I go to the design view of the table
  • 03:50 and delete the format which got automatically supplied.
  • 03:54 I didn't put that format in, Access did.
  • 03:57 Switch back to datasheet view, and now my Currency field
  • 04:01 with no format specified shows with a British pound symbol.
  • 04:06 Consider the Currency data type when you need
  • 04:08 accurate numbers that are big or have decimal places
  • 04:12 even if what you are storing isn't money; directly money;
  • 04:16 perhaps production or inventory values.
  • 04:20 In the next lesson, we will talk about
  • 04:22 yes ... no ... checkboxes and the Boolean data type.

Lesson notes are only available for subscribers.

Gift this course