displaying most recent date or highest value

A

Andrea

I want to display in a Report field only the most recent date from a column
of Review dates for each record.

for example:

Pier Equipment
 
A

Andrea

Opps! I hit return before first message was complete...continued below!

Andrea said:
I want to display in a Report field only the most recent date from a column
of Review dates for each record.

for example:

Pier Equipment
Click to expand...
Review Dates: 01/05/2002, 01/05/2003, 02/05/2004 ---only display 02/05/2004
review date for Pier Equipment show up on Report

Shop Equipment
Review Dates: 03/05/2001, 06/05/2003----only display 06/05/2003 review date
for Shop Equipment show up on report.........etc. so that all the most recent
review dates for the entire list of equipment is included in the report. My
results so far in my report includes all review dates for a Equipment record.
 
S

Steve Schapel

Andrea,

The specifics depend a little on what other data you also want on the
report. But it sounds like it would be good to base your Report on a
Totals Query. Make a query that includes the table(s) that contain the
data you want, select Totals from the View menu of query design, in the
Totals row of the EquipmentType column, put Group By, and in the Totals
row of the ReviewDate column, put Max.

--
Steve Schapel, Microsoft Access MVP

Opps! I hit return before first message was complete...continued below!

:

I want to display in a Report field only the most recent date from a column
of Review dates for each record.

for example:

Pier Equipment
Click to expand...

Review Dates: 01/05/2002, 01/05/2003, 02/05/2004 ---only display 02/05/2004
review date for Pier Equipment show up on Report

Shop Equipment
Review Dates: 03/05/2001, 06/05/2003----only display 06/05/2003 review date
for Shop Equipment show up on report.........etc. so that all the most recent
review dates for the entire list of equipment is included in the report. My
results so far in my report includes all review dates for a Equipment record.

Click to expand...
 
A

Andrea

Steve,

When I put Max in the Totals row on ReviewDate column of my Query then the
dates disappear from my report based on the Totals Query. What happened?


Steve Schapel said:
Andrea,

The specifics depend a little on what other data you also want on the
report. But it sounds like it would be good to base your Report on a
Totals Query. Make a query that includes the table(s) that contain the
data you want, select Totals from the View menu of query design, in the
Totals row of the EquipmentType column, put Group By, and in the Totals
row of the ReviewDate column, put Max.

--
Steve Schapel, Microsoft Access MVP

Opps! I hit return before first message was complete...continued below!

:

I want to display in a Report field only the most recent date from a column
of Review dates for each record.

for example:

Pier Equipment
Click to expand...

Review Dates: 01/05/2002, 01/05/2003, 02/05/2004 ---only display 02/05/2004
review date for Pier Equipment show up on Report

Shop Equipment
Review Dates: 03/05/2001, 06/05/2003----only display 06/05/2003 review date
for Shop Equipment show up on report.........etc. so that all the most recent
review dates for the entire list of equipment is included in the report. My
results so far in my report includes all review dates for a Equipment record.

Click to expand...
Click to expand...
 
S

Steve Schapel

Andrea,

The field your report is bound to will not longer be named ReviewDate.
Unless you enter an alias yourself into the query, Access will name it
by default MaxOfReviewDate. Make sure the ControlSource of the textbox
on the report is pointing to the correct field name, and also that the
contol's name is something other than ReviewDate. Let us know if this
helps.
 
Top