Pass query result DateMax to variable 1

[Du2good]

I have the SQL below that will provide the MaxOfRptDt for a Report.

SELECT tblReportDates.Report, tblReportDates.Action, Max(tblReportDates.RptDt) AS MaxOfRptDt
FROM tblReportDates
GROUP BY tblReportDates.Report, tblReportDates.Action
HAVING (((tblReportDates.Report) Like "*CSH06") AND ((tblReportDates.Action)="Finish"));

I want to take the MaxOfRptDt value from the above strSQL and pass it to another strSQL, replacing Date()-3 with MaxOfRptDt.

"SELECT tblAccount.PI_id, tblAccount.External_Status_cd, tblAccount.Fraud_dt " & _
"INTO  Strqry &  " & _
"FROM tblAccount " & _
"WHERE (((tblAccount.Fraud_dt)>=Date()-3));"

A function that would return the MaxOfRptDt value from the first strSQL is my first guess. And then pass that to the 2nd strsql.... but I need help.
Thank you

Du2good

 

[Remou]

You could do this with a sub query, for example:
[tt]SELECT tblAccount.PI_id, tblAccount.External_Status_cd, tblAccount.Fraud_dt INTO Strqry
FROM tblAccount
WHERE (((tblAccount.Fraud_dt) In (SELECT Max(tblReportDates.RptDt) AS MaxOfRptDt
FROM tblReportDates
GROUP BY tblReportDates.Report, tblReportDates.Action
HAVING (((tblReportDates.Report) Like "*CSH06") AND ((tblReportDates.Action)="Finish"));
)));[/tt]

You will undoubtably get a better version if you post in the Microsoft: Access Queries and JET SQL forum701. Alternatively, you could use a recordset.

I am a little confused by your first query, is it possible for you to simply look up the max date? For example:
[tt]dteDate = DMax("RptDt", "tblReportDates", "Report Like '*CSH06' AND Action='Finish'")[/tt]

 

[Du2good]

Thank you very much Remou !!

The Dmax function is exactly what I needed. I should have been able to find a function that does the same as MAX in a query...I want to say Duh to myself.

Thank you for seeing through my confusion.

Plus I now have an understanding of subqueries!!

Thank you again, and have star for seeing through my haze!!
Du2Good