|
Custom Search
| |
|
How to do text search in MS Access programmatically
Text comparison in MS Access database is by default case-insensitive. However, if you work with Access in your day-to-day job, sooner or later you'll come across a situation where you have to do case sensitive text comparison. Commonly there are 3 ways to do text comparison in MS Access.
The two functions take precedence over the 3 Option Compare Statements if they both are used in a module. For example, if Option Compare Binary is specified for text sensitive comparison across anywhere in a VBA module, using StrComp in VBA code for case insensitive comparison would ignore the Option Compare Binary option, but for any other parts of the VBA module where StrComp is not used, Option Compare Binary still takes effect. 1. Use InStr function InStr function returns the position number of the first occurrence of one string within another. If the string is not found, it returns 0. The following example searches character F within string Flower. It returns 1.
Function test() Dim check As Long check = InStr(1, "Flower", "F", 0) End Function There are 4 arguments specified here.
1 - this sets the starting position for each search. Here we start at the 1st character of the string Flower.
Table below lists the VB constants and the equivalent numeric value. It instructs the type of string comparison INSTR performs.
2. Use the StrComp function StrComp (string1, string2 [, compare ]) As its name suggests, the StrComp function compares two strings based on the type of comparison specified in the third argument.
Great or less is based on alphanumeric order. Numeric values are always less than alphabetical characters. Uppercase characters are less than lowercase characters. Lowercase English characters are less than Non-English characters, see below. 1 < 2 < 3 < 4 < 5 < 6 < 7 < 8 < 9 < 10 < 11 ... < A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø
StrComp ("Geeks", "Geeks") Result: 0 StrComp ("Geek", "Gee", 0) Result: 1 StrComp ("geek", "Gee",1) Result: 1 StrComp ("Gee", "gee",0) Result: -1 StrComp ("À", "1.2",1) Result: 1 check = StrComp("a", "À", 1) Result: -1 StrComp ("3", "t",1) Result: -1 Use StrComp or InStr in a query as part of the WHERE clause If text comparison is conducted between a text and the value from a text column in a table, you also can resort to StrComp or InStr to perform either case sensitive or non-case-sensitive searches. SELECT * FROM Products WHERE StrComp(ProductName,'Chai',0)=0
Result: 1 record returned SELECT * FROM Products WHERE InStr(1, ProductName,'chai',0)>0 Result: 0 records returned. SELECT * FROM Products WHERE StrComp(ProductName,'Chai',1)=0
Result: 1 record returned SELECT * FROM Products WHERE InStr(1, ProductName,'chai',1)>0
Result: 1 record returned 3. Option Compare Statement The Option Compare Statement is used at module level to declare the default comparison method to use when string data is compared. There are 3 types of Option Compare Statements.
3.1 Option Compare Binary Option Compare Binary results in string case-sensitive string comparisons across anywhere in the VBA module where it's declared. SELECT * FROM Products WHERE ProductName='chai' Returns nothing because in Products table there is only 1 product named 'Chai'. SELECT * FROM Products WHERE ProductName='Chai' Returns 1 record. 3.2 Option Compare Text Option Compare Text results in string case-insensitive string comparisons across anywhere in the VBA module where it's declared. It's based on the sort order determined by your system's locale. SELECT * FROM Products WHERE ProductName='chai' Returns 1 record. SELECT * FROM Products WHERE ProductName='Chai' Returns 1 record. 3.3 Option Compare Database Option Compare Database can only be used within Microsoft Access. This results in string comparisons based on the sort order determined by the locale ID of the database where the string comparisons occur.
Happy Coding!
|
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |