Storing value from sql query

[astraltango]

I want to run an SQL query to get a value from a table and store the result in a variable for further use.

How do I do this?

eg "select field1 from table1 where key = 121"

Cheers

 

[geofgan]

when you store a variable in sql statement just do this.

strkey='121'
select * from tablename where key = '" & strkey & "'

that is for string in using numbers you do " & strkey & "

good luck...

 

[astraltango]

not quite what I'm after, I actually want to store in the variable a field value returned from the SQL query

ie, query may return "fred" which I want to store for later use

I have since found the following code in another thread, but I'm still a little confused:
Set rsMyValue = cnMyDB.Execute("Select Value from Table where key=2&quot
rsMyValue.MoveFirst
lngCurrentValue = rsMyValue.Fields("Value&quot

rsMyValue is a recordSet right? What about cnMyDB? Do I use this if I'm working with Microsoft Access?

 

[vb5prgrmr]

astraltango, have you read FAQ222-2244 yet?

With most all data access the process works (very) generally like this ...

Open Connection To Database
Open Recordset Based Upon Which Connection?

So to answer your question the "cnMyDB" is probably the connection to the database. You can tell this by two things. The object variable "cnMyDB" has the prefix of "cn" (cn = connection), and by where it is used in the code. If you look at my very general example you will see "Open Recordset Based Upon Which Connection?" (Set rsMyValue = cnMyDB.Execute("...

And yes you will need to have a connection to your data source even if it is access.

Now, to store a value retrieved from the database you would use something like...

MyStringVariable = MyRecordSetObject.Fields("MyFieldName&quot
or
MyStringVariable = MyRecordSetObject.Fields("MyFieldName&quot.Value

Good Luck

 

[astraltango]

cheers vb5prgrmr, have read faq now

I have discovered that all I require is the use of DLookup