Help! Syntax For Query Criteria Iif Date Range Referencing Form (1 Viewer)

Hi, I have a form with FromDate and a ToDate boxes. These boxes will only appear when a dropdown (called "Status") on the form is set to "Paid". I have a query that needs to be able to restrict the query results between this date range if the dropdown is showing "Paid", otherwise show all results.


The query field is called [DatePaid] and here is the criteria I have against it:

IIF(forms!Payments.Status = "Paid",Between forms!Payments.FromDate and forms!Payments.ToDate,[DatePaid])


When the dropdown isn't showing "Paid" it displays all the results (ie last part of criteria works), when the dropdown is showing "Paid" and the dates are filled in I get no results in the query. Obviously the Between statement is having issues being part of the IIF statement and I've tried every syntax option I can think of (>= "# " etc). Can someone please tell me how this needs written to work.

Many thanks!

MS1234 said:

These boxes will only appear...

Click to expand...

So if said boxes are not in use, they are invisible? In that case set the date/time values of those invisible boxes to be a large enough date range that they include ALL your data.
Then, if you show those boxes, clear them then. Then your query will always work, and you don't need an IFF() function, etc...
As one idea. Also, you can edit the SQL on the fly, in code, but that takes a little more skill in VBA.
hth
Mark

I think your logic is a bit off here.
Those boxes are only available if the status is "paid" so in your query I am guessing that the status field is available so put your "Paid" criteria in that, and just add the date fields criteria normally?


Edit - Markk's typing a great deal quicker than mine.... #tooslow

Thank you both. Mark - that works perfectly. Thank you very much!

Cross posted with another solution at http://www.utteraccess.com/forum/index.php?showtopic=2049673&st=0&gopid=2688547&#entry2688547