Searching Database ?

[kinjalgp]

I am using the following statement to search through a database.
Set RS = DB.OpenRecordset("SELECT * FROM Table1 WHERE '" & SearchField & "' = '" & KeyWord & "'", dbOpenDynaset)

Where "SearchField" and "Keyword" are the variables entered by the program user. But the problem is that the above statement does not result in any records relating to user specified keyword but always displays the first record in the database.
No errors are shown at runtime.

Please help.


Kinjal

[Edited by kinjalgp on 06-27-2000 at 01:32 PM]

[JHausmann]

Misread it the first time, let's try again. Remove the single quotes that are bracketing the Searchfield.

Set RS = DB.OpenRecordset("SELECT * FROM Table1 WHERE " & SearchField & " = '" & KeyWord & "'", dbOpenDynaset)

[Edited by JHausmann on 06-27-2000 at 01:47 PM]

[kinjalgp]

The problem actually is if I enter the field name from a variable, the search does not work. But is I keep the field name fixed like in the following statement then the search function works fine.
Set RS = DB.OpenRecordset("SELECT * FROM Table1 WHERE Name = '" & KeyWord & "'", dbOpenDynaset)

[JHausmann]

if you set the SQL to a string variable and then examine the results in the immediate window (in debug mode), using Name as the keyword, the values for sSQL should be the same for:

dim sSQL as string

sSQL="SELECT * FROM Table1 WHERE Name = '" & KeyWord & "'"

Set RS = DB.OpenRecordset(sSQL , dbOpenDynaset)



and
dim sSQL as string

sSQL ="SELECT * FROM Table1 WHERE " & SearchField & " = '" & KeyWord & "'"

Set RS = DB.OpenRecordset(sSQL, dbOpenDynaset)

[kinjalgp]

No I think you misunderstood my question. I want both the field name and the keyword to be searched to be variales and not constants. That means the user can select from which field he wants to search and that is not happening in my statement. Maybe it's an syntax error.

[JHausmann]

I understand what you want. My last post attempted to show you how to determine if the SQL between a fixed column query was the same as the SQL for one that's variable. Your original SQL statement is wrapping the column name in quotes when it cannot be. Anything wrapped in quotes is treated as DATA in SQL, you need the column to be recognized as a column.

Have you tried the second part of the last post (variable column name, below)?

dim sSQL as string

sSQL ="SELECT * FROM Table1 WHERE " & SearchField & " = '" & KeyWord & "'"

Set RS = DB.OpenRecordset(sSQL, dbOpenDynaset)

Ideally you should be able to cut-n-paste the query, as it's returned in the immediate window, into whatever you're using to run stand-alone queries (Access, SQL Server query window) and get your results

[kinjalgp]

Hey man you are great. . Genius.

It worked. whee..pee

I was trying to solve it since last three days.


Thank you very very much.


Kinjal

[JHausmann]

From your post it looks like the user is entering table names, you might want to consider using a combo or list box for them to select from to avoid errors caused by typo's... HTH

[kinjalgp]

Yes you are right. I am done the same thing to avoid errors.