Unanswered: DLookup working intermittently
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:
This query, qryCSRAllDailyChange, works fine, and gives me the three numbers that I would expect.
SELECT DLookUp("[Score]","qryCSRAllMostRecent2") AS [Last]
, DLookUp("[Score]","qryCSRAllMostRecentButOne2") AS LastButOne
, [Last]-[LastButOne] AS Change;
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:
When I try to add
SELECT 4 AS CheckID
, DLookup("[CheckDate]","qryCSRAllMostRecent1") AS CheckDate
, "Absolute change since previous day" AS CheckName;
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.
, DLookup("[Change]", "qryCSRAllDailyChange") AS Score
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.