Searching for a Wildcard in a “LIKE” Criteria

In a query, the LIKE operator allows the user to query for a character or group of characters anywhere within a text field.  It does this by matching the field to a string which mixes the character(s) you want to match with wildcard characters like * and ?. Some examples:

• FirstName LIKE "C*"  will return any name that starts with a C (i.e. Carlton, Clark)
• LastName LIKE "*-*"  will return any last name that has a hyphen anywhere in the field (i.e. Flickema-Carlson, Smith-Jones)
• PONum LIKE "C????"   will return and PO number that starts with a C and has exactly 5 characters. (i.e. CSIDF, C24DG)
• PONum LIKE "C##"  returns all values that start with a C, is exactly 3 characters long, and characters 2 and 3 MUST be numbers. (i.e. C45, C16)

So how can I search for a wildcard character itself?  I can simply enclose the wildcard character with brackets [].

For instance, if I wanted to find any value that has an asterisk (*) anywhere in it, I could do this:

• PO LIKE "*[*]*"

If I wanted to find any value that starts with a hash mark(#), I could do this:

• Check LIKE "[#]*"

If I wanted to find any value ending with a question mark (?), I could do this:

• Comment LIKE "*[?]"
  

Showing Query Parameters in a Report

A parameter query is one which asks the user for input.  For instance, suppose I have a query that pulls records for a date range. Suppose further, I want the query to ask me for a Start Date and End Date for the range.  I can create a query like this:

SELECT * FROM MyTable WHERE TheDate BETWEEN [Enter Start Date] AND [Enter End Date]

Running this query will bring up two dialog boxes:

 

Entering the values in the boxes will return the records in that range.

But if I create a Report based on this query, how do I show the selected date range?  After all, reports show information from records in the query’s record source, and Parameter values aren’t included.

There are a couple of possibilities.

Method 1: Read Parameters Directly

The first is to read the parameter directly.  To do that, I just put a text box on my report for each query parameter. In the control source for the text boxes, I put the query parameter preceded by an equal sign.

For instance, in the case of the above query, the control sources for my two text boxes will be:
=[Enter Start Date] and =[Enter End Date]

Alternately, I could have a single text box with your dates concatenated:
=[Enter Start Date] & " - " & [Enter End Date]

I can also fancy it up a bit like this:
=”Date Range: “ & [Enter Start Date] & " to " & [Enter End Date]

Method 2: Read Values Returned

The second option is to read the actual minimum and maximum values from the records in the record source.  I can use the Min and Max functions of the report to do that.  Again, in the Control Source property of a text box:

=Min([Enter Start Date]) & " - " & Max([Enter End Date])

I can, of course, put them in separate text boxes as well.

Comparing The Methods

So what’s the difference between the two methods?

The first method returns the date range requested.  The second method returns the date range actually returned.

Isn’t that the same thing?  Not necessarily.  Suppose I request data from 1/1/2010 to 7/1/2011, but my table only has data starting with 2/1/2011.  The first method will return 1/1/2010 to 7/1/2011 (what I requested).  But the second method will return 2/1/2011 to 7/1/2011 (what’s actually in the record source of the report).

Microsoft Releases Office 2010 SP1

Office 2010 Service Pack 1 has been released by Microsoft.  See MS Knowledgebase article: http://support.microsoft.com/kb/2460049.  The service pack can be downloaded from the article as well.

The main improvements to Access appear to be in the area of bug-fixes and stability.  There are only three improvements listed in the KB article, but in the downloadable fix list there’s a much longer list of fixes.

Everybody has different priorities, but to my mind, here are some of the more important problems that were fixed:

• Access does not activate or return the user to the correct Ribbon tab for a previously opened database object when the user returns to that object.
• Access Wizards are not loaded correctly when "Disable all controls without notification" is selected in Trust Center.
• The program crashes when you apply a sort to a query that is based on a multi-value field.
• "Reserved error -5500" occurs when you try to run a cross-tab query that would generate null values in the column names of the query.
• "Object invalid or no longer set" error occurs when you try to use an ALTER TABLE query to change a field type or size.
• You cannot relink tables in Access databases that have linked tables to other MDBs/ACCDBs that cannot be found
• The file format that is displayed in the title bar for Access 2010 databases is "(Access 2007)."
• Incorrect data is displayed when a user's query has a list that includes a combination of GroupBy and either OrderBy or Where
• "Invalid precision for decimal data type" or results are truncated when the user runs a crosstab query.

There are also a lot of “crash fixes”, all of which are important.

New Sample: NormalizingRepeatingColumnsVBA.mdb

By Roger Carlson

This sample demonstrates how to normalize a table that has repeated columns with VBA.  It's purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet with code rather than SQL statements.

Full Article Included

You can find the sample here:
http://www.rogersaccesslibrary.com/forum/normalizingrepeatingcolumnsvbamdb_topic567.html

Normalizing Repeated Columns: VBA

In my earlier blog series (The Problem of Repeated Columns), I defined repeated columns and talked about the data integrity problems associated with them. I also showed several different examples of repeated columns and the how difficult it is to query repeated columns as compared to the normalized equivalent. If you haven't read these yet, it would be worthwhile to read first. Similarly, if you are not familiar with the concept of Normalization, you should read my blog series What is Normalization?

One of the comments suggested I should discuss how to convert a table with repeated columns into a normalized table structure. I thought that was a really good idea, so I'm going to spend the next few posts doing so. The difficulty in discussing this issue, however, is that the exact solution differs with each table, so there is no single solution. Fortunately, in the last series, I showed 5 different tables, each with a slightly different structure, so while I cannot show a single solution that will work for every table with repeated columns, hopefully, one of the following will work for most cases.

Others in this series:

• Single Repeated Column
• Multiple Repeated Columns
• Yes/No Fields
• Using VBA (this post)

In my previous examples, I used purely SQL solutions. This time, I thought I'd illustrate how to do this with VBA. There's no real advantage to using VBA over the SQL solutions. In fact, in large databases, it will almost always be slower. Nevertheless, there may be situations with complex data that VBA would be the best solution, and besides, it represents another tool in your Access toolkit.

In my post Aggregating Across Repeated Columns: Summing, I discussed an example of table with repeated columns that looked like this:

Figure 1: Student Scores table with repeated columns

Normalized to the First Normal Form (1NF) to remove the repeated columns, the table would look like this:

Figure 2: Patient Symptom table (1NF)

Figure 2, however does not represent the final form of normalization. Because the student names are repeated, they should be removed to a separate table. So normalizing to Third Normal Form (3NF) I would have something like this:

Figure 3: Normalized to Third Normal Form (3NF) - Tables and Relationships Views

(For a further explanation of both First and Third Normal Forms, see The Normal Forms , What is Normalization, and Entity-Relationship Diagramming).

Normalizing to First Normal Form (1NF)

I'll start with the 1NF because it's less complex. Overall, it's a matter of looping through the records in the denormalized table (i.e. StudentScores_RepeatedColumns) and writing each column value into a new record in the 1NF table (i.e. StudentScores_1NF).

Public Sub Normalize_RepeatedColumns_VBA_1NF(ParamArray FieldNames())
'   This routine writes data from a table with repeated columns into a
'   table normalized to the First Normal Form (1NF)
'   The field names which are send in via the parameter list of the call
'   are written into an array called FieldNames.
'   declare variablesDim i As Integer
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
'   Open database object to current database

Set db = CurrentDb
'   Open the denormalized table to read the values

Set rsSource = db.OpenRecordset("StudentScores_RepeatedColumns")
'   Open the normalized table write the values

Set rsTarget = db.OpenRecordset("StudentScores_1NF")
'   Loop through the denormalized Source table

Do While Not rsSource.EOF
    '   Loop through the fields, i.e. the values in the parameter array    

For i = LBound(FieldNames) To UBound(FieldNames)
        '   Add a New record to the target table, write the values,
        '   and save (update) the record

        rsTarget.AddNew
            '   write the student name

            rsTarget!StudentID = rsSource!Student
            '   write the field name

            rsTarget!TestNum = FieldNames(i)
            '   write the field value

            rsTarget!Score = rsSource(FieldNames(i))
        rsTarget.Update
     Next i
     rsSource.MoveNext
Loop
End Sub
You can call the subroutine like so:
Sub test1NF()

'   To run the subroutine, place the cursor in this sub and click "run"
'   The arguments are the field names of the columns you want to
'   normalize
  Call Normalize_RepeatedColumns_VBA_1N _
       ("Test1", "Test2", "Test3", "Test4")
   
End Sub

The end result looks like Figure 2 above.


Normalizing to Third Normal Form (3NF)

The process for normalizing repeated column data into 3NF is similar to the 1NF process. It does, however, require two loops, one to add records to the "one-side" table ("Student") , and an inner loop (For...Next) to write records to the "many-side" table ("StudentScores").

It is important to note that the order in which the data is moved is vital. Data must be written into the "one-side" table first, and then data can be moved into the "many-side" table. Overall, the process is to loop through the records in the denormalized table, write common values to the a record in the one-side table, store the primary key value from the new record, and then for each field in the parameter array, create a new record in the many-side table.

Public Sub Normalize_RepeatedColumns_VBA_3NF(ParamArray FieldNames())
'   This routine writes data from a table with repeated columns into two
'   normalized tables: Students and StudentScores
'   The field names which are send in via the parameter list of the call
'   are written into an array called FieldNames.
'   declare variables

Dim i As Integer
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTargetOneSide As DAO.Recordset
Dim rsTargetManySide As DAO.Recordset
Dim StudentIDtemp As Long
'   Open database object to current database

Set db = CurrentDb
'   Open the denormalized table to read the values

Set rsSource = db.OpenRecordset("StudentScores_RepeatedColumns")
'   Open the "one-side" table to write the values

Set rsTargetOneSide = db.OpenRecordset("Student")
'   Open the "Many-side" table to write the values

Set rsTargetManySide = db.OpenRecordset("StudentScores")
'   Loop through the denormalized Source table

Do While Not rsSource.EOF
    '   Add a New record to the "one-side" target table, save
    '   the primary key value (autonumber) for use later,
    '   and save (update) the record

    rsTargetOneSide.AddNew
        rsTargetOneSide!Student = rsSource!Student
        '   save the StudentID created by the autonumber field

        StudentIDtemp = CLng(rsSource("StudentID"))
    rsTargetOneSide.Update
   
    '   Loop through the fields in the Parameter Array    

For i = LBound(FieldNames) To UBound(FieldNames)
   
        '   Add a New record to the "many-side" target table,
        '   write the values, and save (update) the record       

             rsTargetManySide.AddNew

            '   write the saved student id\

            rsTargetManySide!StudentID = StudentIDtemp
            '   write the field name

            rsTargetManySide!TestNum = FieldNames(i)
            '   write the field value

            rsTargetManySide!Score = rsSource(FieldNames(i))
        rsTargetManySide.Update
       
    Next i
    rsSource.MoveNext
Loop
End Sub
You can call the subroutine like so:
Sub test3NF()

'   To run the subroutine, place the cursor in this sub and click "run"
'   The arguments are the field names of the columns you want to
'   normalize  
  Call Normalize_RepeatedColumns_VBA_3NF _
      ("Test1", "Test2", "Test3", "Test4")
   
End Sub
The end result looks like Figure 3 above.

You can find a sample which illustrates the above here:
http://www.rogersaccesslibrary.com/forum/normalizingrepeatingcolumnsvbamdb_topic567.html

New Sample: Form_SynchronizedSubforms

By A.D. Tejpal

This sample db demonstrates synchronized scrolling of two subforms (both in datasheet view).

Two modes are covered:
    (a) One way synchronization: Top subform always functions as the master while the other one serves as the slave.
    (b) Two way synchronization: Whichever subform happens to be the active one, functions as the master while the other one serves as the slave.

Note: For ready identification, the subform currently serving as the slave, has a darker back color as compared to the master.

For each of the above modes, three alternative styles of scroll synchronization are demonstrated:
    (a) Synchronize horizontal scroll only.
    (b) Synchronize vertical scroll only.
    (c) Synchronize both horizontal and vertical scroll.

Sample data depicts student's scores in phase 1 (top subform) and phase 2 (bottom subform). For each student, wherever the scores in these two subform happen to differ, the same get highlighted as follows:
    (a) Top subform: Light grey.
    (b) Bottom subform: If value is greater than that in other subform, it gets highlighted in light green. On the other hand, if value is less than that in other subform, it gets highlighted in light pink.

Note: The above highlights get suitably updated promptly on editing of data in either of the two subforms.

Version:  Access 2000 File Format

You can find the sample here:
http://www.rogersaccesslibrary.com/forum/Form-synchronizedsubforms_topic566.html

New Sample: Query_ComputeAcrossFields_CodeLess

By A.D. Tejpal

    This sample db demonstrates computations across fields via pure SQL. In the first step, normalization of data is accomplished, using Cartesian join between the source table and an ancillary single field table holding the field names. Thereafter, a totals query provides the desired results.

    Two styles are covered:
    (a) Compute across all fields.
    (b) Compute across top 3 fields (i.e. fields holding top 3 values).

Version:  Access 2000 File Format

You can find the sample here:
http://www.rogersaccesslibrary.com/forum/query-computeacrossfields-codeless_topic565.html