SQL Query Urgent Help Please !!!

[sjf]

Hi Guys, i really need a hand with this, I am trying to search multiple tables for a string match using the following SQL statement, it returns 0 records and I have no idea why ....

table1 = "teaokori"
table2 = "news"

SQL = "SELECT title AS one, LongDesc AS two, '"&table1&"' AS TableName "
SQL = SQL & "FROM teaokori WHERE (teaokori.LongDesc Like '%" & searchstring & "%') UNION ALL "
SQL = SQL & "SELECT title AS one, article AS two, '"&table2&"' AS TableName "
SQL = SQL & "FROM News WHERE (News.Article Like '%" & searchstring & "%');"

anyone got and suggestions?

 

[link9]

What is:

, '"&table1&"' AS TableName "

?? I wonder what is in the variable, table1. And I also wonder why you have enclosed it in single ticks -- this should not be necessary.

And also, you have to make sure that the 'LongDesc' and the 'article' are of compatible data types, or the union query will not work...

Also, I assume you are using SQL Server?? If you are not, and you are using Access, try using the * wildcard for the search...


Paul Prewett

 

[Ovatvvon]

Just quick note:
Access does use % for wildcard...I use it in some of the searches I have on one site.

 

[NiteCrawlr]

Try something like this:

"SELECT "&table1&" AS TableName"


this might work

 

[sjf]

thanks for the suggestions, I have finally got it working and it is nothing like the above ... will post when I can

ta
sjf