Memo field and data filtering ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

When designing tables, we carefully organize information to make it easy to retrieve through searches, filters, and queries. For example, in the Employees table of the Northwind.mdb sample database, an employee’s name is split into three separate fields—Title, FirstName, and LastName—so that each piece of information can be managed individually. These fields are also defined with specific lengths, based on the size of the source data.

However, when recording details such as an employee’s qualifications or work experience, we cannot predict the length of the text. In such cases, the Memo field type is used. A memo field allows free-form text of varying lengths, making it ideal for storing descriptive information.

That said, memo fields are not often used directly in reports or queries because their contents are unstructured and more difficult to work with. Still, they do provide some flexibility in filtering records—for example, by searching for specific text that may appear anywhere within the field.

Let’s look at a few examples of working with memo field data from the Employees table in the Northwind.mdb sample database.

Prepare for a Trial Run.

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

If you review the qualification details stored in each employee record, you’ll notice that many employees hold a BA degree. However, the text “BA” does not appear in a fixed position within the memo field—it may occur anywhere in the description. So how can we filter all employee records that include a BA degree?

To begin, let’s try this directly in Datasheet View before moving on to writing a query that filters data based on text within a memo field.
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 for filtering data from the Memo Field are Contains "BA" or Does Not Contain "BA".
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 the text in the Memo Field. You can use the Like Operator with AND, OR logical operators.

Copy and 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 an MBA Degree, too, because of the text 'BA' in MBA. If you want to 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 is an example of the usage of the logical operator OR to filter data of employees with a 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 demonstrates the logical operator AND, and filters the records of the employees with a graduation in BA in Psychology.