473,386 Members | 1,606 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

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 3263
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

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

Similar topics

6
by: JLM | last post by:
What am I missing here? I have a form where I enter a "Class Code". This value corresponds to what sits in table "class code descriptions" along with the "title" of each "class code." Key...
5
by: Kalvin Schroder | last post by:
I am fairly new to Access, and am trying to put together an invoice form. The main form in called InvoiceDetailFm. Source is the table InvoiceDetail and has invoice number, saleman, and CustID as...
0
by: Tony Williams | last post by:
I have posted a number of posts in the Access newsgroups concerning my problem with DLookup. I have had a number of the experts with helpful suggestions but I still can't get it to work! This is...
2
by: g.ormesher | last post by:
Hi, my primary key is a field called "ind" 'Code Dim varUP as Varient Dim varIND as Interger varIND = Me.ind ' this sets varIND to the index value
6
by: Don Sealer | last post by:
I've written this expression for a DLookup function. It works almost alright. What I'm trying to do is type in a description and the ID field (number) populates automatically. It works almost as...
21
by: Thelma Lubkin | last post by:
I would like my DLookup criteria to say this: Trim(fieldX) = strVar: myVar = _ DLookup("someField", "someTable", "Trim(fieldX) = '" & strVar & '") I don't believe that this will work, and I...
3
by: Patrick A | last post by:
All, My form "FRM_Main" contains 2 subforms, each an instance of the form "FRM_Testators" The object name of instance 1 of the form is "SFRM_Clients". The object name of instance 2 of the form...
3
by: Constantine AI | last post by:
Hi can anybody help me with this problem? I have a customer order form with a sales order line subform. The subform contains products ordered by customers, each (wooden) product can come in two...
15
by: rleepac | last post by:
This is a little complicated but I'll do my best to explain. In my db I have a table called L_AgeCorrection which has the following fields: Age, Sex, Frequency, AgeValue This is a table used to...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.