How to write Case Sensitive Query for MS Access?

Question

I want to know Select Query for MS Access case sensitive.

I have two values ​​for VitualMonitorName as below

VCode VirtualMonitorName Row 1 (1, 'VM1'); Row 2 (2, 'Vm1'); 

Here both meanings are different.

If i write

 "SELECT VCode FROM VirtualMaster WHERE VirtualMonitorName like '" + Vm1 + "'"; 

It only responds with VCode = 1.

Answer 1

You can use the StrComp() function with vbBinaryCompare to compare with case. Here is an example from the Immediate window to show how StrComp() works. For more information, see Access Help.

 ? StrComp("a", "A", vbBinaryCompare) 1 ? StrComp("a", "A",vbTextCompare) 0 

StrComp() returns 0 if the first two arguments evaluate to equal, 1 or -1 if they are not equal, and Null if any argument is Null.

To use a function in a query, set the constant vbBinaryCompare (0), not its name.

 SELECT VCode FROM VirtualMaster WHERE StrComp(VirtualMonitorName, "Vm1", 0) = 0; 

This approach is also available for queries from other applications if they use the new Access Database Engine ("ACE") drivers. For example, the following C # code

 string myConnectionString = @"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + @"Dbq=C:\Users\Public\Database1.accdb;"; using (OdbcConnection con = new OdbcConnection(myConnectionString)) { con.Open(); using (var cmd = new OdbcCommand()) { cmd.Connection = con; cmd.CommandText = "SELECT COUNT(*) AS n FROM [VirtualMaster] " + "WHERE StrComp([VirtualMonitorName],?,?) = 0"; cmd.Parameters.AddWithValue("?", "Vm1"); cmd.Parameters.Add("?", OdbcType.Int); var vbCompareOptions = new Dictionary<string, int>() { {"vbBinaryCompare", 0}, {"vbTextCompare", 1} }; string currentOption = ""; currentOption = "vbBinaryCompare"; cmd.Parameters[1].Value = vbCompareOptions[currentOption]; Console.WriteLine( "{0} found {1} record(s)", currentOption, Convert.ToInt32(cmd.ExecuteScalar())); currentOption = "vbTextCompare"; cmd.Parameters[1].Value = vbCompareOptions[currentOption]; Console.WriteLine( "{0} found {1} record(s)", currentOption, Convert.ToInt32(cmd.ExecuteScalar())); } } 

produces

 vbBinaryCompare found 1 record(s) vbTextCompare found 2 record(s) 

Answer 2

Check this:

https://support.microsoft.com/kb/244693?wa=wsignin1.0

This article describes four methods for obtaining case-sensitive JOINs using the Microsoft Jet database engine. Each of these methods has advantages and disadvantages that should be weighed before choosing an implementation. Methods:

• Strcomp
• IISAM Case-Sensitive Driver
• Hexadecimal extension
• Binary field

Answer 3

Using only the built-in functions, add an additional custom column in the query designer:

 location: InStr(1,[VCode],"VM1",0) 

a null parameter requests a binary comparison (case sensitive) when searching for the location “VM1” within [VCode]

set the criteria in this column to >0 , so only entries with a non-zero location matching vcode Like "*vm*" contain the exact string VM1 -

The WHERE as follows:

 WHERE (((VirtualMaster.VCode) Like "\*vm*") AND ((InStr(1,[VCode],"VM1",0))>0)); 

Answer 4

Use at a simpler coding level.

As a condition in a DCOUNT operation, checking for a field (column) that must have the correct register, and ignoring the states of empty fields / territories.

  ' lngcounter will count the all States ' or Territories Field ( Column) with this ' exact case value of 'Ohio'. ([ID] is an Autonumber ID field) lngCounter = DCount("[id]", Trim(Me!tboDwellingTablename), "[State/territory],'Ohio',0) = 0")