Using an SQL query from a form (1 Viewer)

[tsp813]

I am currently working on a search feature for a database. What I have is a text box for entering a phrase and then a combo box for picking where to search.

Right now I'm not too concerned about getting the combo box to work, all I want is to have it so that when I pick Author in the combo box, it will search for an author. The values from the combo box are stored in the variable txtSearchType1 and the values from the text box are stored in txtSearchString1. I can use the information in the text box, but I am having trouble using the information from the combo box.

Here is what I have in code:

SELECT DISTINCTROW LIBRARY.NAME, LIBRARY.TYPE
FROM LIBRARY
WHERE (([Forms]![search]![txtSearchType1]) LIKE "*" & [Forms]![search]![txtSearchString1] & "*")
ORDER BY LIB.N1;

If I change the "[Forms]![search]![txtSearchType1]" part to LIBRARY.AUTHOR it works, but I don't know how to use it so that it uses the value from the combo box. Any help would be appreciated.

Thanks,
Tim

 

[tsp813]

The last line of the code should read "ORDER BY LIBRARY.NAME", just so you don't pick that out as a mistake.

 

[Pat Hartman]

You are referencing the form field twice, essentially comparing it to itself. The first reference needs to be replaced by the column name from the table.

BTW, there is no reason to hardcode this SQL. It is more efficient (and easier to test) to make it a stored querydef.

 

[tsp813]

Pat,

What I'm trying to do is get the actual column name from the combo box.

Tim

 

[Pat Hartman]

You can't use this method in a stored querydef. It is not possible to use parameters for any structural part of the query. Therefore, you cannot use a parameter to supply a column name at run time but you can use a parameter to provide a value that a specified column is compared to. i.e. your first usage of a form firld reference will NOT work but the second will.

You can do what you want if you are building the SQL in code, post the code.