Selecting which record for a report 1

[wx5chr]

I am trying to show only the most current record in the detail section of my report.

I have ABC, Version 1, effective 12/9/1993. I also have ABC, Version 2, effective 12/25/2003. I only want the record with the most recent effective date to be displayed.

Here's an example of the layout. Each new line is a grouping level, except for the ABC lines, which are in the detail section.

PENNSYLVANIA
PITTSBURGH
PITTSBURGH INTL
ABC VER 2 12/25/2003
ABC VER 1 12/9/1993

I tried adding a DMAX statement to the report query, but it only shows those cities with the latest date, which are not all the same. Any ideas??

 

[dhookom]

There are many solutions. One is to add a text box to the detail section:
Name: txtCount
COntrol Source: =1
Running Sum: OVer Group
Then add code to the On Format event of the detail section
Cancel = Me.txtCount>1

You can also modify your report's query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[wx5chr]

Thanks for the starting point. It's close, but it highlighted that I didn't give enough info. Here's a more indepth example of what my report is currently showing:

PENNSYLVANIA
PITTSBURGH
PITTSBURGH INTL
AAA VER 1 12/20/2003
ABC VER 2 12/25/2003
ABC VER 1 12/9/1993
CCC VER 1 11/18/2003

I need it to only show VER 2 (based on date, not version #) of ABC as well as AAA and CCC.

The ID for Pittsburgh Intl is part of the same table as AAA, VER, and date.

 

[dhookom]

Sort your report based on the proper fields so that the most recent version date comes first.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[wx5chr]

Thanks for you quick responses and patience with me.

I have the area sorted by type of procedure and then by date. Let me put in some real data to my example. I won't put in the state and city this time.

PITTSBURGH INTL
ILS RWY 22 ORIG 12/23/2003
NDB RWY 12 5 12/25/2003
NDB RWY 12 4 12/9/1993
RNAV RWY 33 1 11/19/2003
RNAV RWY 33 ORIG 3/5/2000

What I need to see is:

PITTSBURGH INTL
ILS RWY 22 ORIG 12/23/2003
NDB RWY 12 5 12/25/2003
RNAV RWY 33 1 11/19/2003

I tried a DMAX statement in the Query and the only procedure that was displayed was the NDB RWY 12, because it has the most recent date of all procedures, not just the most recent for the NDB RWY 12.

 

[dhookom]

It looks like you need to add another sorting and grouping level by the field containing "ILS RWY 22". I think if this is the level above the date field, you could drop all of your controls into the new grouping header and then don't use a detail section.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[wx5chr]

Thanks so much. This was driving me crazy! I think I did this once about 4 years ago.