use a variable name to filter records in where condition

[jimmib]

hello everyone,



i have been trying for days to get this to work. I keep getting syntax errors
I can't figure it out.

I want to use a variable that I have defined as stocksymbol in the where condition of the sql
the id is the key of the record

Dim stocksymbol as string

rst.Open "select * from stock where id = ", CurrentProject.Connection

I've tried everything .
Please help
Below is the code. This is so simple its driving me crazy


Private Sub Command22_Click()

Dim cnn1 As ADODB.Connection
Dim rst As ADODB.Recordset
dim stocksymbol as string



Set cnn1 = New ADODB.Connection
cnn1.Open CurrentProject.Connection
'
Set rst = New ADODB.Recordset 'assign the recordset
rst.LockType = adLockOptimistic
rst.Open "select * from stock where id = ", CurrentProject.Connection



rst.Close
Set rst = Nothing
cnn1.Close
End Sub


Thank you.

[Welshgasman]

I do not even see where you are trying to use it?

rst.Open "select * from stock where id = " & stocksymbol 

Google concatenation.

An ID field in my table would be the autonumber, so numeric. If not numeric, surround the variable with single quotes.

[June7]

And you do nothing with the recordset. What is your goal with this code?

In future, please post code between CODE tags - use # symbol on toolbar to generate.

[jimmib]

rather than hard code the name of the stock symbol i.e. 'msft'. I want to enter the symbol i want and then execute the query.
I can't figure out the syntax to identify the variable in the where id = stocksymbol . I have thried " ,&, ] . cant get it to work
I am selecting the value from a list box. I save me.list2 in the stocksymbol variable. I then want to select the symbol based on the variabl.e
Simple. Can't get the syntax right or its not doable?

[moke123]

"select * from stock where id = " & me.List2

Assuming the listbox is bound to column(0) and is numeric otherwise you need to indicate the column and delimit it.

if text

"select * from stock where id = """ & me.list2 & """"

[June7]

I save me.list2 in the stocksymbol variable.

Your code does not do this. However, as shown by moke123, you don't really need this variable. Regardless, you need to concatenate as shown by two replies.

Verify id is an autonumber type.

[jimmib]

Forget about the list box. Just SQL to select data from a table where the filter is a variable. This variable is defined by Dim statement.
What is the syntax for the where condition? Type a full example.
"Select * from stock where id = ??????????????????????

[madpiet]

Forget about the list box. Just SQL to select data from a table where the filter is a variable. This variable is defined by Dim statement.
What is the syntax for the where condition? Type a full example.
"Select * from stock where id = ??????????????????????

The problem is that Access needs a way to retrieve the user's choice for what the ID should be equal to.
ONE way to do it is to declare a Parameter in your query, and then use it in the filter. It's much easier to see in SQL Server:

DECLARE @Id INT; /* create a variable @Id of type INT */
SET @Id = 4; /* set the value... yeah, this is a stub! */

/* use the variable to filter the table */
SELECT *
FROM Stock WHERE Id = @Id

In Access, it's like this:
PARAMETERS [Date] DateTime;
SELECT Sales.ID, Sales.Date, Sales.Time, Sales.[Time Zone], Sales.Category, Sales.Item, Sales.Qty, Sales.[Price Point Name], Sales.SKU, Sales.[Gross Sales], Sales.Discounts, Sales.[Net Sales], Sales.[Transaction ID], Sales.[Customer Name]
FROM Sales;

(it automagically filters on the [Date] parameter without it ever being added to the WHERE clause, which is kinda weird and means you can't hijack the parameters to do something else)

[moke123]

Put your variable where Me.list2 is

[Welshgasman]

Forget about the list box. Just SQL to select data from a table where the filter is a variable. This variable is defined by Dim statement.
What is the syntax for the where condition? Type a full example.
"Select * from stock where id = ??????????????????????

What did you think is in post #2 ?
Do not demand anything, that will get you nowhere.

[madpiet]

Wait, so you mean you can't extort people that work for free??!!! What is this world coming to??!!

[jimmib]

moke123, your solution worked.
Thank you very much.

I apologize to anyone who thought I was demanding anything. I was not. The tone of the wording in my post may have been interpreted as a demand.
I assure you it was not.