How do I use queries in controlsource of a report textbox?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to pull a count of records that is not in the same table as the
record source of the report. The only way to do that is to use dlookup right
now through the expressions box in the controlsource. Dlookup is not
flexible. How can I use queries in this text box? Expressions seem to support
queries, but it gives me errors.
 
You cannot use an SQL statement directly as the Control Source of a text
box.

If you use a query as the RecordSource of your report, you may be able to
use a subquery to retrieve the value you need. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209066

If the subquery is not practical (e.g. "multi-level group by error" that
cannot be solved by stacking one query on another), you can write your own
function and use it in the Control Source of the text box. Surprisingly,
that's faster than using the built-in functions anyway. There's an example
in this article:
ELookup - an extended replacement for DLookup()
at:
http://members.iinet.net.au/~allenbrowne/ser-42.html

The example is more flexible that DLookup(), in that it accepts an extra
argument to specify which value you want returned.
 
Back
Top