Using the LIKE Operator and Wildcards to Match Patterns Between Strings

When we enter a criteria for a text field in an Access Query, the results can be a little limiting when we use the equality operator (ie = ).  So, for example, if we have a list of employees and we want to search for all Sales Representatives, we could use ="Sales Representative" as the criteria.  This would correctly produce a list of employees containing this exact value in the Position field.  This is all fine and good.  However, what if we wanted a list of all employees that have the word sales appearing at the start of this field?  Take a look at this screen shot of the table in question:

Figure 1: The Employees Table

As you can see, there are three different job titles beginning with the word Sales - there is Sales Manager, Sales Representative, and Sales Assistant.  Despite this fact,  if we were to enter = "sales" as the criteria, no records would be returned in the query results.  This is because it is not an exact match for any job title.

This is where the LIKE operator comes in handy.  The LIKE operator is used in conjunction with one of the Access Wildcard symbols to compare a pattern between two strings.  So instead of entering the criteria = "sales" we could enter LIKE "sales*".

Figure 2: Query Criteria using the LIKE operator.

This latter criteria would return all records beginning with the word "sales".  So any word letter or character coming after "sales" is ignored.  As such, our query now returns records containing any of the three above mentioned job titles in the Position field.

Figure 3:Query results returning all three job titles
beginning with the word "sales"... 

The type and position of Wildcard is crucial in all this.  We used the * wildcard symbol indicating that it is representing any number of characters (including zero). This is in contrast to the ? wildcard symbol which just represents a single character (eg LIKE "Sales Representativ?" to return "Sales Representative"), or the # wildcard symbol representing a single digit from 0 to 9 (eg LIKE "Person#" to return "Person1".  We could also specify a range of characters between square brackets - for example LIKE "Person[1-5]" would return the string "Person1", or "Person2"etc, but not "Person6" or above.  

These wildcard symbols can appear anywhere in the search string.  We placed ours after at the end of the search string to return any string beginning with "Sales ...".  Had we wanted to return any job title ending in "... Assistant", we would have placed the wildcard at the beginning of the search string ie LIKE "*Assistant" . There are even scenarios where a wildcard may be used in a specific place in the middle of a string too.  Try experimenting with all this - it's the best way to learn!

Append Queries: Automatically Append Data from One Table to Another

Append Queries are really useful when you acquire a new table full of data, and want to add it to an existing table in your database.  This scenario may arise, for example, if your company purchases a mailing list, and you are asked to add the new names to those you already have stored.  Rather than having to retype the new names by hand, an append query can be set up to copy the data from the new table into that which is already in use.

I have created an exercise to demonstrate how this process works.  Lets begin by looking at the two tables we are going to be working with:

Figure 1: tblContacts - the existing table consisting
of  ID (autonumber), FirstName (text) and Surname (text).

Figure 2: tblMoreNames - new data to be appended, consisting of
CustomerId (autonumber), Title (text), Initials (text), and LastName (text).
Notice the fields names are somwhat different and there is also a
risk of duplicating the ID fields from both tables.
More about this soon.

As you can see, figure 1 shows tblContacts, our existing table containing 15 records.  We are going to create an append query to add the 10 new records from tblMoreNames to the data already stored in this table.  You may have noticed that the field names contained in both tables are slightly different, and tblMoreNames has a Title field which tblContacts does not.  You may also have spotted that there is a risk of us attempting to append duplicate data from the CustomerID field of tblMoreNames.  Access would prevent this because the ID field in tblContacts is a Primary Key, so needs to be a unique value.   The simple solution to this, is for us not to include the customerId field (or anyother incompatible field)  in the append query.  As such, we shall just append data from the Initials and LastName fields of tblMoreNames to the FirstName and Surname fields of tblContacts.  Although the field names are slightly different, the data they contain is compatible, and for the purposes of this exercise, it is ok for us to append an Initial in place of a FirstName - the idea here is to demonstrate that the field names do not have to match exactly.

Creating an Append Query

Here is the procedure for creating the append query:

1. Click the QUERY DESIGN icon (located in the OTHER group of the CREATE ribbon).   The QUERY DESIGN window then opens along with the SHOW TABLE dialog form.   
2. The next step is to add tblMoreNames to the QUERY DESIGN window.  Do this by clicking ADD in the SHOW TABLE dialog form.  Notice it is the table containing the data to be appended that we have selected.
3. Click the APPEND icon from the QUERY TYPE group of the DESIGN ribbon.  As you do this, you will see the APPEND dialog box open.
4. You are now asked to select the name of the original table to which the new data is to be appended. So select tblContacts from the drop down list.  
5. You are also asked whether this table is stored in the current database or in an external database. In this exercise both tables are stored in the current database.  This is the default button displayed in the option group, so there should not be any need to change it.
6. Click OK to close the dialog box.
7. Next we are going to select the fields from tblMoreNames to be appended. To do this drag and drop the Initials and LastName fields from the table (in the top half of the window) down onto the design grid.
8. Next we are going to tell Access which fields the data from Initials and Lastname will be appended to.  To do this go down to the APPEND TO row of the design grid (see figure 3 below), and select FirstName in the Initials column, and Surname in the LastName Column.

   

   Figure 3: The Query Design Grid.

9. We could add query criteria at this stage, but this particular exercise does not require any.  If we did, however, this is added in the CRITERIA row just like it is with a select query. 
10. If you want to view the data that is going to be appended, click the VIEW icon from the RESULTS group of the DESIGN ribbon.  It is especially important to do this if any if any criteria is applied in step 9 above.
11. Once you are satisfied the correct data is going to be appended, click the RUN icon, again from the RESULTS group of the DESIGN ribbon.
12. A dialog box opens informing us that 10 rows are going to be appended, and asking us to confirm that we want to go ahead with this operation.  Click YES to complete.

To see the result of our Append Query, re-open tblContacts.  

Figure 4: tblContacts after the
Append Query has been run.

We can now see the new records appended to the end of our original data.  Notice each of the newly appended records has been automatically allocated a unique ID number.  This, of course, is because the ID field in the original table had been set to the AUTONUMBER datatype.  

Using VBA to Filter Report Results

Earlier this week I was asked how to filter the results of a report using a criteria selected from three Combo Boxes on a search form.  You can see Marwa's question posted on my Access Tutorial Facebook Page (Tuesday 10th January 2012).  The solution I suggested involved creating a Parameter Query with three separate criteria referencing the values contained in the Combo Boxes on the Search Form.  You can see the full response I gave in my comment below her question.

By co-incidence I have also been working on a similar task in my work as an Access Developer.  One of the projects I am currently working on is a Journal Database which uses descriptive tags to categorise journal entries.   So, for example, if users made an entry about their progress learning Microsoft Access, they might use tags such as  "Database Development", "Reports", and "Filters" to categorise their entry.

I wanted to create an quick way for users to search their journal by tag name, so that all records categorised by a given tag may be extracted and displayed in a report.  To do this I created a really basic search form that consisted of just one unbound Combo Box.

The ROW SOURCE for the Combo Box is a table containing all of the tag names used in the database.  The idea is that the user selects a tag from the drop down list, thereby triggering a block of VBA code in order to open the report filtered by the selected tag name.  The screen shot below shows the report filtered by the tag name "ADO.Net":

The underlying table structure for this report is based on a query with
three tables from a many to many relationship.  There is a table for the Entries, and a
separate table for Tags.  There is also a junction table to store each instance of
a tag used in any particular Entry.  As such a journal entry can be related to
many tags records, and any tag record can be related to many Entries.

So how does this work?  When the user selects a tag name from the drop down list, Access fires the Combo Box AFTER UPDATE event.  This in turn executes the followingVBA code that I wrote for this event:

Private Sub comTag_AfterUpdate()

    Dim varSQLWhere As String
    varSQLWhere = "tagId = '" & Me!comTag & "'"
    DoCmd.Close acForm, Me.Name
    DoCmd.OpenReport "rptTagSearch", acViewReport, , varSQLWhere

End Sub
NB for sake of clarity, I have removed all code related to error handling. 

The code begins by defining a string type variable called varSQLWhere - this is going to contain the code for an SQL WHERE clause (minus the WHERE keyword).  Next I set the value of this variable to "tagId = '" & Me!comTag & "'".  TagId is the name of the primary key of the table containing all of the tag names.  In fact, tblTags only contains this one field.  Me!comTag is a reference to the name of the Combo box used on the search form.  Note the manner in which it has been concatenated into the string variable. Due to fact that the value contained in the combo box is itself a string, I have had to build two single quotation makes into the varSQLWhere string.  So for example, if the user had selected the tag name 'Visual Studio' in the Combo Box, the contents of the string variable would be "tagId = 'Visual Studio'" .

The next line of code closes the search form so it does not get in the way when the form opens.

The penultimate line of code is the DoCmd.OpenReport method.  This not only opens our report, but also passes our varSQLWhere variable as one of the method's parameters (the WhereCondition). When this is passed, only records matching our WhereCondition are displayed in the report results.  As such, our report effectively filters our report results by the tag name selected by the user in the search form combo box.

It's not too difficult to apply this type of solution to the application that Marwa is building. The only real difference is that her Database search form consists of three combo boxes.  This means that she would also need to use a  Command Button ONCLICK event to start the search rather than relying on one of the Combo Box AfterUpdate Events.  Then she would need to construct the varSQLWhere variable from all three combo box values.  This would look something like:

varSQLWhere = "fld1 = '" & Me!Combo1 & "' AND fld2 = '" & Me!Combo2 & "' AND fld3 = '" & Me!Combo3 & "'"

So its slightly more complicated, but the same principles apply.

Finding out Maximum and Minimum Value's - The DMax and DMin Functions

So we have a table of sales figures for 2011.  Suppose we want to find out the largest and smallest sales value for the month of May. Access has two functions ideal for this task - these are the DMax and DMin functions.

DMax and DMin basically examine a domain of values, returning the largest and smallest value respectively.  The syntax for using these functions follow the same format as the other domain related functions such as DLookUp and DSum which I blogged about last year.  As such, we pass parameters for field name, the domain from which the field belongs (the table or query, for example), and an optional criteria if we are just interested in particular records (such as May sales for example).

Lets take a look at the syntax for DMax and DMin respectively:

DMax("fieldName", "tableName", "criteria")

DMin("fieldName", "tableName", "criteria")

This is the table that we are going to use in our particular scenario:

If you remember, we are interested in the largest and smallest sale during the month of May.  As such, we are going to pass the field name "Total" for the first parameter, the table name "tblOrders" for the second, and the criteria "OrderDate >= #05/01/2011# and OrderDate <= #05/31/2011#" for the third.  Note the third criteria is basically a SQL WHERE Statement (without the WHERE keyword) and uses the American Date Format (ie  Month/Day/Year), rather than the International Date Format.  This is the full syntax:

DMin("Total", "tblOrders", "OrderDate >= #5/1/2011# and OrderDate <= #5/31/2011#")

In previous posts I have shown you how to use Access Functions in Queries and Calculated Controls (see posts on Calculating Date Difference and  DLookUp).  For this example I would like to use the function within a VBA sub procedure.

I have created a form with an unbound text box called txtResult and two command buttons, cmdMax and cmdMin.  When the user clicks one of the command buttons - lets say it is the cmdMax button - the VBA procedure containing the DMax function runs, calculating the maximum sale for the month of May, and displaying the result in txtResult.

Here is the VBA code that I used:


Private Sub cmdMax_Click()
    Dim varMax As Currency
    varMax = DMax("Total", "tblOrders", "OrderDate >= #5/1/2011# and OrderDate <= #5/31/2011#") 
    Me!txtResult = varMax
End Sub


Private Sub cmdMin_Click()
    Dim varMin As Currency
    varMin = DMin("Total", "tblOrders", "OrderDate >= #5/1/2011# and OrderDate <= #5/31/2011#")
    Me!txtResult = varMin
End Sub


As you can see each sub runs when it's respective command button's ONCLICK event is triggered.  The result returned by the function is stored in a currency type variable called varMax or varMin.  The value of txtResult is then set to that of the variable in order to display the result.

You can download this DMax/Min example database by clicking the link.  Please feel free to experiment  using different criteria and adding new data to the table etc.