Multiple Criteria with IIF

[james28]

Hello all!

I am trying to understand the programming behind an expression in access 2010.

I have 2 text boxes that are used as a start and end date (txtstartdate and txtenddate) on a FORM (form is testform).

Also on the form is a button that runs a query.


The query is designed and has a date column. In the criteria of the date column, I have the following code.

Between [Forms]![testform]![txtstartdate] And [Forms]![testform]![txtenddate]



Everything works perfectly and I am able to use my two text boxes with dates to limit what is returned from the query.




To add in extra flexibility, I would like the query to be able to handle a few other events.


Ideally, I would like to have it so that if nothing is entered in either text box, the query gives all entries without filtering at all.

If txtstartdate has a date, but txtenddate does not, I would like it to display all dates equal to or after txtstartdate.

Likewise, if txtstartdate is blank, but txtenddate has a date, I would like it to display all dates equal to or before txtenddate.





To accomplish this, I have tried to modify my existing expression, but I am not sure to how account for all situations, nor can I get it to work even slightly with the IIF.


This is what I am trying:


Like IIf([forms]![testform]![txtstartdate] is NULL and [forms]![testform]![txtenddate] is NULL,"*",Between [Forms]![testform]![txtstartdate] And [Forms]![testform]![txtenddate])



Any idea how I can make the expression do what I described above?


Thank you!

[June7]

Try:

Between Nz([Forms]![testform]![txtstartdate],"1/1/1900") And Nz([Forms]![testform]![txtenddate],"12/31/2900")

[james28]

June,


Are you saying that I would use the piece that you listed instead of what I listed before?


Like IIf([forms]![testform]![txtstartdate] is NULL and [forms]![testform]![txtenddate] is NULL,"*",Between Nz([Forms]![testform]![txtstartdate],"1/1/1900") And Nz([Forms]![testform]![txtenddate],"12/31/2900"))




Thank you!

[June7]

Replace all of your expression with my suggestion, don't use LIKE.

[james28]

June,


Once again, you are amazing!



That does it exactly what I was after!


THANK YOU!

[james28]

June,


The previous reply for a query criteria is still working perfectly.


I am now trying to nearly the same logic, but for a text box on a report. The queries in the report are being filtered by Between Nz([Forms]![testform]![txtstartdate],"1/1/1900") And Nz([Forms]![testform]![txtenddate],"12/31/2900").

I would a text box on the top of the report to show the Date range that the report is based on. Since it will have to be readable by people, I would prefer to leave off the 1/1/1900 and 12/31/2900.


Ideally, it would work as follows.


Situation 1: If [Forms]![testform]![txtstartdate] has a date and [Forms]![testform]![txtenddate] has a date = Display "[txtstartdate] to [txtenddate]"


Situation 2: If [Forms]![testform]![txtstartdate] has a date and [Forms]![testform]![txtenddate] is blank = Display "[txtstartdate] to present "

Situation 3: If [Forms]![testform]![txtstartdate] is blank and [Forms]![testform]![txtenddate] has a date = Display "Before [txtenddate]"


Situation 4: If [Forms]![testform]![txtstartdate] is blank and [Forms]![testform]![txtenddate] is blank = Display "No Date criteria"




This is what I built, but it does not work to account for all possible situations. It accounts for situation 2 and 3, but not 1 and 4. In order to get 4, I think there needs to be an IIF around the the entire function, saying if both boxes are null, but I am not sure how to go about that. Also, for situation 1, I think there may need to be an iif built solely for the " to ".

Nz([Forms]![testform]![txtstartdate],"Before ") & Nz([Forms]![testform]![txtenddate]," to present")




Any suggestion that you could provide would be great.


Thank you!

[June7]

Yes, use nested IIf expressions. They could be rather long and complicated just because of repeated referencing of the form control names.

Alternative is VBA code to build the string. The code can be behind the report in a function that is called by a textbox. Or it can be in button Click event that opens report and passes string to report with OpenArgs argument of OpenReport method. Textbox ControlSource would be: =[OpenArgs].

[binbin]

This was a great find, I didn't quit get the result from the iif criteria and like, when I found this and made some change, my filter form is working now.

I wanted the query to show me every record before the date in a textbox and all null dates also if somehow the textbox was left blank, to show me every record before today.

<=Nz([Forms]![FilterForm![txtDate],Date()) Or Is Null

It's working now, trying it out makes me smile every time, so great, was at it for hours trying to figure it out!

Thank you for starting this thread and of course for solving it!