DLookup - Retreive more than 1 value from Table

[questionit]

We can get a value from a table with this method:

abc =10
getResult = DLookup("[name]", "[Table1]", "[age]='" & abc & "'")

What if the resultant values are more than one. How would i retreive those?

When age = 10 , i need to look for all the names with thier age=10. But with above method i get only one name (value) returned.

Any idea how to get the other values as well?

[JKing]

I would suggest using a recordset if you need more than one value.

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intAge As Integer
 
 
Set db = CurrentDb
Set rst = db.OpenRecordset("Select [Name],[Age] FROM Table1")
intAge = 10
 
 
rst.FindFirst "[Age] = " & intAge
 
Do Until rst.NoMatch
    'Perform actions with each name for example display each name 
    'in a message box
    MsgBox rst![Name]
    rst.FindNext "[Age] = " & intAge
Loop
 
rst.Close
Set rst = Nothing
Set db = Nothing
 

Not too sure what you're doing with each name but nevertheless you can modify the code in the loop to perform your wanted action.

[vkong85]

You would serve better to write a filter code then using dlookup. From what i know dlookup can only search for one value at a time.

[questionit]

I am keep on getting this error on this line:

Dim rst As DAO.recordset

Error: "user type-defined type not defined"

what does this mean?

I would suggest using a recordset if you need more than one value.

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intAge As Integer
 
 
Set db = CurrentDb
Set rst = db.OpenRecordset("Select [Name],[Age] FROM Table1")
intAge = 10
 
 
rst.FindFirst "[Age] = " & intAge
 
Do Until rst.NoMatch
    'Perform actions with each name for example display each name 
    'in a message box
    MsgBox rst![Name]
    rst.FindNext "[Age] = " & intAge
Loop
 
rst.Close
Set rst = Nothing
Set db = Nothing
 

Not too sure what you're doing with each name but nevertheless you can modify the code in the loop to perform your wanted action.