More Complicated Searching with SQL
Many of the search boxes in the program are labelled with the word like. This allows you to perform powerful matching searches, using wildcards.
Wildcards are a computer term borrowed (like most computer terms) from similar usages in other fields. In this particular case, think "Aces are wild".
When you are searching for a match, you can use the ? character to match a single character, a * character to match any number of characters, and an expression like [a-c] to match a,b or c, [ace] to match a c or e, or [!ie] to match any character except i and e. Whatever you type in an entry field with the word like in it, will be enclosed in a pair of asterisks, so that any substring will match, but you can also apply much more powerful searches.
| Kind of match | Pattern | Matches | Won't Match |
|---|---|---|---|
| Multiple characters | a*a | aa, aBa, aBBBa | aBC |
| *ab* | abc, AABB, Xab | aZb, bac | |
| Special character | a[*]a | a*a | aaa |
| Multiple characters | ab* | abcdefg, abc | cab, aab |
| Single character | a?a | aaa, a3a, aBa | aBBBa |
| Single digit | a#a | a0a, a1a, a2a | aaa, a10a |
| Range of characters | [a|z] | f, p, j | 2, & |
| Outside a range | [!a-z] | 9, &, % | b, a |
| Not a digit | [!0-9] | A, a, &, ~ | 0, 1, 9 |
| Combined | a[!b-m]# | An9, az0, a99 | abc, aj0 |
Let's assume you don't remember exactly how to spell your customer, whose name was MacCormack, as there are many variations of this. Simply enter corma in the box, and all names containing these letters in sequence will appear. (The program automatically adds a * character to each end of the string.
Additonal matching capabilities in the Address Book
The address book section of the program also includes an entry field marked Matching:. You can add additional requirements in this entry.
For example, if you want to match all records with a blank email address enter the following text:
email is null
If you wanted to search for all customers who were also on AOL, you could use:
email like "*@aol.*"
You can also include multiple criteria, by enclosing them in parenthesis, and joining them with the operators and/or.
So, if you wanted to choose all customers in France or Switzerland who also have a rank greater than 7, you would type:
Country in ("France", "Switzerland) and Rank > 7
For Yes/No fields, simply compare against yes and no, although, please remember that in a mail merge field, you may need to compare against the values 0 for No, and -1 for Yes.
Field names for all address entries
Below is the list of field names for the address book. You will need this for extended searching, and for generating a Mail Merge. The addresses are stored in a query named MailMerge in the database.
| Field Name | Field Type if not text | Description if not obvious |
|---|---|---|
| Name | The customer's full name including title | |
| Addr1 | The full multiline body of the address | |
| Town | ||
| PostCode | ||
| Country | ||
| Tel | ||
| Fax | ||
| Mobile | A mobile phone number for the customer | |
| Rank | A numeric ranking | |
| NeverEmail | Yes/No | Set to Yes if the customer has requested not be be emailed. |
| Selected | Yes/No | Set to Yes if checked on the address page for the current selection type |
| NeverMail | Yes/No | Set to Yes if the customer has requested not be be mailed |
| Description | Description of the product | |
| Blacklist | Yes/No | Set to Yes if no contact is to be initiated |
| CatName | Product/Contact Category | |
| SubcatName | Subcategory | |
| DetailName | Detail |