Alternative to Dlookup which returns an Array of values. (1 Viewer)

What do you think about this function?

  • It is terribly written (the author sucks!).

    Votes: 1 20.0%

  • Interesting but might have some issues.

    Votes: 2 40.0%

  • Looks like it will work just fine.

    Votes: 1 20.0%

  • Great function! Nicely Coded.

    Votes: 1 20.0%

  • WOW! This just saved me a lot of headache!

    Votes: 1 20.0%
  • Total voters
    5
J

jxaxmxixn

New member
Local time
Today, 14:20
Joined
May 5, 2009
Messages
9
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...
Code: 
Dim AlookupRet As Variant
AlookupRet = Alookup("[Fd1], [Fd2], [Fd3]" , "[Table1]", "[Fd1] = 'something'")
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.

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
Here's the code without comments

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:
Guus2005

Guus2005

AWF VIP
Local time
Today, 20:20
Joined
Jun 26, 2007
Messages
2,641
You might want to test the speed against this one TLOOKUP.

I like your array return value. Might come in handy...

Enjoy!
 
J

jxaxmxixn

New member
Local time
Today, 14:20
Joined
May 5, 2009
Messages
9
Guus2005 said:
You might want to test the speed against this one TLOOKUP.

I like your array return value. Might come in handy...

Enjoy!
Click to expand...

You could set up the return values in the function declaration if you wanted to...

I'll test the speed when I get some time for sure...:eek:
If you get a chance to test it yourself please let me know how it turns out.
For now I just like the simplicity of it...:D

Thanks for the info!
 
T

thardyjackson

Registered User.
Local time
Today, 11:20
Joined
May 27, 2013
Messages
45
jxaxmxixn -- I really want to use your custom function in a SQL query but I must be getting the call syntax wrong.

I have a table called "tbl_products" that stores multiple columns about a product (prod name and default pricing values). I have another table called "tbl_customers" that includes "productChosen" (product they bought). I want to pull multiple values about the product each customer chose and then do some calculations on those values.

I used your Alookup custom function as is but inserted "Public" before the Alookup in the module.

I get Wrong # of arguments used with function in query expression 'AlookupRet(0' for this SQL code:

SELECT tbl_customers.productChosen,
Alookup("[product], [depositMinDefault], [pmtMo1Default]", "[tbl_products]", "[product]= '" & [productChosen] & "'") AS AlookupRet,AlookupRet(0) AS prod, AlookupRet(1) AS depositMin,AlookupRet(2) AS pmtMo1
FROM tbl_customers;
Click to expand...

If I remove the AlookupRet(#) values I get #ERROR for AlookupRet.

SELECT tbl_customers.productChosen,
Alookup("[product], [depositMinDefault], [pmtMo1Default]", "[tbl_products]", "[product]= '" & [productChosen] & "'") AS AlookupRet
FROM tbl_customers;
Click to expand...


Thanks!
Newbie, MS Access 2007, Windows 7
 
G

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:20
Joined
Jan 20, 2009
Messages
12,851
It isn't the solution you need. The query cannot accept an array.

Moreover I would advise against using the function in any situation as it is not well designed. Performance is my main criticism. ALookup would be rendered relatively inefficient by repeated DLookups in a loop.

Besides, a Dlookup can already accept multiple expressions. The only difference is they are returned as a single string in the Dlookup which could easily be Split to an array.
 
T

thardyjackson

Registered User.
Local time
Today, 11:20
Joined
May 27, 2013
Messages
45
Galaxiom -- Thanks. Interesting idea. I am trying it -- my syntax must be messed up. Can you see what I'm doing wrong? Cheers

I get #ERROR when I just use this SQL:

Code: 
SELECT tbl_customers.productChosen, Dlookup("[product]" & "[depositMinDefault]" & "[pmtMo1Default]","[tbl_products]","[product]= '" & [productChosen] & "'") AS ReturnString
FROM tbl_customers;


I get Undefined function 'Split' in expression when I use this SQL:

Code: 
SELECT tbl_customers.productChosen, Dlookup("[product]" & "[depositMinDefault]" & "[pmtMo1Default]","[tbl_products]","[product]= '" & [productChosen] & "'") AS ReturnString,
Split([ReturnString],",") AS ArrayResult,
ArrayResult(0) AS prod,
ArrayResult(1) AS depositMin,
ArrayResult(2) AS pmtMo1
FROM tbl_customers;

Dlookup works fine if I'm just requesting one value such as [depositMinDefault].

Thanks.
 
G

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:20
Joined
Jan 20, 2009
Messages
12,851
Split function cannot be used in Access SQL.

You really need to be joining the tables on the product.

You are probably looking for something more like this:
Code: 
SELECT C.[CustomerID], C.productChosen, P.depositMinDefault, P.pmtMo1Default
FROM tbl_customers As C
INNER JOIN tbl_Product As P
ON C.productChosen = P.product;

However something doesn't seem quite right with your data structure.

Please tell us more about what you are trying to achieve.
 
T

thardyjackson

Registered User.
Local time
Today, 11:20
Joined
May 27, 2013
Messages
45
Galaxiom -- Just had an aha moment where i realized I'm creating unnecessary DLOOKUP's. You are my hero. Cheers.
 
M

msanford88

New member
Local time
Today, 14:20
Joined
Jan 13, 2014
Messages
2
jxaxmxixn, I need some help using your Function. It isn't returning all the values needed. I put the.. well heres the code..

Code: 
Private Sub Product_AfterUpdate()
Dim AlookupRet As Variant
   
'Manufacturer = DLookup("[Manufacturer]", "[Raw Print]", "[ItemNumber] = " & "'" & Product & "'")
AlookupRet = Alookup("[Manufacturer]", "[Raw Print]", "[ItemNumber] = " & "'" & Product & "'")
AlookupRet = Manufacturer

Some of the products have multiple manufacturers. And I can't seem to get this to print out into my Manufacturer combo box.. Any and all help is wanted
 
Rx_

Rx_

Nothing In Moderation
Local time
Today, 12:20
Joined
Oct 22, 2009
Messages
2,803
Observation: Was just going through DLookup this weekend for a Query (not a form).
Query Example: MyResult1:(custom function with variable) MyDlookup1: Dlookup(...[MyResult1]...), ' now do this for 3 more times in the query

MyDLookup always runs first - and calls the custom function no matter its order in the query column.
Then MyResult1 runs the custom funtion again.
Since there were 4 custom functions and 4 DLookup - that was some serious overhead.

My thought was that DLookup just used the return value. Thought a DLookup would use the Query field "by Value" instead of "by Reference" That wasn't the case. DLookup ran the network query independently for each record.
A query doesn't have an acutal ByVal / ByRef. Just saying it acted this way.

Thought that the Custom Function could make a global variable, then have the DLookup use the Global variable's value. Problem, the DLookup runs first.

So, my solution was to use VBA Recordset code to create a table of values. Then execute a Join against that table.

NOTE: Linked to SQL Server not Access DB
ODBC might have changed the rules for SQL Server vs Access DB

For a Form, probably no big deal.
For evaluating thousands of records... a consideration.
For large recordsets, my intention is to avoid DLookup.
For forms or small reports, they are handy!
 
M

msanford88

New member
Local time
Today, 14:20
Joined
Jan 13, 2014
Messages
2
I did manage to get the code to work after switching the last line around. But it still isn't populating the data needed. It only returns one item. Some of our products have 2 or 3 manufacturers. Would I need to run a loop statement in AfterUpdate() function?
 
You must log in or register to reply here.

Similar threads

C
Invalid Use of Null
2
Replies
20
Views
519
chuckcoleman
C
oxicottin
Cant get form filter to work correctly
Replies
8
Views
198
plog
P
W
Converting IE access VBA code to work with Edge
Replies
4
Views
844
wackywoo105
W
J
Insert Records with VBA and get new autonum value - different variants and possible problems
Replies
5
Views
293
Josef P.
J
B
Retrieve 6 numbers from string
Replies
2
Views
200
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom