Problem With Like

[ndjs]

Consider the following numbers :

M01T01-1903-0689-1
M01T01-1903-0689-2
M01T01-1906-0788-t
et al

If I use

SELECT * FROM Component Where TypRef LIKE '"& "%" & QryString & "%" '"

I can find any combination of letters or numbers except when the QryString = "-t" "-2" or just "t"

I have tried Trimming the string but with no success
Can anyone advise please

 

[chrisJLocke]

Have you tried:

SELECT * FROM Component Where TypRef LIKE "*" & QryString & "*"

The star (*) is usually the 'anything' wildcard.

 

[sunaj]

% is the wildcard in SQL.

I don't understand your question. Which rows should your query return?
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[chiph]

chrisJLocke -
The asterisk (*) is the wildcard for MSAccess, which is not ANSI SQL compliant. Everyone else uses the percent-sign (%) for the wildcard character.

ndjs -
I think your double-quotes are a little out of whack. Try this:

sSQL = ""
sSQL = sSQL & " SELECT *"
sSQL = sSQL & " FROM Component"
sSQL = sSQL & " WHERE TypRef LIKE '%" & QryString & "%'"

Chip H.

 

[ndjs]

Sunaj - I'm sorry about the ambiguity, but without trying to type in reams of tedious numbers I was trying to demonstrate that my code was unable to find the last letter(s) of the string whatever it is ie;
If QryString = "T01" 3 records are returned
= "-1903" 2 are returned
= "068" 2 are returned
= "-1" 3 are returned
but = "-2", "2", "-t" or "t" returns nothimg !


Chiph - You are right the quotes are in a mess, but despite your suggestion the amendment still can't find those last numbers ! Is is because they are in the ultimate or penultimate postition of the string ?

 

[sunaj]

Hi agian,

I think Chiph is right about the quotes.
I also thought that the wildcard was * in access, but testing:
Rst.Open "SELECT * from tblMyTable Where Name like '%-t%'", con, adOpenStatic, adLockOptimistic
Returns 'M01T01-1906-0788-t' with ndjs's rows in the table.
Whereas
Rst.Open "SELECT * from tblMyTable Where Name like '*-t*'", con, adOpenStatic, adLockOptimistic
return empty..... Chiph - do you have an explanation?

Anyway ndjs:
SELECT * FROM Component Where TypRef LIKE '%" & QryString & "%'"

should work for you.



Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[chiph]

Rst.Open "SELECT * from tblMyTable Where Name like '*-t*'", con, adOpenStatic, adLockOptimistic
return empty..... Chiph - do you have an explanation?

It's because it's looking for a row that contains '*t*' where that's the literal value (no wildcarding), and there aren't any rows in ndjs' sample data that looks like that.

Unless - you mean that you were trying this on MSAccess the product, and not in VB? If so, what technology were you using to connect? I'm thinking that ADO/OLEDB is translating the percentsigns into asterisks because it knows you're talking to MSAccess.

The percent-sign wildcards should work even at the end of the data, as they mean "match 0 or more characters"

Chip H.

 

[ndjs]

Sunaj - I've tried the crude "SELECT * from MyTable Where Name like '%-t%'", con, adOpenStatic, adLockOptimistic
but still no records are returned ! Whereas you were succesful. Could it be anything to do with the setup?
I'm using ADO looking up an Acesss.mdb