InStrRev gets second value not first in A2k VBA6 1

[zollo9999]

Hi

InStrRev gets the second space " " not first in A2k VBA6.

Here is part of my code

strStreet = "16 Hely St"
'Value actually from a database
'Value has been trimmed so has no leading or trailing white space.

I've tried these variations:

lngPosn = InStrRev(strStreet, " ")
lngPosn = InStrRev(strStreet, " ", -1, vbTextCompare)
lngPosn = InStrRev(strStreet, " ", len(strStreet), vbTextCompare)
lngPosn = InStrRev(strStreet, Chr(32), -1, vbTextCompare)
lngPosn = InStrRev(strStreet, Chr(32))
lngPosn = InStrRev(strStreet, Chr(32), -1, vbBinaryCompare)


The result is always lngPosn = 8.
I would expect lngPosn = 3

I've read help and Ken Getz's Handbook sections on this function
and it appears I am using the function correctly.

I can only conclude that InStrRev does not work in my version of VBA.

Tools References points me to a folder like
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6

and I find these DLLs

VBACV10.DLL
VBACV10D.DLL
VBACV20.DLL
VBE6.DLL
VBE6EXT.OLB

I'm not sure which is the VBA DLL and what the correct version is.

Any help would be appreciated.
Thanks


Zollo A+ / VBA Developer

 

[easyit]

Hi,

Sorry, but you got it backwards!

The startposition is the last character and it works backwards. use InStr if you need to start from the beginning....

easyit

 

[PCLewis]

I agree with easyit. InStrRev is behaving as expected by returning 8 in your examples.

InStr would return 3 if you used the default start position for InStr.

 

[zollo9999]

Hi Thanks for your input.

I got help elsewhere explaining how it works.

I thought if the function searches from the right, it would give the position number from the right, but if gives the position number from the left.

Thus I've had to get position from the right
lngPosn = Len(strX) - InStrRev(strX, " ") - 1

So I could then use
strCheck = Right$(strX, lngPosn)

Function Right$() Counts from the right to extract the string so I thought InStrRev would work the same way.

Anyway I've fixed it now and it works no worries.

Thanks
pb

Zollo A+ / VBA Developer

 

[PHV]

You wanted this ?
strCheck = Mid(strStreet, 1 + InStrRev(strStreet, " "))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[zollo9999]

PH
Thanks
That does what I want with 2 functions rather than 3.
Although Len(strX) is apparently a very fast function anyway, I still love achieving an aim with less code.

Have a star.
pb



Zollo A+ / VBA Developer