IIF Statement help

[nyasa05]

Can someone please help me out with an IIF statement - Essentially I want start date to be blank if the ending date is prior to 1/1/2017; start date to populate is start date is after 1/1/2017 and to be 1/1/17 if start date is prior to 1/1/17 and ending date is after 1/1/2017. I started out with the following and I am stuck:
=IIf([start_date]>#1/1/2017#,[start_date]," ",IIf([end_date]>#1/1/2017#,"1/1/2017"," "))

 

[plog]

start date to populate is start date is after 1/1/2017

You've caused a circular reference error in my brain. You want to populate a field based on the value in itself? Does not compute.

My advice is to write out what you want in English, then convert it to code. This is probably not right, but this is what I would start with:

If [ending date] < 1/1/2017 return blank
If [start date] > 1/1/2017 return [start date]
If [start date] < 1/1/2017 AND [ending date]>1/1/2017 return 1/1/2017

Write it out like that first, then we can convert it to code. As its written now---1. It doesn't make much sense and 2. It isn't all inclusive (e.g. what happens if [ending date] is 1/1/2017?)

 

[nyasa05]

Let me try to write it out:
IF start_date is greater than or equals to 1/1/2017 then return the start_date. And if the end_date is greater than or equals to 1/1/2017 return date of 1/1/2017 or leave it blank.

I hope I have cleared out any confusion.

 

[plog]

=Iif([start_date]>=#1/1/2017#, [start_date], Iif([end_date]>=#1/1/2017#, CDate("1/1/2017")))


If that doesn't work, provide [start_date] and [end_date] values for which it fails and what you want it to produce for those values.

 

[nyasa05]

So I have an end_date of 02/17/2017 and I expected to see a start_date of 1/1/17 but it's showing blank. I also have a start_date of 3/15/2017 that isn't showing.

 

[nyasa05]

User error issues on my end. That works perfectly. Thank you.