Is the ELookUp or DLookUp function can be Brings all the rows ?

[m00j]

Is the ELookUp or DLookUp function can be Brings all the rows Equals with Criteria ?
I think it is Possible by Modified the Criteria ,, but i did not know how .. so who is know ?

plz add example

 

[pbaldy]

To the best of my knowledge, neither DLookup nor any of it's variants (such as Allen Browne's ELookup) will return more than one record. I think Allen's will handle a multi-value field.

 

[Galaxiom]

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.

 

[m00j]

i will be more Specific ,, actuality i need the ELookUp or DLookUp function retrieve all the value . that is i want .

is any way to

 

[m00j]

i am waiting

 

[pbaldy]

2 minutes and "i am waiting"? In a word, no.

 

[Galaxiom]

i am waiting

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.

 

[m00j]

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?

 

[DCrake]

Can you provide an example of what you are actually trying to achieve?

David

 

[m00j]

Can you provide an example of what you are actually trying to achieve?

David

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

 

[ChrisO]

The DLookup function only returns one value.
However, the DLookup function can return more than one field from the same record.

 

[Galaxiom]

The DLookup function only returns one value.
However, the DLookup function can return more than one field from the same record.

Yes, thankyou for the clarification.
DLookUp can return expressions including multiple fields from the same record.

DLookUp("field1&field2","table1","table1.somefield=1")

DLookUp("field1*field2","table1","table1.somefield=1")

 

[ChrisO]

Also like this: -

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 Sub

 

[Galaxiom]

That 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.

 

[m00j]

thanx ChrisO
thanx Galaxiom
really u are generous