Critera to show first and last record. (1 Viewer)

[Dranoweb]

Hi,

I currently have a query containing a list of dates, among other things.

I would like to filter by criteria, the first and last record;

or

the earliest and latest dates.

I am sure this is possible, but my brain has not quite nutted it out yet..

Thanks in advance.

 

[John Big Booty]

You will first need to create a query that returns the Max and Min of your dates (a Totals/Group by query) You can then use the results of this query in a further query to pull up the related records for those two dates.

 

[Dranoweb]

In trying to create a summary report for min/max (using the wizard)
I have found that it will not create a min/max summary for dates, only number fields.

What you have described is what I am already attempting to achieve.

My question is rather HOW return the max and min.

I would prefer to be able to use an SQL statement in the query criteria if at all possible.

 

[Dranoweb]

Nevermind - I managed to do it with a single query.

i just added this line to the Criteria on my first query and it returns both the first and last date only.

Note my table is: PROJECTMEASURES Query1
and my Column is: REPORTDATE

=DMin("REPORTDATE","PROJECTMEASURES Query1") Or =DMax("REPORTDATE","PROJECTMEASURES Query1")

 

[John Big Booty]

Glad you got it sorted

As missinglinq is fond of reminding us; "There's ALWAYS more than one way to skin a cat!"

 

[Dranoweb]

Note a Problem, thanks for the time.