dlookup versus SQL value search

[marianne]

to search for values using coding, we can use dlookup to find the values we need, like:

x=dlookup("fieldname","tablename","criteria=y")

so there we get the value for x.

how can I use SQL to get the value of x in a table?

thanks.

 

[Atomic Shrimp]

There might be a more elegant way, but I would do it like this (aircode, untested):

Dim db as Database
Dim rst as Recordset
dim StrSQL as String

set db= CurrentDB()
strSQL = "SELECT TableName.Fieldname FROM TableName WHERE {Your criteria};"
rst=db.OpenRecordSet(strSQL)
x=rst.Fields!Fieldname

Set rst=Nothing
set db=Nothing

Like DLookup, if there are multiple rows that match your criteria, this will only find one of them.

 

[gemma-the-husky]

but if you only want a single value, dlookup must surely be the fastest and most optimised way.

 

[jjturner]

Use a Subquery.

Although not intimately familiar with the native processes of DLookup vs Subquery, I would tend to think the SQL solution requires less 'overhead' than a DLookup.

 

[marianne]

yah i am looking for single value only gemma. but is it true and dlookup is more faster than sql? how is that?