Getting max and min dates in a report

  • Thread starter Thread starter LordTico via AccessMonster.com
  • Start date Start date
L

LordTico via AccessMonster.com

Hi
I have a Billing Report. This one reports all the bills from one date to
another. This dates are specified in a previous form with filters. So you
apply the filter in the form, press a button and you get a report based in
the form with the same filters.
I want to print in the report "Billing Report from [first date] to [last
date]" (to know from when to when are the bills being listed)
For this I've created 2 textbox controls with the following data source
property for each one
=max([date])
=min([date])
I've also tryied with first and last but nothing seems to work (because
max/min and first and last doesn't work with dates.
I can't use dmax/dmin neither dfirt/dlast because the data source is a
report not a table.
any suggestions???
thx
Tico
 
LordTico said:
I have a Billing Report. This one reports all the bills from one date to
another. This dates are specified in a previous form with filters. So you
apply the filter in the form, press a button and you get a report based in
the form with the same filters.
I want to print in the report "Billing Report from [first date] to [last
date]" (to know from when to when are the bills being listed)
For this I've created 2 textbox controls with the following data source
property for each one
=max([date])
=min([date])
I've also tryied with first and last but nothing seems to work (because
max/min and first and last doesn't work with dates.
I can't use dmax/dmin neither dfirt/dlast because the data source is a
report not a table.
Click to expand...


Max and Min work fine with Date fields. Are you really
using a field named "Date" in the table? That's the name of
a built in function and there may be confusion about which
one you are trying to refer to.

If you want to display the values the user specified in the
form, you can use this kind of reference in the report text
box:
=Forms!theform.txtStartDate
exactly the same way you do in the query.
 
Hi Marshall, thx for your reply.
Ok, I've follow you but I'm still stuck.

I've used this for the text box in the report with this source (is in
spanish):
=M?n(Informes!relacion_facturas!fecha)
(in english would be:)
=Min(reports!bill_report!date)

But nothing happens...only an error...
thx again
Tico
 
LordTico said:
Hi Marshall, thx for your reply.
Ok, I've follow you but I'm still stuck.

I've used this for the text box in the report with this source (is in
spanish):
=M?n(Informes!relacion_facturas!fecha)
(in english would be:)
=Min(reports!bill_report!date)

But nothing happens...only an error...
Click to expand...


FIrst, the aggregate functions only operate on **fields** in
the report's record source table/query. They are unaware of
**controls** on a form or report.

In the case above, you have no need for Min because the
value is already in the form's text box. Try using:

=Informes!relacion_facturas!fecha

To get the actual minimum date that appears in the report
(not necessarily the value in the form), you should be able
to use:
=Min([nameofdatefield])
as long as the name of the date **field** isn't causing the
problem.
 
Back
Top