[RESOLVED] LIKE not working some times

[RobCrombie]

Hi,
I have a VB6 program that uses ADO (2.5) and Access 97 DB
When I do an SQL SELECt using LIKE it works most times, but not when the text being queried is hard left in the DB record.
EG say I was searching for averly which occurs in the word Waverly.
If that word is in a record such as Mount Waverly, it gets a 'hit'
If that word was on the extreme left such as Waverly Gardens, it does not return a 'hit'
This is the query that was executing -

SELECT * FROM LeadsJobs  WHERE  JobNu<>0 AND  CdJobAddress LIKE '%averly%'

Both the examples I mention above are present in the DB, but only the Mount Waverly record is returned.

Is this a bug, or an incompetent programmer ?

Would appreciate comments

[si_the_geek]

I've never seen an issue like that before.. so I suspect the issue is something unrelated to the Like.

What are the JobNu values for those rows?

[RobCrombie]

JobNu is a Numeric field in Access (Long Integer0
The record with Waverly hard left has 6529 in the JobNu field
The record that is returned has 6553

Thanks

[brucevde]

When things like this occur in MS Access something is corrupt. It is time to try a Repair...

[RobCrombie]

I prepared a simple example to post, but it worked ok.
So I examined every possibility, until I discovered that some one had spelled the offending record with ley on the end instead of ly. And then some idiot programmer didn't notice.
In my defence the user has been complaining about records not being found by the search. I have now requested that he send me a real example, when it next occurs.

Thanks to you both, for your time.