Customize the Office Quick Access Toolbar

The Quick Access Toolbar (QAT) was Microsoft’s attempt to appease users of Office 2003 for the replacement of the menus/toolbars with the Ribbon that debuted in Office 2007. The QAT allowed users to add their favorite command shortcuts to one location rather than having to search the ribbon for commands that were no longer in the expected location. The QAT is the series of icons that are (by default) displayed above the tabs of the Office ribbon. The default QAT for Word 2010 is shown below.


To add the commands that you need to the QAT, click the drop-down arrow at the right end of the QAT and select More Commands from the menu. NOTE: Although our example is using Word, the procedure is the same for all Office applications that have the QAT.


This will open the Word Options dialog box as seen in the next image. NOTE: this post uses images from Office 2010. The dialog boxes look slightly different in Office 2007.


The left drop-down allows you to narrow the area from which you wish to choose a command. Use All Commands for a comprehensive list.


The right drop-down allows you to customize the QAT for all documents, or just the current one. This could be very useful if a document that you use on a regular basis requires special commands.


The basic procedure is to select a command from the left pane and move it to the right using the Add >> button. Do not to forget to add the Separator to add vertical bars to the QAT. Use the << Remove button to remove commands from the QAT. The up/down arrows along the right side of the dialog box can be used to arrange the icons left-to-right.


The QAT will be look like the following after clicking the OK button on the previous dialog box.


You can go through the procedure again to make any additional modifications. You can also erase all of your customizations and start from scratch if you click the Reset button on the options dialog box.




View VBA Consultants profile on LinkedIn

Copyright 2013 – VBA Consultants Ltd

Microsoft Access: Number vs. Currency Fields

In a prior post we explained the difference between the text and memo data types. This post will cover the topic of why you should choose a currency data type rather than the number data type.

The currency data type is used to only store numerals – no text characters. It is accurate to fifteen digits to the left of the decimal point and four digits to the right of the decimal point. (Since fifteen digits equates to trillions you could use Access to track the federal debt.)

The number data types are also used to store numerals – no text characters. The accuracy of the number data type is based on the chosen field size.

A field size of byte, integer, or long integer allow whole numbers only – no decimal points. A byte allows positive values from 0-255. An integer can accept positive or negative values from -32768 to 32767 while the long integer field size accepts values from -2147483648 to 2147483647 (approximately 2.1 billion). If you need to store numbers with decimals you have to choose the decimal, single, or double field size. These field sizes accept both positive and negative numbers, and the size of the numbers is expressed in either powers of ten (10^38-1) or scientific notation (3.402823E38).

Due to the internal mechanics of how computers store large numbers, a computer may consider 1.00000000000001 to be equal to 1.00000000000002 although we can see that these two numbers are not exactly equal. Although the difference is immaterial you can never be 100% sure how Access would use or compare these numbers.

The currency data type was created to avoid the small differences that appear to the far right of the decimal point. This data type should be used to store financial data, or data that will be used in calculations, requiring a high degree of accuracy.

Microsoft Access: Text vs. Memo Fields

Database programs, including Microsoft Access, require you to choose a data type whenever you create a field in a table. This data type, along with a field size and input mask (where appropriate), are an attempt by the database program to block improper data from entering into the database.

One example would be the creation of a date field. This data type would not allow you to enter the word ‘month’ – you can only enter a date. Likewise, a number field created to store inventory counts would not allow the word ‘twenty’ only the numeral ‘20’.

Novice Access users are often confused about the very similar text and memo fields.

A text data type can hold a maximum of 255 alpha-numeric characters.

A memo data type can hold a maximum of 65,536 alpha-numeric characters if entered through the user interface (i.e. a form). It can store a maximum of one gigabyte’s worth of characters if the data is being entered programmatically.

The question is why would you choose the limited text field when you can assign a memo data type and store your great American classic in one field of one record? The answer, although simple, is important when you want to query or sort your data.

You cannot search, filter, or query a memo field. [Note: a memo field can be sorted, but Access only uses the first 255 characters of data.]

The memo data type is designed to hold data that although important, is not meant to be searched. The classical example of a memo field is to record comments, such as supervisor comments on an employee evaluation. You do not search the comments, you search for the employee (or supervisor) and then review the comments stored for that employee.

The text field should be used to store data that (a) will be used for searching, filtering, or querying and (b) will not be used for calculations.