Using a SQL query as the Control Source for a text box in a report 1

[davidd31415]

Is it possible to use a SQL query as the Control Source for a text box in a report?

The table I am reporting, "Transactions," has a field "TestRequestID," which I am grouping by. I would like to display the TestRequestNumber (stored in the table "TestRequests") on the report.

I've tried entering this into the Control Source:

=SELECT TestRequestNumber FROM TestRequests WHERE TestRequests.TestRequestID = Transactions.TestRequestID

but I get the error "The syntax of the subquery in this expression is incorrect."

I've tried a few other syntaxes but always end up with the same error message.

Am I approaching this problem the wrong way?

 

[Remou]

You need DlookUp

[tt]=DlookUp("TestRequestNumber","TestRequests","TestRequestID = " & TestRequestID)[/tt]

Assuming that TestRequestID is numeric.

 

[davidd31415]

Excellent, works great.

Thank you Remou.