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.

[RuralGuy]

You would need to use a RecordSet and a SELECT statement Marianne.

[marianne]

can you give me an example code RG?

[RuralGuy]

It would look something like:

Public Function Fooo() As Variant
   Dim MyRs As DAO.Recordset, MySQL As String
   MySQL = "SELECT fieldname " & _
           "FROM tablename " & _
           "WHERE criteria = y"
   Set MyRs = CurrentDb().OpenRecordset(MySQL, dbOpenDynaset)
   Fooo = MyRs![fieldname]
   MyRs.Close
   Set MyRs = Nothing
End Function

...which is basically what DLookup does anyway.