Access Cookbook - Ken Getz [31]
When you execute the query, it will return only exact, case-sensitive matches. If you run qryWordCS in the 01-14.MDB database and enter "SwordFish" at the parameter prompt, you should get the datasheet shown in Figure 1-41.
Figure 1-41. qryWordCS is case-sensitive, so it returns only one matching record
Now, open the tblWords table in 01-14.MDB (see Figure 1-42). Notice that the word "swordfish" appears in four records, each spelled using a different combination of upper- and lowercase letters. Run the qryWordsCI parameter query and enter SwordFish at the prompt. When the query executes, it returns all four swordfish records, not the specific version you typed at the prompt. Now run the qryWordsCS query, entering the same string at the prompt. This time the query returns only one swordfish record, the one that's spelled exactly as you typed it.
Figure 1-42. tblWords contains four swordfish records with different capitalizations
Discussion
This solution uses a simple VBA function to perform a string comparison. Because this function resides in a module that contains the Option Compare Binary statement, any string comparisons made using procedures in this module are case-sensitive. The acbExactMatch function is simple:
Option Compare Binary
Public Function acbExactMatch(var1 As Variant, var2 As Variant) As Boolean
acbExactMatch = (var1 = var2)
End Function
This function returns True only when the strings are spelled exactly the same way. The code compares the values in var1 and var2, and returns True if the values are equal, and False if they're not.
Another alternative, which provides slightly less flexibility, is to use the VBA StrComp function. This function can compare two strings on a binary basis (that is, it compares each character in the strings, taking case into account) and returns 0 if the two strings are exact matches. The syntax for calling StrComp in qryWordsCS looks like this:
StrComp([Word], [Enter Word], 0)
and the Criteria is 0 (not -1, as shown earlier).
1.15. Use a Query to Create a New Table Complete with Indexes
Problem
You know how to create a table from a make-table query, but when you create a table in this way it has no primary key or any other indexes. Furthermore, you can only create a new table with a structure based on that of an existing table. You'd like a way to create a table on the fly with the data types and field sizes you want and with appropriate indexes.
Solution
Access provides the data definition language (DDL) query, which is used to programmatically create or modify tables. It is one of the SQL-specific queries, which can be created only using SQL view. This solution shows you how to create and modify table definitions using DDL queries.
Follow these steps to create a table using a DDL query:
Design your table, preferably on paper, deciding which fields and indexes you wish to create. For example, before creating qryCreateClients, we came up with the design for tblClients shown in Table 1-8.
Table 1-8. Design for tblClients
FieldName
DataType
FieldSize
Index
ClientID
AutoNumber
Long Integer/Increment
Yes, primary key
FirstName
Text
30
Yes, part of ClientName index
LastName
Text
30
Yes, part of ClientName index
CompanyName
Text
60
Yes
Address
Text
80
No
City
Text
40
No
State
Text
2
No
ZipCode
Text
5
No
Create a new query. Click on Close at the Add Table dialog. Select Query → SQL Specific → Data Definition. This will place you in SQL view.
Enter a CREATE TABLE SQL statement. To create the sample table tblClients, enter the following SQL:
CREATE TABLE tblClients
(ClientID AutoIncrement CONSTRAINT PrimaryKey PRIMARY KEY,
FirstName TEXT (30),
LastName TEXT (30),
CompanyName TEXT (60) CONSTRAINT CompanyName UNIQUE,
Address TEXT (80),
City TEXT (40),
State TEXT (2),
ZipCode TEXT (5),
CONSTRAINT ClientName UNIQUE (LastName, FirstName) );
Save your query and run it by selecting Query → Run