'Like' function returning odd results (AC2007)

[AOB]

Hi guys,

I'm trying to do a string compare between two variables. One string variable is part of an array (which I'm looping through), the other is passed to the function as an argument.

(Background, if it helps; the function should return the position of a field in the OrderBy string of a subform. So I can see the priority of a field in the sort)

Here's the VBA :

Private Function SortPosition(strOrderBy As String, strField As String) As String
[INDENT]Dim arrSortedFields() As String
Dim i As Long
 
If Len(strOrderBy) > 0 And InStr(strOrderBy, strField) > 0 Then
 
[INDENT]arrSortedFields = Split(strOrderBy, ",")
 
For i = LBound(arrSortedFields) To UBound(arrSortedFields)
 
[INDENT]If [COLOR=blue]arrSortedFields(i) Like "[" & strField & "]*"[/COLOR] Then
 
[INDENT]SortPosition = "Sort Priority " & i + 1 & " of " & UBound(arrSortedFields) + 1
Exit For
[/INDENT]
 
 
End If
[/INDENT]
 
 
Next i
[/INDENT]
 
 
Else
 
[INDENT]SortPosition = ""
[/INDENT]
 
 
End If
[/INDENT]
 
 
End Function

The weird thing is, the line in blue returns True when, by watching the variables, it appears that it should not, and returns False when it appears that it should?

For example :

arrSortedFields(i) = "[RandomField]"
strField = "RandomField"

arrSortedFields(i) Like "[" & strField & "]*" = False

arrSortedFields(i) = "[SomeFieldName]"
strField = "ADifferentField"

arrSortedFields(i) Like "[" & strField & "]*" = True

I would have thought the expression "[RandomField]" Like "[RandomField]*" should return True?

And similarly the expression "[SomeFieldName]" Like "[ADifferentField]*" should return False?

Any suggestions? Have been using 'Like' for donkey's years and never seen it throw results like this before?

(P.S. The reason I need to use 'Like' rather than a straight = is to account for the possibility that a field may be sorted descending, and therefore to nullify the DESC keyword which may follow any given field...)

Thanks!

AOB

 

[ajparkme]

I cannot understand the behaviour you are describing either, I am afraid. However, would it be possible to remove the keyword "DESC" from the string you are testing like this, after the line

For i = LBound(arrSortedFields) To UBound(arrSortedFields):

Dim tempString as String
tempString = iif(Right(arrSortedFields(i),5) = " DESC", Left(arrSortedFields(i),len(arrSortedFields(i)) - 5), arrSortedFields(i))

you could then forget using the "Like" comparison and you could compare tempString with the string you are looking for

 

[AOB]

Hi ajparkme

I can get round the problem using the InStr function thus :

If InStr(arrSortedFields(i), "[" & strField & "]") > 0 Then

I was just wondering why the 'Like' function was acting so strangely?

Thanks for the response!

AOB

 

[namliam]

The problem comes from the fact that for LIKE the square brackets [] actually means something, see:
http://office.microsoft.com/en-us/access-help/examples-of-wildcard-characters-HA102809757.aspx

[] actually is like a *, a wildcard(-ish) thing, which is making it not actually search for your fieldname but doing something completely different with it.

 

[AOB]

Thanks namliam!

Never even crossed my mind to check if the square bracket was a special character! Great to know for future reference, thanks for passing on the link!

 

[Brianwarnock]

This was a great catch by Namliam as I'm sure that most readers, like me, assumed normal brackets not noticing that they were square. Proof reading is a great skill.

Brian

 

[namliam]

We do tend to read what we expect to read, instead of actually reading the writen text. Yay discovery channel's brain games
I am sure that Mersayside is a nice town in Englend...

Did you really notice the typo's first time around?

 

[David R]

I was too busy trying to figure out where Merseyside was... dumb Yank here.

 

[Brianwarnock]

We do tend to read what we expect to read, instead of actually reading the writen text. Yay discovery channel's brain games
I am sure that Mersayside is a nice town in Englend...

Did you really notice the typo's first time around?

Of course not!
Pleased to see that David spelt Merseyside properly.

Just incase David hasn't found it yet its on the banks of the river Mersey
Home of the world's best football team , Liverpool FC , the Beatles and is a world heritage site.
That should annoy Col.

Brian

 

[AOB]

Bonus 'Thanks' to Brian for being a fellow Liverpool supporter!

YNWA