SQL with Where clause

[funnydesi]

I have a MSaccess database with one table called table1. The table has 3 columns - ID, Medname and companyid. each medname has more than one companyid. I have an SQL statement which looks like this:

SELECT companyid from table1 WHERE medname='tylenol'

This works fine. But when I try to use a variable in which I have stored the medname input by a user, the very same SQL statement does not work. It does not give me any errors but no records shown either. with the variable name my SQL statement looks like this:

SELECT companyid from table1 WHERE medname=' &rsmed'

rsmed is where I store the user input medicine name. I did put in code to check what was contained in rsmed. It was ok and showed tylenol. but the sql statement doen not work. It looks very easy but I just cannot make it work.

Are there any restrictions to making the SQL dynamic or is it just the syntax. I am not sure what I am doing wrong. Any help will be appreciated.

Thanks in advance!!!

Funnydesi

[Rawhide]

How are you trying to run this query? In ASP code, in VB code, in an Access VBA module? Please post your code where you build the dynamic SQL. Also try outputting the whol SQL string to the interface (whatever that interface is) and see if it looks like it is correct.

By the way, don't forget your second ampersand:

SELECT companyid from table1 WHERE medname=' & rsmed & '

If you're building the SQL in ASP, it would look something like this:

SQL = "SELECT companyid from table1 WHERE medname='" & rsmed & "';"

[funnydesi]

Thank you very much for your help. It worked. Yes, I am using ASP code. As soon as I added the second & and the double quotes, it worked just fine.

Thanks again,

Funnydesi