Question on InStr() Function

Question

Question on InStr() Function

Goober

SSC Enthusiast

Points: 177
More actions
August 24, 2005 at 3:18 pm

#69629

InStr(start, string1, string2, compare)
How do I get the string2 argument to be case sensitive?
Example: I have data in a field that is both uppercase and lower case. However, all the uppercase is listed first in the field. I am wanting to use the InStr() function to determine the first position of a lowercase char in the string. I will then use the return in a Left() function to be rid of all lowercase char using an update query.
Greg

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Richard Moldwin SSCommitted Points: 1872 More actions · Answer 1

Search for Instr in the VBA help file. You just need to set the compare option to binary compare. IIRC, the compare constant is something like acCompareBinary, or something like that. It's all in the help file.

HTH,

Rich

Richard Moldwin SSCommitted Points: 1872 More actions · Answer 2

Settings

The compare argument settings are:

Constant	Value	Description
vbUseCompareOption	-1	Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare	0	Performs a binary comparison.
vbTextCompare	1	Performs a textual comparison.
vbDatabaseCompare	2	Microsoft Access only. Performs a comparison based on information in your database.

scking SSCrazy Points: 2598 More actions · Answer 3

Is your object only to change lower case characters to upper case? You don't need to know where those characters exist, just use the UPPER sql command. Of course it will also change the current text in upper case to upper case but then we shouldn't really care.

Richard Moldwin SSCommitted Points: 1872 More actions · Answer 4

If the goal is to change the whole string to upper or lower, you would use UCase or LCase in VBA. (You would not want to use a T-SQL command in VBA by mistake)

Michael Girdler Right there with Babe Points: 780 More actions · Answer 5

Hi Greg,

You will have to write yourself a function to do this. Try the sample code from this url where the question was asked and answered fairly well.

http://forums.aspfree.com/archive/t-48781/Access-DB-using-VB-to-select-messages-which-contain-Capitals

The code you are after is at the end of the link. The code is fairly well documented too. You won't need all of the code.

Hope this helps

Michael