Query Asking For Input after enabling Criteria

[AdamN]

I am using Access 2003 and am having problems with a Query.

Attached is the SQL 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.

What I am trying to filter the criteria where the date difference is less than 1 day or the meidcal date field is null.



I am sure I am not understanding something correctly, but I just don't know what.

[ssanfu]

To set a criteria, you need a field, an operator and a value. The first part of the WHERE clause is

WHERE (((tbl_members.Status)=1)

So the field is "STATUS", the operator is "Equals" and the value is 1.
In the 2nd part of the criteria, you have

((IIf(IsNull([tbl_members]![Medical]),"",DateDiff("d",Date(),[ExpiryDate])))<1));

No field, no operator, just a value??? (the result of the IIF() function)

[AdamN]

I think I figured out my issue, now just trying to solve it. Once I do I will post solution.