DLookup in custom function not returning values

[Television]

Greetings!

I made a custom function to look certain value from table based on couple of criterias that it gets from query where I want to use it. Function's code is below:

Public Function PotteryWeights(strLocusID As Long, nrPotSubID As Long) As Variant
    Dim priSubID As Long
    Dim priLocusID As Long
    Dim priResult As Variant
    priSubID = nrPotSubID
    priLocusID = strLocusID
    Select Case priSubID
        Case Is = 1: priResult = DLookup("AmphoraWeight", "FindsLocusTable", "[ID] = " & [priLocusID])
        Case Is = 2: priResult = DLookup("PlainWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case Is = 3: priResult = DLookup("PompeianRedWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case Is = 4: priResult = DLookup("CookingWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case Is = 5: priResult = DLookup("TerraSigillataWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case Is = 6: priResult = DLookup("ThinWalledWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case Is = 7: priResult = DLookup("BlackGlossWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case Is = 8: priResult = DLookup("BlackFigureWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case Is = 9: priResult = DLookup("RedFigureWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case Is = 10: priResult = DLookup("BuccheroWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case Is = 11: priResult = DLookup("ImpastoWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case Is = 12: priResult = DLookup("DoliumWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case Is = 13: priResult = DLookup("LampWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case Is = 14: priResult = DLookup("OtherPotteryWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case Else: priResult = "No weight data found"
        PotteryWeights = priResult
    End Select
End Function

However, when I use it in query it only returns Case else - option and everything else is empty. Anyone could tell me what I am doing wrong here?
Thanks in advance!

Television

 

[pr2-eugin]

Your Select statement is a bit off. Try this..

Public Function PotteryWeights(priLocusID As Long, nrPotSubID As Long) As Variant
    Dim priResult As Variant
    Select Case nrPotSubID
        Case 1 
            priResult = DLookup("AmphoraWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case 2
            priResult = DLookup("PlainWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case 3
            priResult = DLookup("PompeianRedWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case 4
            priResult = DLookup("CookingWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case 5
            priResult = DLookup("TerraSigillataWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case 6
            priResult = DLookup("ThinWalledWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case 7
            priResult = DLookup("BlackGlossWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case 8
            priResult = DLookup("BlackFigureWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case 9
            priResult = DLookup("RedFigureWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case 10
            priResult = DLookup("BuccheroWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case 11
            priResult = DLookup("ImpastoWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case 12
            priResult = DLookup("DoliumWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case 13
            priResult = DLookup("LampWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case 14
            priResult = DLookup("OtherPotteryWeight", "FindsLocusTable", "[ID] = " & priLocusID)
        Case Else
            priResult = "No weight data found"
    End Select
[COLOR=Blue][B]    PotteryWeights = priResult[/B][/COLOR]
End Function

 

[Television]

That did it!
Thanks again Eugin!

Television

 

[pr2-eugin]

No problem. Glad to help.

 

[namliam]

Doing a series of DLookups like this is terribly inefficient, cant you re-write this to a join query?

 

[Television]

Doing a series of DLookups like this is terribly inefficient, cant you re-write this to a join query?

Unfortunately, that is not possible due to structure of out data (too long to explain). Fortunately our data amount is not overly large, so this DLookup function doesn't slow down query, where I use it. Thanks for concern thought.

Television

 

[namliam]

In either case you have a working solution... however I am concerned about your data structure here as well.... To me it looks like instead of having one you have:
ID, AmphoraWeight, PlainWeight, PompeianRedWeight, CookingWeight, ....

You would be better off normalizing the data to:
ID, PotSubID, Weight

As long as it works for you, guess thats the important part.

 

[Television]

In either case you have a working solution... however I am concerned about your data structure here as well.... To me it looks like instead of having one you have:
ID, AmphoraWeight, PlainWeight, PompeianRedWeight, CookingWeight, ....

You would be better off normalizing the data to:
ID, PotSubID, Weight

As long as it works for you, guess thats the important part.

You are probably right about normalization. Total pottery weights are located in their find unit table and finds are on their own table. Both table contains additional info. I probably should have put pottery weights to separate table in a manner you suggested and linked it to either unit table or find table. In my defense I can only say that I was in a hurry and was just started (have learned a lot since then). Well it works as it is and that is important part. If I have time I will change it.