SQL Statement not working...why??

[WhizzkidWallace]

Hello,

I have created the query as shown below, and it works fine when I click the View button, but when I use the SQL from it to create an ADODB recordset it contains no records!!

Its part of an enhanced Switchboard I have, and it is driving me nuts!!! Can somebody please shed some light?

SELECT [Switchboard Items].SwitchboardID, [Switchboard Items].Description
FROM [Switchboard Items]
WHERE ((([Switchboard Items].Description) Like '*'+'print'+'*'))

If I remove the wildcards and use the exact search it works, but I need it to be a 'contains' type of search.

Thanks...it will save my sanity.....

 

[Mile-O]

SELECT [Switchboard Items].SwitchboardID, [Switchboard Items].Description
FROM [Switchboard Items]
WHERE ((([Switchboard Items].Description) Like '*'+'print'+'*'))

First of all, get out of the habit of using + for concatenation. Use &. The + is better served in maths. The & is the correct concatenation character.

Also, since print is a string you don't need all this concatenation.

WHERE ((([Switchboard Items].Description) Like '*print*'))

My personal preference is to ditch the ' too and replace it with two "

i.e.

WHERE ((([Switchboard Items].Description) Like ""*print*""))

 

[Mile-O]

However, is your Description field, by any chance, a MEMO field type?

 

[WhizzkidWallace]

It was....I tried changing it to text, which made no diff. I have solved it now from another forum..yipee..the solution was changing the * wildcard to the more generic %

The code that works is :

strSQL = "SELECT * FROM [Switchboard Items] WHERE Description like '%" & Trim(strSearch) & "%'"

Thanks for your help though...

Every possible variatn involving the * would not work!!

 

[Mile-O]

Are you using SQL Server?