SQL wont work

[malhyp via AccessMonster.com]

Can anyone suggest why this SQL wont work?

Someone has suggested that I have a few " in the wrong place.
I am missing the basic understanding about variable / string concatenation.

SELECT SupplierName, Location, ShortDescription FROM Query1 WHERE
'TimberSpecies' LIKE '%" & " + Replace(rsSearchResult__MMColParam, "'", "''")
+ " & "%' AND 'CategoryTable' LIKE '%" & " + Replace
(rsSearchResult__MMColParam2, "'", "''") + " & "%' AND 'Location' LIKE '%" &
" + Replace(rsSearchResult__MMColParam3, "'", "''") + " & "%' ORDER BY
SupplierName"

Thanks

Mally.

 

[Rob Oldfield]

If you're using Access then the wildcard character is *. % is used in
T-SQL.

 

[John Spencer]

What are you trying to do? Are you trying to populate a variable with a
valid SQL string? Is this for an Access + Jet DB (.mdb) or for Access
Project (.adb) or are you linking to an SQL server or some other database
engine?

StrSQL = " SELECT SupplierName, Location, ShortDescription " & _
" FROM Query1 " & _
" WHERE TimberSpecies LIKE '%" & Replace(rsSearchResult__MMColParam, "'",
"''") & "%' " & _
" AND CategoryTable LIKE '%" & Replace (rsSearchResult__MMColParam2, "'",
"''") & "%' " & _
" AND Location LIKE '%" & Replace(rsSearchResult__MMColParam3, "'", "''")
& "%' " & _
" ORDER BY SupplierName"

If you are trying to do this with Access + Jet then the wild card character
is * not %

What is rsSearchResult_MMColParam. Is this a variable in your code?