Is the ELookUp or DLookUp function can be Brings all the rows ? (1 Viewer)
- Thread starter m00j
- Start date
The whole point of the DLookUp and its variants is to return a single value. DLookUp returns the first matching value. That is the first one it comes across. Don't make any assumptions about the order of the search.
Use a query if you want all matching records.
Use a query if you want all matching records.
No matter how long you wait or how much you want, DLookUp will never return more than one value.
That is: the value in the field specified in the first argument, from the table or query specified in the second argument and the first record that matches the third argument.
It cannot return all the values or all the records.
If you want all the records that match your criteria, you need to use a query or open the recordset in VBA.
dear friends ,,
let me asking u by another way ,,
Is there a function that can retrieve all values which are Compatible with Criteria?
hi Mr.David
really i am Happy Because your enjoying my add
and i am feeling your Nobility ,,the example what i need in the attach file.
Reality i am Succeeded but with Trick non Acceptable Because i use SubReport and i am think is available other way better my Solution.
thanx
Last edited:
Yes, thankyou for the clarification.
DLookUp can return expressions including multiple fields from the same record.
Code:
DLookUp("field1&field2","table1","table1.somefield=1")
Code:
DLookUp("field1*field2","table1","table1.somefield=1")
ChrisO
Registered User.
- Local time
- Today, 15:27
- Joined
- Apr 30, 2003
- Messages
- 3,202
Also like this: -
Code:
Sub Test()
Dim vntResult As Variant
vntResult = DLookup("[Systolic] & '|' & [Diastolic] & '|' & [Pulse]", "tblPressures", "[SampledAt] = #02/08/2009 8:36:52 AM#")
MsgBox "Systolic = " & Split(vntResult, "|")(0) & vbNewLine & _
"Diastolic = " & Split(vntResult, "|")(1) & vbNewLine & _
"Pulse = " & Split(vntResult, "|")(2)
End SubThat would certainly be better than three separate DLookUps.
You could improve on it by performing the Split once and writing the results to an array from where they would be displayed.
However in such a situation I would be inclined to open a Recordset using the criteria and read the values directly rather than using the DLookUp. Essentially this is the same as the actions taken by the DLookUp function but this technique would work from data queried directly from an SQL Server whereas the DLookUp would not.
You could improve on it by performing the Split once and writing the results to an array from where they would be displayed.
However in such a situation I would be inclined to open a Recordset using the criteria and read the values directly rather than using the DLookUp. Essentially this is the same as the actions taken by the DLookUp function but this technique would work from data queried directly from an SQL Server whereas the DLookUp would not.
