How do I make InStr case sensitve in MS Access

Question

How do I make InStr case sensitive in MS Access?

I'd like the following to display 0

 msgbox InStr("In Here", "here")

Instead I get 4.

I've tried adding vbBinaryCompare

 msgbox InStr("In Here", "here", vbBinaryCompare)

but it complains about a type mismatch.

Answer 1

Use InStrB instead of InStr. Then it will do a byte by byte comparison instead of case insensitive.

 msgbox InStrB("In Here", "here")

Displays 0.

Answer 2

The help topic doesn't make this point clear, but when you use the optional compare argument, you need to also supply the optional start argument in order to avoid that type mismatch complaint.

So this displays 0 in the MsgBox:

MsgBox InStr(1,"In Here", "here", vbBinaryCompare)