- Messages
- 52
- Reaction score
- 21
- Points
- 18
In microsoft access 2003 what are wildcards?????
thNx in advance
thNx in advance
-
We need your support!
We are currently struggling to cover the operational costs of Xtremepapers, as a result we might have to shut this website down. Please donate if we have helped you and help make a difference in other students' lives!
Click here to Donate Now (View Announcement)
ICT HELP NEEDED ASAP
- Thread starter SweetSomebody
- Start date
- Messages
- 181
- Reaction score
- 81
- Points
- 38
A wildcard is a special character that can represent one ormore characters in a text value. You can use wildcards to find many recordswith similar, but not exactly the same, information. You can also use them to lookfor a specific record when you can't remember enough information to retrievejust that one record.
The ability to find and retrieve data easily is great, butuse wildcards cautiously. If you use the wrong character, a query will almostcertainly return erroneous data. If you're not lucky enough to catch themistake visually, the erroneous data could go undetected for a long time--longenough to corrupt your data or even your entire application.
# 1: Match characters in a specific position
The most flexible wildcard character is the asterisk (*). Itmatches any character or any block of characters in a specific position. Forinstance, the following statement would return any entry that contains thestring access without regard toletter case:
Like "*Access*"
So it would return the following entries: Microsoft Access, Access 97, and accessing.
Drop the first * character to match entries that begin withthe string access:
Like "Access*"
This statement would return Access 97 and accessingbut not Microsoft Access. Similarly,the following statement would return MicrosoftAccess but not Access 97 or accessing:
Like "*Access"
# 2: Spaces matter in a * match
When using the * character to match characters in a specificposition, pay close attention to space characters. If the search stringincludes a space character between the literal characters and the * character,Access will return only those entries that include a space character in thatposition. For instance, the following statement matches Microsoft Access but not Access97 or accessing:
Like "* Access"
You might not expect that search string to return Access 97, but the absence of the word accessing might be a surprise. Access won'treturn accessing because the entrydoesn't have a space character before the accessblock. Similarly, the following statement matches Access 97 but not MicrosoftAccess or the single word entry, accessing:
Like "Access *"
# 3: Match just one character in a specific position
The question mark character (?) serves as a single-characterplaceholder. You can combine this wildcard to specify a certain number ofcharacters. For instance, the following statement would match Smith and Smyth but not the word smooth(without regard to letter case):
Like "Sm?th"
You can combine ? characters to cast a wider net or to focusthe search, depending on the circumstances. For example, the following criteriawould find Smith, smile, smite, and smirk:
Like "Smi??"
It would not find smirchor smitten. That's because each ?character represents only one character. Although both smirch and smitten matchthe literal characters in the expression (smi), both words have more thantwo characters following those literal characters. To catch all entries thatbegin with smiwithout regard to the number of characters, use the * character (# 1).
# 4: Use ? to match symbol characters
Although the English language doesn't use specialcharacters, like è and ü, it's common to store values that contain suchcharacters. They pose a unique problem in a search, because you must use thespecial character and not just the English counterpart. You might find iteasier to use the ? placeholder in these cases. For instance, if you want tofind the record for Comèrcio Mineiro,you could take the time to insert the é character, but doing so requires extrakeystrokes. Instead, use the ? wildcard to represent the special character, asfollows:
Like "Com?rcio *"
This search expression might match a few other entries, butin most cases, it won't, because it's such a narrow search. In this example,you must also include the * or the literal string to match the desired entrybecause it contains more than one word. If you omitted the *, Access wouldmatch an entry that contained only the text Com?rcio.
# 5: Use [ ] to match literal wildcard characters
Sometimes, you'll want to include a wildcard character aspart of a literal search string. For instance, you might use the * character todenote a special note or comment in a memo or text field. In that case, youmust tell Access to evaluate the * character as a literal character rather thana wildcard by enclosing the wildcard in brackets as follows
Like "[*]*"
If you use Like"*", Access will return every entry in the data source. (It isn'tnecessary to enclose the ! or ] wildcard characters in brackets.)
# 6: Match characters in a list
It's possible to match more than one specific character at atime using a list. To match any single character in a list, separate each itemin the list with a comma and enclose the list of characters in brackets. Forinstance, the following search string will find any entry that begins with a or z:
Like "[a, z]*"
If the list is a set of consecutive values, use the hyphento separate the first and last characters in the sequence. The followingexpression matches all entries that begin with the letters a, b, c, d,or e:
Like "[a-e]*"
The bracket wildcards are flexible enough to handle multiplelists as follows:
Like "[a-e, k, p-s]*"
The above expression matches all entries that begin with thefollowing letters: a, b, c,d, e, k, p, q,r, or s.
You can also use the bracket wildcards to denote charactersin the middle of a string. For instance, the following expression matchesentries that begin with the letter aand have the letters b or f as the second character:
Like "a[b, f]*"
# 7: Exclude characters
Most of the wildcard characters match characters. Theexclamation point character (!) is different. Use ! to exclude matchingcharacters from a query's results. For instance, the following expression wouldreturn all entries that do not begin with the letter a:
Like "[!a]*"
Notice that the expression includes the bracket wildcards.The ! character works with the brackets. In truth, you can exclude matches onlyfrom a list. However, the list can contain just a single character. Expand thelist possibilities by applying the same techniques discussed in # 6.
# 8: Accommodate SQL conflicts
Access uses Jet SQL, a custom vendor-specific version ofSQL. Access Project files (.adp) use Transact SQL (T-SQL). SQL Server also usesT-SQL. The two versions of SQL don't support the same wildcard characters. Thatmeans that an expression that works fine in an .mdb file won't work in an .adpfile or in SQL Server. (If you should ever upgrade, wildcards could be aproblem.) The ActiveX Data Objects (ADO) and Data Access Objects (DAO)libraries have a similar conflict. TableA lists the most common Access wildcards and their ANSI-92 SQL counterpartsin a Project file and SQL Server.
Table A: ANSI-92 SQL wildcards
Access (.mdb) and DAO
ADO, Access Project (.adp), and SQL Server
Explanation
*
%
Matches any character or multiple characters in its position.
?
_
Matches any single character in its position.
The ability to find and retrieve data easily is great, butuse wildcards cautiously. If you use the wrong character, a query will almostcertainly return erroneous data. If you're not lucky enough to catch themistake visually, the erroneous data could go undetected for a long time--longenough to corrupt your data or even your entire application.
# 1: Match characters in a specific position
The most flexible wildcard character is the asterisk (*). Itmatches any character or any block of characters in a specific position. Forinstance, the following statement would return any entry that contains thestring access without regard toletter case:
Like "*Access*"
So it would return the following entries: Microsoft Access, Access 97, and accessing.
Drop the first * character to match entries that begin withthe string access:
Like "Access*"
This statement would return Access 97 and accessingbut not Microsoft Access. Similarly,the following statement would return MicrosoftAccess but not Access 97 or accessing:
Like "*Access"
# 2: Spaces matter in a * match
When using the * character to match characters in a specificposition, pay close attention to space characters. If the search stringincludes a space character between the literal characters and the * character,Access will return only those entries that include a space character in thatposition. For instance, the following statement matches Microsoft Access but not Access97 or accessing:
Like "* Access"
You might not expect that search string to return Access 97, but the absence of the word accessing might be a surprise. Access won'treturn accessing because the entrydoesn't have a space character before the accessblock. Similarly, the following statement matches Access 97 but not MicrosoftAccess or the single word entry, accessing:
Like "Access *"
# 3: Match just one character in a specific position
The question mark character (?) serves as a single-characterplaceholder. You can combine this wildcard to specify a certain number ofcharacters. For instance, the following statement would match Smith and Smyth but not the word smooth(without regard to letter case):
Like "Sm?th"
You can combine ? characters to cast a wider net or to focusthe search, depending on the circumstances. For example, the following criteriawould find Smith, smile, smite, and smirk:
Like "Smi??"
It would not find smirchor smitten. That's because each ?character represents only one character. Although both smirch and smitten matchthe literal characters in the expression (smi), both words have more thantwo characters following those literal characters. To catch all entries thatbegin with smiwithout regard to the number of characters, use the * character (# 1).
# 4: Use ? to match symbol characters
Although the English language doesn't use specialcharacters, like è and ü, it's common to store values that contain suchcharacters. They pose a unique problem in a search, because you must use thespecial character and not just the English counterpart. You might find iteasier to use the ? placeholder in these cases. For instance, if you want tofind the record for Comèrcio Mineiro,you could take the time to insert the é character, but doing so requires extrakeystrokes. Instead, use the ? wildcard to represent the special character, asfollows:
Like "Com?rcio *"
This search expression might match a few other entries, butin most cases, it won't, because it's such a narrow search. In this example,you must also include the * or the literal string to match the desired entrybecause it contains more than one word. If you omitted the *, Access wouldmatch an entry that contained only the text Com?rcio.
# 5: Use [ ] to match literal wildcard characters
Sometimes, you'll want to include a wildcard character aspart of a literal search string. For instance, you might use the * character todenote a special note or comment in a memo or text field. In that case, youmust tell Access to evaluate the * character as a literal character rather thana wildcard by enclosing the wildcard in brackets as follows
Like "[*]*"
If you use Like"*", Access will return every entry in the data source. (It isn'tnecessary to enclose the ! or ] wildcard characters in brackets.)
# 6: Match characters in a list
It's possible to match more than one specific character at atime using a list. To match any single character in a list, separate each itemin the list with a comma and enclose the list of characters in brackets. Forinstance, the following search string will find any entry that begins with a or z:
Like "[a, z]*"
If the list is a set of consecutive values, use the hyphento separate the first and last characters in the sequence. The followingexpression matches all entries that begin with the letters a, b, c, d,or e:
Like "[a-e]*"
The bracket wildcards are flexible enough to handle multiplelists as follows:
Like "[a-e, k, p-s]*"
The above expression matches all entries that begin with thefollowing letters: a, b, c,d, e, k, p, q,r, or s.
You can also use the bracket wildcards to denote charactersin the middle of a string. For instance, the following expression matchesentries that begin with the letter aand have the letters b or f as the second character:
Like "a[b, f]*"
# 7: Exclude characters
Most of the wildcard characters match characters. Theexclamation point character (!) is different. Use ! to exclude matchingcharacters from a query's results. For instance, the following expression wouldreturn all entries that do not begin with the letter a:
Like "[!a]*"
Notice that the expression includes the bracket wildcards.The ! character works with the brackets. In truth, you can exclude matches onlyfrom a list. However, the list can contain just a single character. Expand thelist possibilities by applying the same techniques discussed in # 6.
# 8: Accommodate SQL conflicts
Access uses Jet SQL, a custom vendor-specific version ofSQL. Access Project files (.adp) use Transact SQL (T-SQL). SQL Server also usesT-SQL. The two versions of SQL don't support the same wildcard characters. Thatmeans that an expression that works fine in an .mdb file won't work in an .adpfile or in SQL Server. (If you should ever upgrade, wildcards could be aproblem.) The ActiveX Data Objects (ADO) and Data Access Objects (DAO)libraries have a similar conflict. TableA lists the most common Access wildcards and their ANSI-92 SQL counterpartsin a Project file and SQL Server.
Table A: ANSI-92 SQL wildcards
Access (.mdb) and DAO
ADO, Access Project (.adp), and SQL Server
Explanation
*
%
Matches any character or multiple characters in its position.
?
_
Matches any single character in its position.
Matches any character in list.
[!list]
[^list]
Excludes any character in list.
#
N/A
Matches the numeric digits 0 through 9.
//http://www.techrepublic.com/article/10-tips-for-using-wildcard-characters-in-microsoft-access-criteria-expressions/6154704
- Messages
- 52
- Reaction score
- 21
- Points
- 18
thanx a lot