This code:

WHERE TimberSpecies LIKE '%MMColParam%'

searches for text that contains the text MMColParam, not the variable's value.

Probably easiest to see how this works is to switch to the Advanced tab and create your query there, using the Variables list to add your variables. Then look at the resulting code and tweak it if necessary.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thanks Imar, I will have to play with it tomorro. Cant keep my eyes open. Early morning here. Thanks for your help.

Mally.

Hm, little lost here.

Ok can you explain what we mean by this line.

"WHERE TimberSpecies LIKE '%MMColParam%'searches for text that contains the text MMColParam, not the variable's value."

What is the difference between having the MMColParam & Variable?

So that means that my code should read.

SELECT *
FROM Query1
WHERE TimberSpecies LIKE '%varsearch%'

varsearch % Request("keywordSearch")

Actualy I up to this SQL code now. No errors, but no search results at all.

SELECT SupplierName, Location, ShortDescription
FROM Query1
WHERE 'TimberSpecies' LIKE '%MMColParam%' AND 'CategoryTable' LIKE '%MMColParam2%' AND 'Location' LIKE '%MMColParam3%'

MMColParam 1 Request.Form("keywordSearch")
MMColParam2 2 Request.Form("Location")
MMColParam3 3 Request.Form("CategoryTable")

Mally.

The fact you get no results is due to what I tried to describe earlier.

This:

WHERE 'TimberSpecies' LIKE '%MMColParam%'

searches for records that contain the actual text MMColParam, not it's underlying value. It's highly unlikely you have the text MMColParam somewhere in your database.

To get the value of the variable, you'll need to concatenate it to the WHERE clause:

WHERE 'TimberSpecies' LIKE '%" & MMColParam & "%'

This adds the value of MMColParam to the where clause. So, if MMColParam contains Mally, your WHERE clause ends up like this:

WHERE 'TimberSpecies' LIKE '%Mally%'

which searches for records that have the text Mally in them.

Does this help??

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Rob D - Clubbed To Death (kurayamino mix) by The Matrix (Track 4 from the album: The Matrix - OMPST) What's This?

Imar, thank it dose. I understand now. Will post my code as soon as I have completed it.

Thanks for the explanation.

Mally.

Also a small note. Change of subject. When I post a topic on the Wrox website, I get the following message.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key row in object 'FORUM_SUBSCRIPTIONS' with unique index 'IX_FORUM_SUBSCRIPTIONS_DUPCHECK'.

/post_info.asp, line 2058

This appears everytime straight after a post.

mally.

Arrrrk.

Hey there, sorry ab out this. I have changed my SQL in the recordset to read the following;

SQL
SELECT SupplierName, Location, ShortDescription
FROM Query1
WHERE 'TimberSpecies' LIKE '%" & MMColParam & "%' AND 'CategoryTable' LIKE '%"& MMColParam2 & "%' AND 'Location' LIKE '%"& MMColParam3 & "%'

VARIABLES
MMColParam 1 Request.Form("keywordSearch")
MMColParam2 2 Request.Form("CategoryTable")
MMColParam3 3 Request.Form("Location")

I am getting a error message which reads;

Microsoft JET Database Engine error '80040e14'
Syntax error in string in query expression ''TimberSpecies' LIKE '% + Replace(rsSearchResult__MMColParam,'.
/html/results.asp, line 55

LINE 55 READS
<%
Dim rsSearchResult
Dim rsSearchResult_numRows
Set rsSearchResult = Server.CreateObject("ADODB.Recordset")
rsSearchResult.ActiveConnection = MM_connTimberseek_STRING
rsSearchResult.Source = "SELECT SupplierName, Location, ShortDescription FROM Query1 WHERE 'TimberSpecies' LIKE '%" & " + Replace(rsSearchResult__MMColParam, "'", "''") + " & "%' AND 'CategoryTable' LIKE '%"& " + Replace(rsSearchResult__MMColParam2, "'", "''") + " & "%' AND 'Location' LIKE '%"& " + Replace(rsSearchResult__MMColParam3, "'", "''") + " & "%'"
rsSearchResult.CursorType = 0
rsSearchResult.CursorLocation = 2
rsSearchResult.LockType = 1
rsSearchResult.Open() <---LINE 55
rsSearchResult_numRows = 0
%>

Also can you suggest a good book that can teach all this SQL. Seems like I am going to have to learn how to use it.

Mally.

Can you post the outcome of the following statements:

Response.Write(rsSearchResult.Source)
Response.End()

If you add those lines AFTER the Source has been set in the code you just posted, you can see the actual SQL that's passed to the database. That makes it a lot easier to see where a possible problem is.

Re: SQL books: if you want to know the nitty gritty of T-SQL: check out "Professional SQL Server 2000 Programming" (http://www.wrox.com/WileyCDA/WroxTit...764543792.html)

It mainly targets SQL Server, but you'll find that it'll help you understand general concepts that apply to other databases as well.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

What do we mean by this?

"Can you post the outcome of the following statements:
Response.Write(rsSearchResult.Source)
Response.End()"

Mally.