calling a query from a report textbox

Question

I have a report based on a number of different queries. Most of the queries use the value of a row's customerID textbox as a key to extract specific data from other fields.

Here is what I have for the Control Source property of one of the textboxes:

=DLookUp([Level],[qryLevel],[Me].[customerID])

Here is the SQL for qryLevel:

SELECT TOP 1 Level, myDate FROM sometable WHERE custID=Me.customerID ORDER BY myDate DESC

qryLevel works when tested independently, but the DLookUp function does not seem to be working properly because Access gives a dialog box asking for each parameter and then outputs #NAME? in the textbox when no values are input into the dialog boxes.

How can I get each of these textboxes to output its own result from a separate query?

Yawar Yawar · Accepted Answer · 2013-10-04T00:14:58

DLookup function arguments must all be strings: http://allenbrowne.com/casu-07.html

So for the first two arguments, just enclose them in double-quotes.

For the last argument, the documentation says it's equivalent to a SQL where clause, without the word 'where'. Actually since you're returning only a single record from your query you probably don't need the last argument at all:

=DLookup("[Level]", "[qryLevel]")

Although, I don't see how qryLevel can work as an independent query since it refers to Me which implies a container object. Better to express as:

SELECT TOP 1 Level, myDate
FROM sometable
WHERE custID = [Forms]![MyForm]![customerID]
ORDER BY myDate DESC

... which will work in any context--inside or outside a form.

calling a query from a report textbox

1 Answers