469,631 Members | 1,218 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,631 developers. It's quick & easy.

DLookup - Retreive more than 1 value from Table

553 512MB
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?
Jul 19 '07 #1
3 3080
JKing
1,206 Expert 1GB
I would suggest using a recordset if you need more than one value.

Expand|Select|Wrap|Line Numbers
  1. Dim rst As DAO.Recordset
  2. Dim db As DAO.Database
  3. Dim intAge As Integer
  4.  
  5.  
  6. Set db = CurrentDb
  7. Set rst = db.OpenRecordset("Select [Name],[Age] FROM Table1")
  8. intAge = 10
  9.  
  10.  
  11. rst.FindFirst "[Age] = " & intAge
  12.  
  13. Do Until rst.NoMatch
  14.     'Perform actions with each name for example display each name 
  15.     'in a message box
  16.     MsgBox rst![Name]
  17.     rst.FindNext "[Age] = " & intAge
  18. Loop
  19.  
  20. rst.Close
  21. Set rst = Nothing
  22. Set db = Nothing
  23.  
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.
Jul 19 '07 #2
vkong85
24
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.
Jul 19 '07 #3
questionit
553 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. Dim rst As DAO.Recordset
  2. Dim db As DAO.Database
  3. Dim intAge As Integer
  4.  
  5.  
  6. Set db = CurrentDb
  7. Set rst = db.OpenRecordset("Select [Name],[Age] FROM Table1")
  8. intAge = 10
  9.  
  10.  
  11. rst.FindFirst "[Age] = " & intAge
  12.  
  13. Do Until rst.NoMatch
  14.     'Perform actions with each name for example display each name 
  15.     'in a message box
  16.     MsgBox rst![Name]
  17.     rst.FindNext "[Age] = " & intAge
  18. Loop
  19.  
  20. rst.Close
  21. Set rst = Nothing
  22. Set db = Nothing
  23.  
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.
Jul 19 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by JLM | last post: by
5 posts views Thread by Kalvin Schroder | last post: by
reply views Thread by Tony Williams | last post: by
2 posts views Thread by g.ormesher | last post: by
6 posts views Thread by Don Sealer | last post: by
21 posts views Thread by Thelma Lubkin | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.