Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Memo field and data filtering

We design tables carefully to organize information properly so that we can retrieve them easily through search, filter etc.  If you look at the Employees table in the Northwind.mdb sample database you can see that even an employee's name is split into three parts (Title, First Name & Last Name) and stored into three different fields so that we can work with each piece of information separately.  The name fields are defined to a specific length taking into consideration of the size of the source information.

When it comes to recording of employees' qualifications or work experience we cannot define the field size to a specific length because the length of information may vary from case to case.  This is where we think of the Memo field type.  Memo field is a free form text field where you can record descriptive information of various lengths.

When we want to extract information for reports or views we never think of using the Memo field contents because it has information in an unpredictable form and considered difficult to work with besides displaying/printing it's contents.

Even though Memo Field has only limited flexibility in data filter operations, we can filter records based on specific text spread all over different locations in the memo field.

We can try few examples with Memo Field data from the Employees Table of Northwind.mdb sample database.

  1. Import the Employees Table from the sample database C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb.
  2. Open the Employees Table in datasheet view.
  3. Move the horizontal scrollbar at the bottom to the right so that the Notes Memo Field contents are visible to you.
  4. Point the mouse at the left border of the table at the inter-section of the two rows so that the mouse pointer turns into a cross.
  5. Click and drag down to increase the row size bigger so that the Notes field contents can be viewed properly.

    Now, if you look at the qualification information of each employee record you can see that most of them have a BA degree but the text "BA" is not positioned at a specific location in the Memo Field. If you want to filter all employees records with BA degree, how do we do it?

    Let us do it directly on the datasheet view first, before we consider writing a Query to filter data based on text in the Memo Field.

  6. Highlight the letters BA in any one of the records and Right-click on the highlighted text.

    A shortcut menu is displayed and the suggested options are for filtering data from the Memo Field are Contains "BA" or Does Not Contain "BA".

  7. Click on the Contains "BA" option to filter the records with the text "BA" appearing anywhere within the memo field.

If you want to filter records this way for printing a Report then we must create Queries to filter data based on text in Memo Field.  You can use the Like Operator combined with AND, OR logical operators.

Copy and the paste the following SQL Strings into the SQL Editing Window of new Queries and save them with the suggested names:

Query Name:  Employee_BAQ

SELECT Employees.LastName, Employees.FirstName, Employees.Notes
FROM Employees
WHERE (((Employees.Notes) Like "*BA*"));

The output of this Query will include a record of an employee with MBA Degree too, because the text 'BA' in MBA. If you want exclude this record then modify the criteria with a space immediately after the first asterisk like '* BA*'.

Query Name:   Employee_BA_BSCQ

SELECT Employees.LastName, Employees.FirstName, Employees.Notes
FROM Employees
WHERE (((Employees.Notes) Like "* BA*")) OR (((Employees.Notes) Like "*BSC*"));

The above query gives the example of the usage of the logical operator OR to filter data of employees with graduation in BA or BSC.

Query Name:   Employee_BA_BSCQ

SELECT Employees.LastName, Employees.FirstName, Employees.Notes
FROM Employees
WHERE (((Employees.Notes) Like "* BA*" And (Employees.Notes) Like "*psychology*"));

The above example shows the usage of the logical operator AND and filters the records of the employees with graduation in BA in Psychology.


Lively Controls on Form

We always concentrate on timely completion of a Project and to deliver it to the Users as quickly as possible.  When I am pressurized to complete a particular project I will try to complete it as quickly as possible and deliver it to the User.  But all the time I would like to have a second look at the overall design and appearance of the Forms and Reports in a relaxed atmosphere and bring in some improvements with whatever new tricks that I can think of at that point of time.

In my past experience on these kind of improvements I have been always rewarded with appreciation from the Users. Their responses on these little things that I have incorporated into the design always encouraged me to look for something different next time.  Forms are the main component of any Application that catches the eye of the Customers besides nicely formatted Reports and impressive Graph Charts.

Forms have a special place in the minds of the User. It should be pleasing to look at and user friendly to work with.  Once the initial pressure of designing the main components of an application is over and if you have enough time to have a second look at the main forms' design you can make use of your creative ideas and pull little tricks on the Form that will do wonders. All the controls we draw on the Microsoft Access Forms remains stationery for ever.  If we can put some action of movement on some of the controls, without overdoing it, it will definitely have some positive impact on the Customers.

We have learned several animation tricks on controls in the past and If you would like to take a second look at then their links are given below:

  1. Command Button Animation
  2. Reminder Ticker Form
  3. Startup Screen Design
  4. Animated Floating Calendar
  5. Wave Shaped Reminder Ticker
  6. Command Button Animation-2
  7. Animating Label on Search Success
  8. Run Slide Show when Form is Idle
  9. Label Animation Style-1
  10. Label Animation Style-2
  11. Label Animation Variant
  12. Label Animation Zoom in Style
  13. Label Animation in Colors
  14. Label Animation Zoom-out Fade
  15. Digital Clock on Main Switchboard

Here, we will do a simple trick on an Option Group Control to respond, when the mouse moves over it for selection of options.  The trick is simple, when the Option Group control is created we will give it a Raised Style design.  When the mouse moves over the Option Group control the Style will change to Sunken.  When the mouse moves away from the Option Group control it will go back to its earlier Raised state.  Repetition of this action gives a lively look to the Option Group control.

  1. Open a new Form in Design View.
  2. Click on the Wizard Button (with the magic wand icon) on the Toolbox to select it, if it is not already in selected state.
  3. Select the Option Group control and draw it on the detail section of the form.
  4. Enter the options as shown on the design below by pressing Tab key after each option to advance to the next row. You may enter any Text as options as you like.
  5. Complete the design by selecting the Finish Command Button.
  6. Move the attached label above and make it as wide as the Option Group Control and change its Caption to Main Menu.
  7. Move the attached child label above and make its width same as the Option Group control and align it to left edge. 
  8. Change the Caption of the label to Main Menu.
  9. Click on the Option Group to select it and display its Property Sheet (View - -> Properties or press ALT+Enter).
  10. Change the following Property Values as given below:
    • Name = Frame0
    • Back Style = Normal
    • Special Effect = Raised
  11. Display the VBA Code Module of the Form (View - ->Code or click on the Module Toolbar Button or press ALT+F11).
  12. Copy and Paste the following VBA Code into the VBA Module of the Form:
    Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, x As Single, Y As Single)
       If Me.Frame0.SpecialEffect <> 1 Then
           Me.Frame0.SpecialEffect = 1
       End If
    End Sub
    Private Sub Frame0_MouseMove(Button As Integer, Shift As Integer, x As Single, Y As Single)
       If Me.Frame0.SpecialEffect <> 2 Then
           Me.Frame0.SpecialEffect = 2
       End If
    End Sub
  13. Save and close the Form.
  14. Open the Form in normal view.  You will find the Option Group control is in Raised style.
  15. Move the mouse over the control and it will enter into Sunken state.
  16. Move the Mouse pointer away from the control and it will restore back to Raised state.
  17. Repeat this action in quick succession and you can see how the Option Group control responds to this simple action, otherwise the control remains rigid for ever.
Technorati Tags: ,

Create your own color palette

We have learned Binary, Octal & Hexadecimal Numbering Systems and learned few simple rules by which we can devise new numbering systems with any Base value, provided the user knows about it and can decipher the new number. 

Last week we have worked with a Form to enter Decimal Numbers and get it converted into Binary as well as to display the RGB Color for that number.

If you would like to learn and understand Binary, Octal and Hexadecimal Numbering Systems then check the following Posts:

  1. Learn Binary Numbering System
  2. Learn Binary Numbering System-2
  3. Octal Numbering System
  4. Hexadecimal Numbering System
  5. Colors 24 bits and binary conversion

This week, we will work with a Form based Utility that is more useful to you at design time of a new Project.  You can create your own 24 bit custom colors visually and save up to 15 colors in a Color Palette on the Form. You can pick any of this colors and apply it on Form background or on other Control's Foreground, Background or Border Color Properties at design time.

An image of the Employees Form in Design View with the Form's Header Section Property Sheet along with the Color Palette Form is given below for reference:

A new custom color is created on the color palette Form (see the big rectangle) and applied it on the Employees Form Header Section Background, by setting the color value in the Back Color Property.

Click on the image to enlarge it.  The Employees Form is in Design View, its Header Section is in selected state and its Property Sheet with the Back Color Property is visible.  When you click on one of the grid of 15 Color Boxes (these you can create yourself and save it in each box of the color palette, over-writing the existing ones) will display its corresponding color number in the Text Box above the Color Palette with RGB Color label.  You can Copy (Ctrl+C) and Paste (Ctrl+V) into the Property Sheet for Value: Back Color, Fore Color or Border Color, as the case may be, to set the Color of Form Background or Control Colors.

If you are using MS-Access2007 the Color Values in the above Properties will be shown in Hexadecimal Form with the hash symbol like #4E61BC.  But you can paste the Decimal Color Value there and it will automatically convert into Hexadecimal form.  Since, we have learned Hexadecimal Numbering System I want to draw your attention to a small problem in Microsoft Access2007.

Let us take a sample Color Number in Decimal: 12345678 for the Form Header or Detail Section Background and paste it (or type it) into the Property Sheet and press Enter key. It will be converted into Hexadecimal Number like #4E61BC and the Form Background will be filled with a Dark Blue Color.  Take note of the first two digits and last two digits of Hexadecimal Number shown in blue and red colors.

  1. Now, open the VBA Editing Window (Alt+F11) and display the Debug window (Ctrl+G)
  2. Type ? HEX(12345678) and press Enter key to convert the Decimal Number 12345678 into Hexadecimal Number. The Question mark is the Print Command.
  3. The result will be BC614E and if you paste this value into the Property Sheet as #BC614E it will be accepted as it is and the color displayed will be Dark Red Color instead of Dark Blue.  Compare this number with the earlier number shown in blue and red colors.  MS-Access2007 converts the Color Decimal Number into Hexadecimal form and switches the right-most two Hexadecimal digits with the left-most two digits because it handles the color values (Red and Blue positions) differently.
  4. Suppose you have a Color Value in Hexadecimal form like 614E (24910 decimal) while pasting the value into the Property sheet you must change it to #4E6100 to get the Color equal to the decimal value 24910.  The color number is always expressed in 6 digit hexadecimal number.
  5. The safest method is to Paste the decimal value into the Color Property and let MS-Access2007 take care of the conversion part.

Now, we will look at the simple trick of designing new Colors (logically you have over 16 Million 24 Bit Colors to work with) and saving it on the Color palette for later use. 

You can download a database with the above Form with the Programs from a link at the bottom of this Article, so that you can Import this Form into your new Projects, design your own colors and use them on your Forms or Controls.

You can click on the above image to enlarge it to view the controls properly.  Let us examine the controls on the Form.

  1. There are three Scrollbar controls to input the integer values from 0 - 255 for Red, Green and Blue colors (primary colors) by moving the Scrollbar's slider controls to the right to increase the value, or left to decrease the value, of each color. If you click on the arrow marks on the right/left edges of the scrollbars then the values will increase by 1 on each click or if you click and hold the control then the value will increase/decrease rapidly and the color graph also will change with it.  You can watch the large rectangle at the bottom right how the new color is formed by changing the colors with the Red, Green and Blue Scrollbars.
  2. The Bar Graph at the right of the scrollbars gives a visual indication of the variation of color values, which mixes together to form the new color, and the decimal values appear in the Text Boxes at the left, with the labels Red, Green and Blue.
  3. The big rectangle label control, at the bottom right of the Form, displays the new color created by setting the values in the scrollbar control.
  4. The Text Box with the Label RGB Color displays the RGB Color Number.
  5. You can highlight the number in the RGB Color Text Box and Copy (Ctrl+C) the Number and Paste (Ctrl+V) it in the Form's/Control's Back Color, Fore Color or Border Color Property to set the Color there.
  6. If you would like to preserve this newly created color then you can Click on the big rectangle, to pick the Color from there, then click on one of the 15 color boxes shown at the left to store that color there for later use.
  7. If you want to pick a color from one of the saved colors then click on it.  The selected color number will appear in the RGB Color Text Box above.  You can Copy and Paste it in the Color Property of the Form or Control.


  1. You have the RGB Color Number, but you want to modify it to your liking then Copy and Paste that color decimal number into the RGB Color Text Box (or type it in) and press the Enter Key.  The color will appear in the big rectangle and the Red, Green and Blue Text Boxes will display their corresponding color values. The Bar Chart will give you a visual clue of each color at a glance.  You may then move the slider of the Scrollbar Controls to modify the color.
  2. If you are using MS-Access2007 then you must convert the Hexadecimal Number into Decimal Number by typing ? &hXXxxXX in the Debug Window, where xX stands for Hexadecimal digits. Don't forget to interchange the rightmost two digits with the leftmost two digits of the six digit hexadecimal number.  You will get the result in Decimal Number form and then you can copy it into the RGB Color Text Box to modify.
  3. You can directly enter integer values (Ranging from 0 to 255) in Red, Green and Blue Text Boxes given at the left of the Scrollbar Controls to create a new color.
Technorati Tags: ,,

Download Demo RGBColor.zip


Colors 24 Bits and Binary Conversion

If you were following the last four Articles on Numbering Systems (Decimal, Binary, Octal and Hexadecimal) then here is something for you to play with.  Before that following is the list of Links of those Articles. It is better if you take a look at them before continuing. You will be in a better position to understand it, appreciate the usefulness and can enjoy working with its methods.

  1. Learn Binary Numbering system
  2. Learn Binary Numbering system-2
  3. Octal Numbering System
  4. Hexadecimal Numbering System

The above is an image of a MS-Access Form where you can enter a Decimal Number, ranging between 1 and 16777215 (equal to a maximum of 24 Bit Binary Value) and get it converted into Binary Number.  At the same time the number entered into the Text Box will be used for generating the RGB Color that will be displayed at the top color band, where the binary bit positional sequence numbers are shown. Logically you can display a total of over sixteen million colors on the top color band on the form.

If you look closely on the image above, you can see that the value 65535 is entered into the text box and the Binary digits 1111111111111111 are appearing in the grey band above the Text Box.  The decimal value equal to each bit position is in red font color in the labels arranged vertically above the binary digits. If you add up all those red colored values together you will get the result equal to the value in the text box.

Binary bit number 0 to 7 (8 bits) values give the Red Color and bit number 8 to 15 (8 bits) values form the Green Color.  When Red and Green Colors are mixed you will get Yellow Color shown above the Red,Green & Blue Color bands.

Let us make little modification to the above sample color by subtracting some bit values and see how the color changes on the top color band.  Click on the labels with the value 4096 and 64 one by one.  These values are moved into the Text Box and will be subtracted from 65535 (the sample number already appearing in there). The color of the labels changed back to its default color black.  Click on the Command Button with the label Convert. The result value (RGB Number) is appearing in blue color below the text box and the RGB Color of that number is now showing on the top color band. See the image given below:

If you want to add some more color (Red, Green or Blue) to the existing color number you can click on the labels with numbers in black color, to mix the color above those labels, and click Convert Command Button to show the result on the top band.  The intensity of the color added depends on the magnitude of the number selected from within the respective color band. The color of the label clicked will change to red indicating that the value is added to the color number.

If you click on the labels with red colored font again it will change to black indicating that those values will be subtracted from the color values.  You must click on the Convert Command Button to evaluate the final color value and display the color on the top color band. Every time the Binary digits of those values will also appear in the grey band above the Text Box as well.

To reset everything back to default setting click on the Reset Command Button.

You can use one of the three methods given below or a mix of all the three to enter the number into the Text Box:

  1. Enter a Decimal Number into the Text Box and Click on the Convert Command Button.
  2. Enter a valid expression into the Text Box like 2^10+2^5+5*25+1638 and click Convert Command Button.
  3. Click on the labels showing the binary digit values underneath Red, Green & Blue color bands to pick those color numbers and to mix them together to create a new color. Each value clicked will be added into the Text Box in the form of an expression. Each Label clicked will change its font color to Red indicating it is added to the color number and if any of those labels are clicked again that color value will be subtracted and the label font color will change back to default black.  When done, click Convert Command Button.
  4. You can use a mix of all the three methods given above to input a valid expression to arrive at a valid value between 1 and 16777215 (16777215 = 256 Blue * 256 Green * 256 Red -1)

Get the result in three ways:

  1. The RGB Color equal to the number selected is displayed on the top color band.
  2. The Binary Number.
  3. The RGB Color Number of the top color band.

You can use this Color Number while designing your Forms or Controls to set the Background Color, Foreground Color or Border Color on the Property Sheet of the Form or Control.  If you are a Web Designer you can convert the Color Number into Hexadecimal format and use it on Style Sheets.  You can call the Function HEX$(Decimal Number) in Debug Window to convert it into Hexadecimal value.  Use the color value in 24 bit format like #0000FF for decimal color value 255.

Enter the number 16777215 in the Text Box and click Convert Command Button to change the top color band to white.

You can download a database with the above Form and Programs by clicking on the Download link below:

Technorati Tags: ,

Download Demo Binary.zip



Your email address:

Delivered by FeedBurner


Infolinks Text Ads

Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...


Blog Archive

Recent Posts