How to add case-sensitivity to a Combo box in MS-A

Question

I have a combo box with a RowSource. When it displays, it shows a list of items that come from a query. The query has two columns. When the user chooses something, the first column is stored in the table, but the second column is displayed (works fine).

When you come to the form and view some existing data, it shows the display value from the query (RowSource) like it should. Works nice most of the time.

The challenge is that one of my combo boxes deals with case-sensitive keys. The wrong stuff is getting shown.

Here is some contrived data:

id   value
[a1] [Apples]
[A1] [Oranges]

The underling table is like this:

MyTable
id: int
...other fields...
fruit_key: string

When the fruit_key column contains, "a1", I want to see Apples. When it contains "A1", I want to see Oranges.

What's happening is that I am always seeing Apples because it's doing a case-insensitive match.

How do I change this to case-sensitive?

Answer 1

You can't, except with kludgy workarounds. From How To Perform a Case-Sensitive JOIN Through Microsoft Jet:

The Microsoft Jet database engine is inherently case-insensitive.

And there is no way to change that.

In WHERE conditions and JOINs in queries, you can work with binary string compare: StrComp(Field1, Field2, 0) = 0.

But here, with the bound combobox, your best choice would be to convert the fruit_key string into e.g. a hex string (see Hexadecimal Expansion in the article).

In the rowsource of the combobox you could use a computed column, but in the table you want to edit with the combobox, you'd have to actually store the hex string in addition to the fruit_key, because you can't edit a computed column.

I'm not sure if or how the Binary Field method would work in your case.

All in all you'd probably be better off by

• "doing it differently" so that you don't need the case-sensitive data, or
• not using Jet as backend, but a server database, where you can set a case-sensitive collation.