How to write Case Sensitive Query for MS Access?

Question

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

I have two values for VitualMonitorName as below

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

Here both values are different.

If I write

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

It replies VCode = 1 Only.

Solution 1

You can use the StrComp() function with vbBinaryCompare for a case-sensitive comparison. Here is an example from the Immediate window to show how StrComp() works. See the Access help topic for more details.

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

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

StrComp() returns 0 if the first two arguments evaluate as equal, 1 or -1 if they are unequal, and Null if either argument is Null.

To use the function in a query, supply the vbBinaryCompare constant's value (0) rather than its name.

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

This approach is also available to queries from other applications if they use the newer 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)

Solution 2

Check this out:

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

This article describes four methods of achieving a case-sensitive JOIN using the Microsoft Jet database engine. Each of these methods has advantages and disadvantages that should be weighed before choosing an implementation. The methods are:

• StrComp
• Case-Sensitive IISAM Driver
• Hexadecimal Expansion
• Binary Field

Solution 3

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

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

the zero parameter requests binary compare (case sensitive) when finding location of "VM1" within [VCode]

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

The WHERE clause looks like:

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

Comments

[Code Pharaoh]

hello it might help if your code used the questions example code. It may make it easier for the asker to apply your solution.

[Ddddan]

Note: if you are using this in a query (as opposed to a vb script), you have to use the numeric value as the vb constants will throw an error