Hi,

Does anyone know how to use variable names in SQL queries?  Here's my
problem:

I have a searchbox to find a record in a Dynaset.  I have a combo box that
lists all the field in my database and a text box that the user types in the
search pattern to look for.  Now, I can use a variable to represent the
search string, but I can't use a variable to represent the field name that I
want to search on.

ie.

  Select * From myTable WHERE COMPANY Like 'A*'

would return all the companies that start with A from myTable.

But, I want to replace myTable, COMPANY, and 'A*' all with variables.  That
way the user can choose what field to search on.  

I got the following syntax from the programmers reference:

                '" & variablename & "'

making the statement:

  Select * From myTable WHERE COMPANY Like '" & variable1 & "'

This line works, but when I try and change COMPANY to a variable, it fails.

Any ideas?  I can't imagine it being to different, but I am probably missing
something obvious.........  Any help is greatly appreciated.

Thanks.

Walter

Cordially,
dmc

are you by any chance trying to do something like
        "select * from MyTable where '" & CompanyStr & "' like etc"?
If this is the case then you should try
        "select * from MyTable where " & CompanyStr & " like etc"

Quotes should only surround string variables (considered as string
variables by SQL not by VB) so that tblname does not require quotes
around it whereas any string variable such as variable1 does.

Sometime I find it very useful to store the sql string in a variable
and then just display the completed sql string in a message box. If
what you see displayed looks like a valid sqlstring (taking special
care about the presence/absence of quotes) then it should be OK.

Fred.

Do not use the double set of quote marks for the column name, as SQL
only want quotes around literals.  If you look at the string in the
debug window, you can see what VB is building.  It should look like:

select * from MYTABALE where COMPANY like 'A%'

not like:

select * from MYTABALE where 'COMPANY' like 'A%'

Good luck
--
Mark Wagoner