Max Date Problem 2

[gardenia]

In my report, design view, my calculation works to get last date in a column of information:
=Max([Date])
My problem is that now I need the last 2 dates in the column and have tried different calculations but none work so far. Example: =Max("[Date]=2&quot
Please help. Thankyou.

 

[jrbarnett]

Create a query with the following SQL:

select top 2 date from table order by date desc

You then have the two records in a recordset and can pull them off with Max(queryname) and min(queryname)

John

 

[randysmid]

Hi,
There is another solution that can accomplish the same thing in VBA code in the OnFormat event of the report footer:
Dim db As Database, rsLast As Recordset
Set db = CurrentDb()
Set rsLast = db.OpenRecordset("Select County_name from County ORDER BY County_name DESC&quot
rsLast.MoveNext
txtLastMinus1 = rsLast![COUNTY_NAME]
Set rsLast = Nothing
Set db = Nothing

This code presumes the text box name on the report footer is called txtLastMinus1.


HTH,
Randy Smith
California Teachers Association

 

[gardenia]

MaxDate
I was able to select the last 2 dates but only for 1 company and product at a time. Is there a way to choose the last 2 order dates in one query for each company and each of the products they buy...ALL at ONE Time?
My SQL now:
SELECT TOP 2 [zPractice Query for Certain Companies].PRODUCT,[zPractice...Companies].CUSTOMER, [zPractice Query...Companies].DATE, [zPractice Query...Companies].[PREV ORD DATE],[zPractice Query...Companies].[Days Betwn],[zPractice Query...Companies].[ID Number]FROM [zPractice Query...Companies]WHERE((([zPractice Query...Companies].PRODUCT)="T&quotAND(([zPractice Query...Companies].CUSTOMER)="NB&quot)ORDER BY [zPractice Query for Certain Companies].DATE DESC, [zPractice Query...Companies].[Days Betwn]DESC;

Also tried 2nd suggestion of creating VBA in Footer of Report. Steps done: Created txt box with name "txtLastMinus1" under the "PRODUCT FOOTER" section. Highlighted "Product Footer" bar...properties...Event...OnFormat typed Dim db As Database, rsLast As Recordset Set db=CurrentDb()Set rsLast=db.Open Recordset("SELECT[Shipping and Into Process] ORDER BY [Shipping and Into Process].DATE DESC&quot rsLast.MoveNest txtLastMinus1=rsLast![Shipping and Into Process].Date Set rsLast=Nothing Set db=Nothing
When I went to run the report...gave error..."Microsoft Access can't find the macro 'Dim db As Database, rsLast As Rcordset Set db=Current DB() Set rsLast=db'

Please Help

Thanks Gail

 

[randysmid]

Hi,
I am not sure I understand the problem, but you can take the report footer code and place it in the group footer for the company, as well as the product.

HTH,
Randy Smith
California Teachers Association

 

[gardenia]

MaxDate Problem Continued
In a query you can retrieve the TOP 2 Order dates for one company....
Question...If a person has 6 companies, is there a way to create a query that gets the TOP 2 Order dates for each of the companies all within one query?

 

[Clifford77]

=Max([Date]) Current Date
=Max([Date]-1)Previous Date
=Max([Date]-2) The day berore the previous day

You can just do this in the text box on your form or report. Right click on the field and use the expression builder.

 

[Clifford77]

SYNTAX CORRECTION, sorry. The below is the correct syntax for your date problem.

=Max([Date]) Current Date
=Max([Date])-1 Previous Date
=Max([Date])-2 The day berore the previous day

Remember that this can be done on the report text box it self under the control source property.

 

[gardenia]

MAX DATE PROBLEM CONTINUED

Thank you to Clifford77 for your response. In response to Clifford77. Sorry...I guess I wasn't clear about my question. Doing =Max([Date]) gives you the last, let's say...Order Date a person has placed an order, for example May 30th, 2003. But,
=Max([Date])-1 gives you the Last Date - 1 day, so it would give you May 29th, 2003. What I am looking for though is the 2 last dates a person ordered, for example: Maybe they ordered May 30th, 2003 but previous to that, they ordered May 5th, 2003 and before that, April 20th, 2003.
What I am looking for is a query that gives the last 2 order dates that a company ordered...and one query that will give that information for each of the companies you have...all in one query. Is such a thing possible?
Please help. Thanks.