Dlookup question

[Guest]

can you use dlookup to find more than 1 item from a table e.g.,
dlookup("Field1","field2",[tblName],field 1=0)

 

[Guest]

No, you'll need to open a RecordSet

 

[Douglas J. Steele]

Sort of.

DLookup will only return a single value, but you can have it concatenate
fields together. For example, you could have it return field1 and field2
separated by a comma, and then split the values apart afterwards.

Dim strField1 As String
Dim strField2 As String
Dim strReturn As String

strReturn = DLookup("[Field1] & ',' & [Field2]", _
"[tblName]", "[Field1] = 0")

strField1 = Left(strReturn, InStr(strReturn, ",") - 1)
strField2 = Mid(strReturn, InStr(strReturn, ",") + 1)

 

[Guest]

Amaizing, I had no Idea.
So you can actually return unlimited amount of fields

Dim strField1 As String
Dim strField2 As String
Dim strField3 As String
Dim strReturn As String

strReturn = DLookup("[Field1] & ',' & [Field2] & ',' & [Field3]", _
"[tblName]", "[Field1] = 0")

strField1 = split(strReturn ,",")(0)
strField2 = split(strReturn ,",")(1)
strField3 = split(strReturn ,",")(2)

Thank you, Douglas

Sort of.

DLookup will only return a single value, but you can have it concatenate
fields together. For example, you could have it return field1 and field2
separated by a comma, and then split the values apart afterwards.

Dim strField1 As String
Dim strField2 As String
Dim strReturn As String

strReturn = DLookup("[Field1] & ',' & [Field2]", _
"[tblName]", "[Field1] = 0")

strField1 = Left(strReturn, InStr(strReturn, ",") - 1)
strField2 = Mid(strReturn, InStr(strReturn, ",") + 1)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

can you use dlookup to find more than 1 item from a table e.g.,
dlookup("Field1","field2",[tblName],field 1=0)