I wrote this quick function (Alookup) which works just like Dlookup except it can return as many field values as you give it.
Function Alookup(Exprs As String, Domain As String, Criteria As String) As Variant
An example of how to use this function would be...
To get Fd1 you would use
Alookupret(0)
To get Fd2 you would use
Alookupret(1)
And so on...
You can read more about Dlookup here http://support.microsoft.com/kb/208786 which will better help you to understand how Alookup works.
Don't forget: The rest of Alookup works just like Dlookup, also requiring strict formatting for the Criteria. So make sure you take your time when writing your code or you'll probably get some errors.
Here's the code without comments
Function Alookup(Exprs As String, Domain As String, Criteria As String) As Variant
An example of how to use this function would be...
Code:
Dim AlookupRet As Variant
AlookupRet = Alookup("[Fd1], [Fd2], [Fd3]" , "[Table1]", "[Fd1] = 'something'")
Alookupret(0)
To get Fd2 you would use
Alookupret(1)
And so on...
You can read more about Dlookup here http://support.microsoft.com/kb/208786 which will better help you to understand how Alookup works.
Don't forget: The rest of Alookup works just like Dlookup, also requiring strict formatting for the Criteria. So make sure you take your time when writing your code or you'll probably get some errors.
Code:
[COLOR=Green]'Alookup - By Jamin Szczesny 25MAY09[/COLOR]
[COLOR=Green][COLOR=SeaGreen]'[COLOR=Green]Returns[/COLOR][/COLOR] an Array with values for each requested field item in the expression[/COLOR]
Function Alookup(Exprs As String, Domain As String, Criteria As String) As Variant
Dim x As Integer, AlookupOUT As Variant, DlookupRet As Variant, GotValues As Boolean
[COLOR=Green]'Split field expressions into an Array[/COLOR]
AlookupOUT = Split(Exprs, ",")
[COLOR=Green]'Loop for each field item[/COLOR]
For x = 0 To UBound(AlookupOUT)
DoEvents
[COLOR=Green]'Gather the data for the current field item[/COLOR]
DlookupRet = DLookup(AlookupOUT(x), Domain, Criteria)
If Not IsNull(DlookupRet) Then
AlookupOUT(x) = DlookupRet
GotValues = True [COLOR=Green]'got info from atleast one of the field items[/COLOR]
Else
[COLOR=Green]'Default return type for the current array element when
'[/COLOR][COLOR=Green]nothing is [/COLOR][COLOR=Green]found for the current field item[/COLOR]
AlookupOUT(x) = vbNullString [COLOR=Green]'Change to any return type you desire[/COLOR]
End If
Next x
If GotValues = True Then
[COLOR=Green]'we got some info[/COLOR]
Alookup = AlookupOUT
Else
[COLOR=Green]'got no info at all[/COLOR]
Alookup = Null
End If
End Function
Code:
[COLOR=Green]'Alookup - By Jamin Szczesny 25MAY09[/COLOR]
[COLOR=Green][COLOR=SeaGreen]'[COLOR=Green]Returns[/COLOR][/COLOR] an Array with values for each requested field item in the expression[/COLOR]
Function Alookup(Exprs As String, Domain As String, Criteria As String) As Variant
Dim x As Integer, AlookupOUT As Variant, DlookupRet As Variant, GotValues As Boolean
AlookupOUT = Split(Exprs, ",")
For x = 0 To UBound(AlookupOUT)
DoEvents
DlookupRet = DLookup(AlookupOUT(x), Domain, Criteria)
If Not IsNull(DlookupRet) Then
AlookupOUT(x) = DlookupRet
GotValues = True
Else
AlookupOUT(x) = vbNullString
End If
Next x
If GotValues = True Then
Alookup = AlookupOUT
Else
Alookup = Null
End If
End Function
Last edited: