DMAX

[pachad]

Using the code below, I am trying to get the latest facility info for each facility, based on user input. The facility detail table stores the info, and only has rows for when data changes i.e.:
[tt]
FACILITYCODE MONTHNO YEARNO RATE
PLANT_A 1 2006 100
PLANT_A 4 2006 110
PLANT_A 6 2006 125
PLANT_B 2 2006 105
[/tt]
If the user enters [For month] 5 and [For year] 2006
I want to retreive 4/06 for plant A and 2/06 for plant B, and if the user enters [For month] 3 and [For year] 2006
I want to retreive 1/06 for plant A and 2/06 for plant B.

SELECT tblFacilities.FacilityCode, tblFacility_Detail.Rate, DateSerial([tblfacility_detail.yearno],[tblfacility_detail.monthno],1) AS DetailDate
FROM tblFacilities INNER JOIN tblFacility_Detail ON tblFacilities.FacilityCode = tblFacility_Detail.FacilityCode
WHERE (((DateSerial([tblfacility_detail.yearno],[tblfacility_detail.monthno],1)) In (SELECT DISTINCT DMax("DateSerial([tblfacility_detail.YearNo], [tblfacility_detail.MonthNo],1)","tblfacility_detail","DateSerial([tblfacility_detail.YearNo], [tblfacility_detail.MonthNo],1) <=DateSerial("+[For Year] +","+[For Month]+",1)") AS MaxMonth
FROM tblfacility_detail));

This code works. The problem is that when I go to close and save the query, access pops up an error "Missing ( ] or |" and highlights the "IN" criteria field above.

PLEASE HELP!

 

[addy]

In your IN statement, you have 5 ( and 6 ) symbols.

Try removing one of the last ) fro the end of your statement.