Nested DLookup

[jamarogers]

Hi All and thanks again for this outstanding site and forums. It seems everything I try to do with Acess involves hours of study, experimentation and visiting here, often with questions...perhaps that's just the way it is! Anyway, I'm back to share a solution and hopefully get some comments/ideas; if there is a simpler, more efficient way to do this. I've attached a pic of my relationship map. Here is my "nested DLookup" expression, which I FINALLY got to work, but as you might imagine is pretty complex and prone to MANY syntax errors...=DLookUp("[SaleTaxRate]","StateTaxes",'[StAbbr] = DLookup("[StA]","Customers","[CusLName]=Forms![SOs]![SOCusLName] and [CusFName]=Forms![SOs]![SOCusFName]")').

Ultimately I will use this "LookedUp" SaleTaxRate to calculate Taxes for a SaleOrder. The rate will of course vary, depending on the State of residence of the Customer.

Am I over complicating things here? It is new to me since in this case, I must navigate back through TWO tables to get my SaleTaxRate... Thanks for any advise or reference
Jake

[June7]

Gotta get the quote marks and concatenation right. Try:

=DLookUp("[SaleTaxRate]","StateTaxes","[StAbbr] = '" & DLookup("[StA]","Customers","[CusLName]='" & Forms![SOs]![SOCusLName] & "' And [CusFName]='" & Forms![SOs]![SOCusFName] & "'"))

Names are bad criteria unique identifiers. What if you have 5 customers named John Smith? Customers should have a unique ID - autonumber field type could serve.

[jamarogers]

Your code does NOT work...mine DOES! Please re read my original post for my question.
Also since CusFName and CusLName are key fields, duplicates would not be allowed...

[ItsMe]

Your code does NOT work...mine DOES! Please re read my original post for my question.
Also since CusFName and CusLName are key fields, duplicates would not be allowed...

I read your post and your question. To me, your post comes across as one big contradiction. As for your question, no, you are not overcomplicating things. It seems like you are oversimplifying things.

One way is having names as a non duplicating Key.

Another way of oversimplifying things is having a sales tax table based on State alone. Maybe you are trying to make a DB to track sales over the internet and you don't care about local sales taxes, I don't know. But yeah, seems like you are not considering a lot of things.

[June7]

Last name and first name as primary keys? Still a bad idea. What if you have more than one customer named John Smith or Mary Jones?

I don't know why your expression works. I've used nested domain aggregate functions before and this one doesn't make sense to me. The criteria argument has quote marks inside apostrophes - that's backwards, never seen it. It also has variables (the entire inner DLookup as well as reference to form controls) within apostrophes and quotes. Variable within apostrophe/quote marks results in the variable as a literal string, not the content of the variable.

[jamarogers]

It works!!! Try mine then try yours. I believe the " and ' are interchangeable, with neither taking priority over the other. I agree and index would be easier to deal with than two name fields...I actually had that originally but changed for some reason I can't recall. One can always add I, II, III for the few (if any duplicate) names you run into...a small business here. Thanks for your input. Do you think my original post is "contradictory"? Seems logical to me...but then what do I know?

[June7]

Contradictory in that you are looking for simpler but complicating with use of names as keys and a compound key at that. I'd do just about anything to avoid compound keys.

I do think the ' and " matter in SQL statements and since the WHERE CONDITION of domain aggregate follows SQL syntax, I would have thought it matters there as well. Still don't understand why the variables within quotes give you good results but as long as you are satisfied...

[jamarogers]

FYI, I just discovered that DLookup has a bit different syntax when used as an expression (mine above that worked, which was indeed an unbound Form expression) and when used in VBA code, where it did NOT work. After making changes to the VBA code, which were similar to your recommendation, it did indeed work! I don't remember Access being this "picky" when I used it in the "XP version" days, but I am muddling along. I seem to solve one problem but create another in the process...getting very frustration for an old man! Thanks again for your help...I'll be back, I'm sure!
Jake

[ssanfu]

I know this is marked solved but I thought I would toss in my $0.02.

I see you are using text fields as primary keys.... this is considered a bad idea. Plus Long Integers are faster.
See http://www.bluemoosetech.com/microso...Primary%20Keys

Also, you might read
"7 Ways to Make Database Administration a Nightmare"
http://www.bluemoosetech.com/relatio...0a%20Nightmare


Be aware you have used reserved words, "TYPE" & "STATE", in two tables.
Here is a list of reserved words:
http://allenbrowne.com/AppIssueBadWord.html


As to the DLOOKUP(), I would write a UDF. Much cleaner IMO.

In a control, you would use
=GetTaxRate(CustomerLName, CustomerFName)

In a query, you could use
Tax:GetTaxRate(CustomerLName, CustomerFName)

The code

Option Compare Database  '<<should be at the top of every module
Option Explicit          '<<should be at the top of every module

Public Function GetTaxRate(PLName As String, pFName As String) As Single
    Dim r As DAO.Recordset
    Dim sSQL As String

    sSQL = "SELECT StateTaxes.SalesTaxRate"
    sSQL = sSQL & " FROM StateTaxes INNER JOIN Customers ON StateTaxes.StAbbr = Customers.Sta"
    sSQL = sSQL & " WHERE CustomerLName = '" & PLName & "' AND CustomerFName = '" & pFName & "';"

    Set r = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot, dbFailOnError)
    If r.BOF And r.EOF Then
        GetTaxRate = 0
    Else
        GetTaxRate = r.Fields("SalesTaxRate")
    End If

End Function