Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Angry Unanswered: DLookup working intermittently

    Hi All

    I'm working in Access 2K, and DLookup is being a pain (as usual).

    I'm trying to design a fairly simple database to capture and display statistics on the health of a website. Some of the stats requested by my manager^2 are:
    • Open helpdesk calls on a day
    • New calls opened that day
    • Absolute change in number of calls since the previous day
    • Relative change in number of calls since the previous day

    Now, I get a report of the total open calls each night, which generated after the poor brave souls on the helpdesk go home. This report includes a call creation date, so the first two stats are fine. In order to derive the other two stats, I'm going around the houses a little.
    I have one query to derive the most recent date for which I have call information, and one to derive the day before that. Using those two, I then have one query to derive the call number for the most recent date, and one to derive the call number for the day before that.
    I then use a couple of DLookups to obtain just the call stats and change:
    Code:
    SELECT    DLookUp("[Score]","qryCSRAllMostRecent2") AS [Last]
    ,    DLookUp("[Score]","qryCSRAllMostRecentButOne2") AS LastButOne
    ,    [Last]-[LastButOne] AS Change;
    This query, qryCSRAllDailyChange, works fine, and gives me the three numbers that I would expect.

    It all falls over when I try to assemble a query to insert a record in the main data table relating to this stat. So far I have:
    Code:
    SELECT    4 AS CheckID
    ,    DLookup("[CheckDate]","qryCSRAllMostRecent1") AS CheckDate
    ,    "Absolute change since previous day" AS CheckName;
    When I try to add
    Code:
    ,    DLookup("[Change]", "qryCSRAllDailyChange") AS Score
    to the SELECT statement above, I get the error message, "Query input must contain at least one table or query". Clicking the "Help" button on the message box gives me a blank help page, which is less than no help. I've tried running the function in a new blank query, and I've tried changing the name of the field to be returned, both with the same result.

    I should point out that I'm not using the criterion argument in any of the DLookup functions because the queries being looked in will only have one record each.

    Does anyone know what this error message means, and what I need to do to correct the situation? Any help would be gratefully received! It's a little frustrating that the function works some times but not others. I could write a VBA function to return the values that I need, but I'd prefer to avoid that because of the maintenance overhead.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My question would be why use DLookup at all? The DOMAIN aggregate functions are notoriously slow and should only really be used when there is no other option available.
    e.g.
    Code:
    SELECT  qryCSRAllMostRecent2.[Score] AS [Last]
          , qryCSRAllMostRecentButOne2.[Score] AS LastButOne
          , qryCSRAllMostRecent2.[Score]-qryCSRAllMostRecentButOne2.[Score] AS Change
    FROM    qryCSRAllMostRecentButOne2, qryCSRAllMostRecent2
    This is portable (ISO SQL compliant rather than using proprietary functions), efficient and I bet won't error (assuming there is nothing funny going on in the two referenced queries).

    Also, if I had to guess I bet the problem is because the third column of your first query referenced the two previous columns by their aliases.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Talking

    Thank you!

    I was using DLookup from force of habit. I should have remembered that the cartesian product of a series of one-record queries is a one-record output...

    I now have a query that derives the information I need!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •