Code:
SELECT tbl_members.NameFirst, tbl_members.NameLast, tbl_members.Medical, tbl_members.DOB, Format(IIf(IsNull(tbl_members!DOB),"",IIf(IsNull(tbl_members!Medical),"",IIf(DateDiff("yyyy",tbl_members!DOB,tbl_members!Medical)+(Format(tbl_members!DOB,"mmdd")>Format(tbl_members!Medical,"mmdd")) Between 37 And 39,DateAdd("yyyy",42,DateAdd("d",-1,tbl_members!DOB)),IIf(DateDiff("yyyy",tbl_members!DOB,tbl_members!Medical)+(Format(tbl_members!DOB,"mmdd")>Format(tbl_members!Medical,"mmdd")) Between 40 And 64,DateAdd("yyyy",2,tbl_members!Medical),IIf(DateDiff("yyyy",tbl_members!DOB,tbl_members!Medical)+(Format(tbl_members!DOB,"mmdd")>Format(tbl_members!Medical,"mmdd")) Between 18 And 36,DateAdd("yyyy",5,tbl_members!Medical),"Error!"))))),"Medium Date") AS ExpiryDate, tbl_members.Status, tbl_members.Initials, tbl_members.CurrentRank, tbl_units.UnitNumber, tbl_units.Element, tbl_acis.ACI, tbl_acis.Element, tbl_acis.ShortGrouping, IIf(IsNull(tbl_members!Medical),"",DateDiff("d",Date(),[ExpiryDate])) AS DaysToExpiry
FROM (tbl_members INNER JOIN tbl_units ON tbl_members.UIC = tbl_units.ID) INNER JOIN tbl_acis ON tbl_units.ACI= tbl_acis.ID
WHERE (((tbl_members.Status)=1) AND ((IIf(IsNull([tbl_members]![Medical]),"",DateDiff("d",Date(),[ExpiryDate])))<1));
For a reason that I cannot determine, if I enable criteria on the filter, for example <1, it prompts with a screen "Enter Parameter Input" for ExpiryDate. If I don't have criteria entered, it does not ask for input.