VBA: how to asign a value from a table to a variable 1

[SashaBuilder3]

Hi everybody,

I am wondering if there is a way to get a value from a table via SELECT statement and assign it to a variable in a VBA sub. For example, in Oracle it is possible to do the following:

SELECT SomeField INTO VarName WHERE...


Can anyone help with something similar for Access VBA?


Thanks

Alexandre

 

[BillPower]

This obviously isn't SQL but in the meantime here's one way to do it.

VarName = DLookup("[SomeField]", "SomeTable", "[AnotherSomeField] = SomeCriteria&quot

 

[beetee]

The DLookup solution looks good. However, if you haven't made a query from your SQL statement, you can try this approach:

However, you can say something like:
Dim MyVariable as variant
Dim MyRst
Set MyRst = CurrentDb.OpenRecordset(&quot;SELECT * FROM <tablename> WHERE <condition>&quot
MyRst.MoveFirst
MyVariable = MyRst(0)
MyRst.Close
Set MyRst = Nothing

 

[SashaBuilder3]

Billpower, Beetee,

Thanks for your help. I knew about the recordset way but it seemed to me a bit cumbersome to get just one value from a table. And I suspected there should have been another way. This DLookup function looks like what I was after.

Thanks a lot again!

Alexandre