none
Dlookup, multiple criteria, in SQL query

    Question

  • Hi,

    I can't see why this doesn't work, perhaps it is to do with my syntax and multiple criteria.

    My SQL Query:
    SELECT tblCustomers.[_Name] AS Customer, [qryDC-CHARt1].Product, [qryDC-CHARt1].Type, [qryDC-CHARt1].Project AS Proj_ID, IIf([Proj_ID]=0,"GENERAL",[tblProjects_Header].[_Name]) AS Project, [qryDC-CHARt1].Application, tblProjects_Header.Market_sector, [qryDC-CHARt1].Qty, [qryDC-CHARt1].Sales_Currency, [qryDC-CHARt1].Sales_Price, [qryDC-CHARt1].Invoice_Date, DLookUp("[Technology]","[tblProjects_Products]","[Project] = '" & [Proj_ID] & "' AND [Product] = '" & [Product] & "'") AS Techny
    FROM ([qryDC-CHARt1] LEFT JOIN tblProjects_Header ON [qryDC-CHARt1].Project = tblProjects_Header.ID) INNER JOIN tblCustomers ON [qryDC-CHARt1].Customer = tblCustomers.ID;
    The part that is causing the issue is the Dlookup column on the end:
    Techny: DLookUp("[Technology]","[tblProjects_Products]","[Project] = '" & [Proj_ID] & "' AND [Product] = '" & [Product] & "'")

    Please help.

    Thanks,

    Charles


    Thursday, November 01, 2012 11:03 AM

Answers

  • The syntax per se looks OK, but at the moment the expression assumes that both Proj_ID and Product are of text data type.  If they are in fact of number data type then it would be:

    DLookUp("[Technology]","[tblProjects_Products]","[Project] = " & [Proj_ID] & " AND [Product] = " & [Product])

    I'd still go for a JOIN if possible, though.  As regards performance, be sure that all join columns are indexed.

    Ken Sheridan, Stafford, England

    • Marked as answer by Charles__Toray Thursday, November 01, 2012 2:51 PM
    Thursday, November 01, 2012 1:22 PM

All replies

  • It might be a mismatch on the data types or the fact that you are referencing the alias Proj_ID rather than the original column name, but rather than calling the DLookup function why not just join the tblProjects_Products table to the result set of the qryDC-CHARt1 query on:

    tblProjects_Products.Project = qryDC-CHARt1.Project AND tblProjects_Products.Product = qryDC-CHARt1.Product

    You can then include the Technology column in the SELECT clause.  Or use a subquery:

    (SELECT Technology
     FROM tblProjects_Products.Project
     WHERE tblProjects_Products.Project = qryDC-CHARt1.Project
     AND tblProjects_Products.Product = qryDC-CHARt1.Product) AS Techny

    If there is a possibility that the subquery could return more than one row use an arbitrary aggregation operator to force it to return a single row:

    (SELECT FIRST(Technology)
     FROM tblProjects_Products.Project
     WHERE tblProjects_Products.Project = qryDC-CHARt1.Project
     AND tblProjects_Products.Product = qryDC-CHARt1.Product) AS Techny

    Of course these alternative solutions don't rule out the possibility of a mismatch on the data types.  This could be the case if for instance you've used the dreaded lookup field wizard when designing the tables, in which case the values of the Project and/or Product tables would not be the text values you see, but a hidden numeric value.

    Ken Sheridan, Stafford, England

    Thursday, November 01, 2012 11:34 AM
  • Hi Ken,

    Thanks so much for getting back to me.  I did use a subquery but that makes the query take really long to run (there 791 expected rows returned):
    Techny: (SELECT Technology FROM tblProjects_Products WHERE Project = [qryDC-CHART1].Project and Product = [qryDC-CHART1].Product)

    This query is built over three queries becuase when I do it in one Access complains about amibiguity.  If I add the  tblProjects_Products to the second query (or the third) using a LEFT JOIN (INNER and RIGHT JOINs are ambiguous) I end up with 909 rows.

    I have tried using the actual field name [Project] and the alias [Proj_ID] but neither works.

    I have checked and [Project] is INT in both.  [Product] is also OK I think.

    I have not used the Lookup Query Wizard.

    I found that a DLookUp was quicker than the subquery and returned the correct results in another query I was using, although I didn't need to filter on multiple criteria.  That is why I suspected that it could be my syntax with the multiple criteria.

    Perhaps what I ought to be doing is writing this query better so that it joins up properly.  If so, I will post a new thread.

    What do you reckon?

    Thanks,

    Charles

    Thursday, November 01, 2012 1:04 PM
  • The syntax per se looks OK, but at the moment the expression assumes that both Proj_ID and Product are of text data type.  If they are in fact of number data type then it would be:

    DLookUp("[Technology]","[tblProjects_Products]","[Project] = " & [Proj_ID] & " AND [Product] = " & [Product])

    I'd still go for a JOIN if possible, though.  As regards performance, be sure that all join columns are indexed.

    Ken Sheridan, Stafford, England

    • Marked as answer by Charles__Toray Thursday, November 01, 2012 2:51 PM
    Thursday, November 01, 2012 1:22 PM
  • Ahhhhhhhhhhhh Ken you are a GENIUS!!!!

    Poject = integer
    Product = text

    I had original written it as you just suggested, i.e. if both were int.  Then I tried the one I posted, i.e. both were text.

    Can't believe I didn't realise it.

    Changed it now to:
    Technology: DLookUp("[Technology]","[tblProjects_Products]","Project =" & [qryDC-CHART1].[Project] & "AND [Product] = '" & [qryDC-CHART1].[Product] & "'")

    Works like a charm!!

    That said, I think you're right.  I should be able to do this with joins, otherwise my DB design is whack!!

    Please kindly look out for my next post on the topic of creating this query with crazy joins, probably sometime tomorrow - I will update this thread with the name of the new post.

    Thanks again!!!!

    Cheers,

    Charles

    Thursday, November 01, 2012 1:55 PM