How to put result query in report footer

[DetRich]

Hello,

I am creating a simple report to provide details on lease expirations for
our servers.
In the report footer, I'd like to put a simple count of the number of
servers that appear in the report detail.

The simple query is something like
select count (*)
From tblLeases
where (LeaseEndDate - Now() < 30).

How do I put the number returned in a text box in the report footer?

Thanks,
Rich

 

[fredg]

Hello,

I am creating a simple report to provide details on lease expirations for
our servers.
In the report footer, I'd like to put a simple count of the number of
servers that appear in the report detail.

The simple query is something like
select count (*)
From tblLeases
where (LeaseEndDate - Now() < 30).

How do I put the number returned in a text box in the report footer?

Thanks,
Rich

Your "simple" Query is not going to work.
I assume it's supposed to return just one value.

After you correct the syntax and get the count value into a named
field, you can use DLookUp.

Using an Unbound text control:
=DLookUp("NameOfTheCountField","QueryName")

You do not have to actually run the query.

 

[Marshall Barton]

I am creating a simple report to provide details on lease expirations for
our servers.
In the report footer, I'd like to put a simple count of the number of
servers that appear in the report detail.

The simple query is something like
select count (*)
From tblLeases
where (LeaseEndDate - Now() < 30).

How do I put the number returned in a text box in the report footer?

Try using a text box expression like:
=DCount("*", "tblLeases", "LeaseEndDate - Date() < 30)

 

[DetRich]

Thanks for the reply Fred.

Here is the actual SQL query that I would like to run, along with the
DLookup...

select count (*) As Total
From tblHWConfig
Where (LeaseEndDate - Now() < 90)

=DLookup ("Count (*)", "tblHWConfig", "(LeaseEndDate - Now() < 90))

Do I simply use the DLookup statement as the control source for the text box?

Note that as it is right now, the DLookup statement produces an #Error on
the report. Not sure why...


~~Rich

 

[fredg]

Thanks for the reply Fred.

Here is the actual SQL query that I would like to run, along with the
DLookup...

select count (*) As Total
From tblHWConfig
Where (LeaseEndDate - Now() < 90)

=DLookup ("Count (*)", "tblHWConfig", "(LeaseEndDate - Now() < 90))

Do I simply use the DLookup statement as the control source for the text box?

Note that as it is right now, the DLookup statement produces an #Error on
the report. Not sure why...

~~Rich

No.
You are using a query to return the count value.
To show this value in the report, you simply use DLookUp (without
criteria) to read that one value.
For the DLookUp, it's the [Total] column that contains the count
value.
tblHWConfig appears to be the name of the table that is being counted,
not the name of the query itself. You must use the query name here.
Criteria in the DLookUp in the report is not needed as the query is
returning just the one value.

So...

=DLookup ("[Total]", "TheQueryName")

Make sure the name of this control in the report is NOT the same as
the name of any field used in it's control source expression.

Note: In your query criteria, because Now() contains the time of day
as well as the date, in some instances the result might vary according
to the time of day you run this. Use Date() instead of Now().

Where (LeaseEndDate - Date() < 90)

You could also by-pass the query entirely and use DCount on the table
instead:
In the report:
=DCount("*","tblHWConfig","[LeaseEndDate] - Date() < 90")

Note that in the DCount above, "[LeaseEndDate] - Date() < 90" is
enclosed entirely within double quotes.

Make sure the name of this control in the report is NOT the same as
the name of any field used in it's control source expression.

 

[DetRich]

Thanks Fred,

I got it now. Works like a charm!!!

Thanks for the reply Fred.

Here is the actual SQL query that I would like to run, along with the
DLookup...

select count (*) As Total
From tblHWConfig
Where (LeaseEndDate - Now() < 90)

=DLookup ("Count (*)", "tblHWConfig", "(LeaseEndDate - Now() < 90))

Do I simply use the DLookup statement as the control source for the text box?

Note that as it is right now, the DLookup statement produces an #Error on
the report. Not sure why...

~~Rich

No.
You are using a query to return the count value.
To show this value in the report, you simply use DLookUp (without
criteria) to read that one value.
For the DLookUp, it's the [Total] column that contains the count
value.
tblHWConfig appears to be the name of the table that is being counted,
not the name of the query itself. You must use the query name here.
Criteria in the DLookUp in the report is not needed as the query is
returning just the one value.

So...

=DLookup ("[Total]", "TheQueryName")

Make sure the name of this control in the report is NOT the same as
the name of any field used in it's control source expression.

Note: In your query criteria, because Now() contains the time of day
as well as the date, in some instances the result might vary according
to the time of day you run this. Use Date() instead of Now().

Where (LeaseEndDate - Date() < 90)

You could also by-pass the query entirely and use DCount on the table
instead:
In the report:
=DCount("*","tblHWConfig","[LeaseEndDate] - Date() < 90")

Note that in the DCount above, "[LeaseEndDate] - Date() < 90" is
enclosed entirely within double quotes.

Make sure the name of this control in the report is NOT the same as
the name of any field used in it's control source expression.