Date with IIF statement 2

[monkeysee]

I have a query that lists the type and dates of inspections for food facilities. Fields used:

FoodID
Priority (this will only have entries: 1, 2, 3)
InspectionDate

I wish to add a field : "Re-Inspection Date"
which will be based on the Priority & InspectionDate fields;

Examples:
If priority = 1, then the next inspection will be 4 months after the InspectionDate.

If priority = 2, then the next inspection will be 1 year after the InspectionDate.

If priority = 3, then the next inspection will be 2 years after the Inspection Date.

Can anyone help with building the "iff" statement or suggest alternative?

 

[lespaul]

you just need to add the additional criteria:

IIf(F.Priority=1 AND [Inspection.Type] = "routine", DateAdd('m',4,I.InspectionDate))

Leslie

 

[monkeysee]

PHV

Thanks! This statement IS now filtering the 'routine' criteria to calculate the reinspection date. But it will not allow me to enter a different date in the ReInspectionDate field if it is not routine.

this is what I have:


SELECT Inspections.InspectionID, Food.BusinessName, Food.Priority, Inspections.TypeID, Inspections.InspectorID, Inspections.InspectionDate,
IIf(Inspections.TypeID=2,
IIf(Food.Priority='1', DateAdd('m',4,Inspections.InspectionDate),
IIf(Food.Priority='2', DateAdd('yyyy',1,Inspections.InspectionDate),
IIf(Food.Priority='3', DateAdd('yyyy',2,Inspections.InspectionDate),
Inspections.InspectionDate))), [Enter new inspection date]) AS ReInspectionDate
FROM Food INNER JOIN Inspections ON Food.FacilityID=Inspections.FacilityID;

I removed the beginning "parameters' statement as it the message box came up before running the query.

any ideas?