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" may return one record: "fred" which I wish to store in a variable

I have found the following code in another thread, but I'm still a little confused on how to implement the "Execute" command.

Set rsMyValue = cnMyDB.Execute("Select Value from Table where key=2&quot
rsMyValue.MoveFirst
lngCurrentValue = rsMyValue.Fields("Value&quot

What is cnMyDB and what should I initialise it to?

Or maybe there is an easier way?

Cheers

 

[JonFer]

If you are just returning a single value, a simpler way is to use the DLookup function. Look up the help for examples as it can be confusing.

strValue = DLookup("Field","TableName","Key=2&quot

 

[astraltango]

thanks heaps, exactly what I need