use unrelated query in report footer

  • Thread starter Thread starter Art Marks
  • Start date Start date
A

Art Marks

I have a report based on a single table with groups on a date field by month
and year. In the date by year footer I want to include a text box with a
value given by a query (a TOP 1 query that returns a single value). I can't
figure out a syntax using either the query name or the SQL behind the query
that will work. When I try something like = [Queryname].[fieldname] it
thinks this is a parameter. Let me know if you need more information.

Thanks for any help,

--Art
 
Art said:
I have a report based on a single table with groups on a date field by month
and year. In the date by year footer I want to include a text box with a
value given by a query (a TOP 1 query that returns a single value). I can't
figure out a syntax using either the query name or the SQL behind the query
that will work. When I try something like = [Queryname].[fieldname] it
thinks this is a parameter. Let me know if you need more information.
Click to expand...


You can't refer to a query directly in an expression, but
you can use a function that retrieves a value from the
query. Since you only want a single value, the built-in
DLookup function will take care of it. Just use an
expression in the report's text box:

=DLookup("nameoffield", "nameofquer")
 
Worked great. Thanks.

--Art



Marshall Barton said:
Art said:
I have a report based on a single table with groups on a date field by
month
and year. In the date by year footer I want to include a text box with a
value given by a query (a TOP 1 query that returns a single value). I
can't
figure out a syntax using either the query name or the SQL behind the
query
that will work. When I try something like = [Queryname].[fieldname] it
thinks this is a parameter. Let me know if you need more information.
Click to expand...


You can't refer to a query directly in an expression, but
you can use a function that retrieves a value from the
query. Since you only want a single value, the built-in
DLookup function will take care of it. Just use an
expression in the report's text box:

=DLookup("nameoffield", "nameofquer")
Click to expand...
 
Back
Top