InStr returning unexpected result ( Access2013)

[AOB]

Hi everybody,

This has me stumped for some time now...

I'm trying to determine the position of two characters in a string (ASCII 252 or [ü] and ASCII 254 or [þ])

See the below code :

Sub test()
    Dim strTest As String
    Dim lng252 As Long
    Dim lng254 As Long

    strTest = "Please use this ülinkþ for instructions"

    lng252 = InStr(1, strTest, Chr(252))
    lng254 = InStr(1, strTest, Chr(254))
End Sub

I would imagine that lng252 would evaluate to 17 (it is the 17th character in the test string) and lng254 would evaluate to 22 (it is the 22nd character in the test string)

Instead I'm getting 17 and 12??

Where is it getting 12 from? The 12th character in the string is "t"?

Am I going crazy?

 

[steve the fish]

Re: InStr returning unexpected result (XL2013)

I get 17 and 22

 

[Fluff]

Re: InStr returning unexpected result (XL2013)

I get 17 & 22
also on xl2013

 

[AOB]

Re: InStr returning unexpected result (XL2013)

Okay just copped that the code is actually in Access 2013, not Excel - sorry

In Excel I get 17 and 22 as well

But in Access, the exact same code gives me 17 and 12

Any ideas?

 

[Fluff]

Re: InStr returning unexpected result (XL2013)

I'll move this to the Access board

 

[Fluff]

Re: InStr returning unexpected result (XL2013)

I get the same result as you if running "Option Compare Database" but get 22 if I remove it

 

[RoryA]

Re: InStr returning unexpected result (XL2013)

Specifying the final compare argument should override any Option Compare statements.

 

[AOB]

Re: InStr returning unexpected result (XL2013)

Specifying the final compare argument should override any Option Compare statements.

Should - but doesn't (for me)

[COLOR=#FF0000]Option Explicit[/COLOR]
[COLOR=#FF0000]Option Compare Database[/COLOR]

Sub test()
    Dim strTest As String
    Dim lng252 As Long
    Dim lng254 As Long

    strTest = "Please use this ülinkþ for instructions"

    lng252 = InStr(1, strTest, Chr(252), [COLOR=#FF0000]vbTextCompare[/COLOR])
    lng254 = InStr(1, strTest, Chr(254), [COLOR=#FF0000]vbTextCompare[/COLOR])
End Sub

 

[AOB]

Re: InStr returning unexpected result (XL2013)

This works mind...

[COLOR=#ff0000]Option Explicit[/COLOR]
[COLOR=#ff0000]Option Compare Database[/COLOR]

Sub test()
    Dim strTest As String
    Dim lng252 As Long
    Dim lng254 As Long

    strTest = "Please use this ülinkþ for instructions"

    lng252 = InStr(1, strTest, Chr(252), [COLOR=#ff0000]vbBinaryCompare[/COLOR])
    lng254 = InStr(1, strTest, Chr(254), [COLOR=#ff0000]vb[COLOR=#ff0000]Binary[/COLOR]Compare[/COLOR])
End Sub