Last Date entered to show in query

[monkeysee]

My data base consists of restaurant data and includes inspection dates. I need to have a query which will only show the last date entered in the "inspection date" field in addition to what I have now. My sql is as follows:

SELECT Inspections.InspectionID, Food.BusinessName, Food.Priority, Inspections.TypeID, Inspections.InspectorID, Inspections.InspectionDate, IIf([Priority]="1",DateAdd("m",4,[InspectionDate]),IIf([Priority]="2",DateAdd("yyyy",1,[InspectionDate]),DateAdd("yyyy",2,[InspectionDate]))) AS ReInspectionDate, Inspections.FacilityID, Food.Status, Food.NameID, Food.AddressLocationID, Food.CertifiedManager, Address.CityName, License.LicenseNo
FROM (Address RIGHT JOIN (Food INNER JOIN Inspections ON Food.FacilityID = Inspections.FacilityID) ON Address.AddressLocationID = Food.MailingAddressID) LEFT JOIN License ON Food.FacilityID = License.FacilityID
WHERE (((Inspections.TypeID)=1 Or (Inspections.TypeID)=2) AND ((Inspections.InspectionDate)<Date()) AND ((IIf([Priority]="1",DateAdd("m",4,[InspectionDate]),IIf([Priority]="2",DateAdd("yyyy",1,[InspectionDate]),DateAdd("yyyy",2,[InspectionDate]))))<Date()) AND ((Food.Status)="active"))
ORDER BY Inspections.InspectionDate DESC , IIf([Priority]="1",DateAdd("m",4,[InspectionDate]),IIf([Priority]="2",DateAdd("yyyy",1,[InspectionDate]),DateAdd("yyyy",2,[InspectionDate])));


As it is now, it is not picking up recent inspections.

 

[Crowley16]

a combination of the TOP and ORDER BY DESC will do the trick

--------------------
Procrastinate Now!

 

[monkeysee]

The "Order by Desc" is in the sql - but is not returning the last date

 

[Golom]

You may be having a problem because of this

AND ((IIf([Priority]="1",DateAdd("m",4,[InspectionDate]),
      IIf([Priority]="2",DateAdd("yyyy",1,[InspectionDate]),
          DateAdd("yyyy",2,[InspectionDate])))) < Date())

That's going to eliminate any records where the computed date is on or after the current date but you are adding 4 months, 1 year or 2 years to the InspectionDate. That means that any Inspection Date that is less than 4 months, 1 year or 2 years in the past (depending on priority) will not be included.