Query Criteria Expression

[StuInBerks]

Hi all, I wonder if anyone can help me? What I am trying to do is relativley simple but I am just having a problem executing the expression.

I have a search form which returns the list of results in a subform, in the search form (attached), there is a field which is to bring back results for contracts due in X amount of days, where the user inputs X. In the query results I only want this field to queried on if something is actually typed into the field. The field in the query I am writing this under is called [RenDate]. I have written a criteria expression in my query as follows, but am getting no results:

IIf(IsNull([Forms]![F Search]![days for renewal]),"",[RenDate] Between Date() And Date()+[Forms]![F Search]![days for renewal])

Everything else is working fine with this form/query. If anyone could help or advise on my expression it would be much appreciated.

Many thanks

Stuart

 

[neileg]

Extra set of brackets may help.

IIf(IsNull([Forms]![F Search]![days for renewal]),"",[RenDate] Between Date() And (Date()+[Forms]![F Search]![days for renewal]))

 

[StuInBerks]

Thanks Neil, but unfortnately no luck. I'm thinking the problem is with my initial condition of IsNull([Forms]![F Search]![days for renewal]). Anyone any ideas? Thanks

 

[EMP]

Try setting the criteria for the RenDate field in a new column in the query grid like the following.

--------------------------
Field: [RenDate] Between Date() And Date()+[Forms]![F Search]![days for renewal] Or [Forms]![F Search]![days for renewal] Is Null

Show: uncheck

Criteria: True
--------------------------

See Jon K's thread for explanation
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^