InStr problem (1 Viewer)

[tsteinh]

Can someone tell me why this statement fails in Access97 when the string SID contains CTRLTH232099

pos = InStr(SID, "B", vbBinaryCompare)

I simply want to know if the string contains a capital B. I keep getting a type mismatch error.

Thanks!

 

[simongallop]

Remove the vbBinary bit and just have Instr(SID, "B"). Just one other thought. What have you declared SID as? Also what have you declared pos as?

[This message has been edited by Harry (edited 01-23-2002).]

 

[rockies1]

InStr requires a starting position.

Use: pos = InStr(1,SID, "B", vbBinaryCompare)

The 1 tells it to begin at the first character.

 

[simongallop]

no it doesn't

 

[tsteinh]

Harry:

SID is defined as a String and pos is an Integer. I added the vbBinary to make sure it only found the upper case B. It appeared to me that the lower case B was also being found when I left off the vbBinary. Do you have another idea I should try? I just want pos to have a value if the upper case version is found.

Thanks for the quick response!

 

[simongallop]

Find it all a bit strange as the following works:

Dim pos As Integer
Dim SID As String

SID = "CTRL156740"
pos = InStr(SID, "B")
MsgBox pos


Comes back with 0 which is correct!

 

[tsteinh]

Harry:

I will look at the problem in debug mode again. If your code works I must have another problem that I am just not seeing.

Thanks for the assistance! At least I know that this should work!

 

[tsteinh]

Here is some test code that illustrates my real problem:

SID = "b90e09s00"
pos = InStr(SID, "B") 'look for B
If pos > 0 Then
pos = InStr(pos, SID, "E") 'look for E
If pos > 0 Then
pos = InStr(pos, SID, "S") 'look for S
If pos > 0 Then 'have a BES format
MsgBox "SID contains BES"
End If
End If
End If

I want the message box to show ONLY if the upper case letters BES appear somewhere in the string in that order. The message appears even if the letters are only lower case.

Any assistance would be GREATLY appreciated!

 

[tsteinh]

I found the problem with a little assistance from another user.

In case someone else runs into this, you must specify the start position if you also want to indicate the type (vbBinaryCompare). The pos variable needs to be a Long type. In my case, I had to specify both the starting position and the vbBinaryCompare in order to have the script behave properly when testing lower case values.

Hopefully this will help someone. I have found answers many times on this forum and really appreciate everyone's help.