How do I make wildcards work in sql thru ado?

[Herbatic]

I've got a tricky problem, I can't fix it in msdn, so what to do? time to come to you gurus here at vbforums

The situation is I'm accessing an access 2000 (jet) database from vb6 using ADO. when i try and open a recordset based on this string:

SELECT Files.[FileID], Files.[File Name], CDs.[CDID], CDs.[CD Name] FROM CDs INNER JOIN Files ON CDs.CDID = Files.CDID WHERE ((Files.[File Name]) Like 'gladiato*');

it returns 0 records. If I run the same string directly in an Access query it works fine and i get 1 record 'gladiator'. also if i change the 'gladiato*' to 'gladiator' then it works thru vb.

It seems to me that i can't use wildcards in the sql string from vb. does anyone know if this is correct, and if it is, does anyone know a way around it? my program sort of revolves around this working, it's a searching program

[peet]

use % not *

SELECT Files.[FileID], Files.[File Name], CDs.[CDID], CDs.[CD Name] FROM CDs INNER JOIN Files ON CDs.CDID = Files.CDID WHERE ((Files.[File Name]) Like 'gladiato%');

[Herbatic]

OK, while i was writing my last post i finally realised that the wildcards were the problem. So, stupidly, I searched vbforums for sql wildcards after hitting the submit button. and promptly found out that ado uses % instead of *, and _ instead of ?. So problem solved, sorry to have bothered everyone with such an easy question.

[peet]

[Herbatic]

OK. That makes a new problem. I am searching file names in my database, and filenames can have % or _ in them so what is the override-type thing in ado so that it will search for a % or _ instead of treating them as wildcards?

[Franky]

I am not sure but maybe %% and __ instead?

Franky

[peet]

No, I think you have to use brackets around the wildcard symbols if you want to search for them.

SELECT * FROM FileTable WHERE FileName LIKE '2002[%]04[_]%'

will give you files with the following mask

2002%04_ABC.DOC
2002%04_DEF.DOC
2002%04_TEST.DOC
.
.
.

[Herbatic]

Thanks All
peet - you were right the [ ] do the trick