FindFirst finds wrong string and NoMatch=False

[ccoindre]

Using FindFirst and FindNext to build a tree from a wirelist. Build array of possible points to search and use pointer to index thru them adding more points to the array end until the tree is complete. The possible wires with beginning and end points are storeed in a table as text fields. The array is a string array. The code is:

tmpStr = "([TO_LRU_CONN_PIN] = '" & tmpArray(tmpArrayPointer) & "' AND [Group Number] = 0) "
rstCommon.FindFirst tmpStr

This works 80% of the time. Except for: E.G. tmpArray(at current pointer) = "3050 A1J143 a". FindFirst finds a record that matches where field [TO_LRU_CONN_PIN] = "3050 A1J143 A". I looped thru after FindFirst found its matching value and stripped out each character using the mid function and compared the two strings; the comparison says that the alpha characters "a" and "A" are equal. I then converted each character to ASCII and the comparison reported that "A" (ASCII 65) does not equal "a" (ASCII 97). Both strings are the same length; and look identical when printed. As far as I can determine, lowercase is being treated as upper case. Is there some criteria qualifier that is automatically converting lowercase to upper case or vice versa? I have searched the forums and internet, but not found a case where FindFirst finds a wrong string.
Thanking you in advance.

 

[RoyVidar]

Access and Jet is by default not case sensitive, so in ordinary comparisions "A" = "a".

In VBA you can force case sensitive comparision by altering the Option Compare statement to read

[tt]Option Compare Binary[/tt]

But that won't influence database operations. What you can try, is the following:

[tt]tmpStr = "strcomp([TO_LRU_CONN_PIN], '" & tmpArray(tmpArrayPointer) & _
"', 0)=0 AND [Group Number] = 0)"[/tt]

Look up the strcomp function in help for more information.

Roy-Vidar

 

[ccoindre]

Thanks RoyVidar,
Appreciate the suggestion. But, the problem lies in the FindFirst process. FindFirst is finding a record that is wrong based on the criteria. Testing the criteria value in the record after FindFirst 'finds' it shows that the alpha comparison of the two values is correct whether you compare the whole value or compare character by character. However, when you take the character by character ASCII value and compare those, they are definitely different, but, only for the lowercase characters. In the example the criteria string ended in a lowercase "a" and the record that FindFirst found containd a string in the field with an uppercase "A". It would take too long to cycle thru several thousand records using a raw compare. Just trying to understand what is wrong. Perhaps a misplaced or missing reference is responsible? I built a comparison test case not using the FindFirst/FindNext and it works correctly. For now, I will test the value in the FindFirst record found for validity with the requested criteria.
ccoindre