Using IIF and Date()

[Tony2021]

Experts,

I am using IIF to evaluate whether or not a date [InvDueDate] is between the criteria below.  It is not evaluating correctly in all cases since it returns "Payment is Pending" even when [InvDueDate] is outside the criteria.  I seem to remember that IIF is not the best way to do this.  Maybe there is a better way to write the expression (DateSerial or CDate?). 

Any help is appreciated.  

the below is in a text box on a report. 

=IIf([txtInvDueDate] Between Date() And Date()+20,"Payment is Pending","")

I have also tried the below using DateAdd but the results are the same:

=IIf([txtInvDueDate] Between Date() And DateAdd("d",20,Date()),"Payment is Pending","")

[#2]

You have to account for the fact that Date() generates a date with time of 00:00 AM. All dates, in fact, do have both date and time, but sometimes the time is not specified so it defaults to midnight.

Now, if the value in that Invoice Due Date field is also a date with a time, and that time is NOT 00:00AM, i.e. not midnight, it could fall outside your criteria by the number of hours in that time component.

Is that possibly the reason for this problem?

If I were doing this, I would indeed want to account for that by using DateValue() on the InvoiceDueDate field to limit the comparison to the date, without the times.

[#3]

@George Hepworth 

HI George, I cant say for sure if it has something to do with as you describe above. I looked at it further and I forgot I am using a Union and on one of the queries I put "" as InvDueDate for the column since the field InvDueDate is not in that table. I think its termed "dummy" column.

Below are the columns in the Union. I am only taking a snippet of the entire code. I am not sure if its advisable to use "" as I did below but I think I must since the field is not in the table. 
this is the first query in the union:
, "" AS InvDueDate,
this is the first query in the union:
,tblFXRollsChild.InvDueDate,
The correct invoice date is being returned by the Union though so to me using "" is not the issue (but it could be...see far below DateValue part) since its referring to the correct invoice date.

I dont have a format placed on the field.  I do nto see a time being added to the date in the datasheet display so to me it also doesnt seem likely its due to the time as you touched on above.  

Using DateValue()

I might be on to something. 

=IIf(DateValue([txtInvDueDate]) Between Date() And Date()+20,"Payment is Pending","")

It returned #Type on some of the records. 

It ight have something to do with me assigning "" to the InvDueDate? 

What else can I do than assign a "" for a dummy column?  

[#4]

I'd probably use Null and then change the expression to:

IIf( txtInvDueDate Is Not Null AND DateValue([txtInvDueDate]) Between Date() And Date()+20,"Payment is Pending","")

Try that.

If it doesn't work, try some impossible date value guaranteed to be outside the range, such as #2099/1/1#

[#5]

ok making progress. The "Payment Pending" is no longer showing. I now have a #type! displaying for some of those records.
I tried using the Date()+ 20 and also #2099/1/1# but have #type! for either way.

Do you have a suggestion on how I could return a blank when I get the #type! error? If I could do that then I think my problem is solved.

thank you

[#6 · Accepted solution]

That tells you the result is the wrong datatype. The control is expecting one datatype, but the expression is returning a different datatype.

And, with that, I see my mistake. You want to put either a valid date or a text string ("Payment is Pending") in that control. You can't put text into a date field. You'll have to present an alternative way to signal to the user that the payment is pending, a control that does accept text.

[#7]

Hi George, thank you for the assistance on this one. I ended up placing some conditional formatting on that field by looking at another date in the record set and shading it white (essentially turning the visible OFF). Might not be the best way to do it but Its seems to display what I want. thank you again for the help.

[#8]

Sometimes the simpler solution is the right one. There may be some additional code one can write, but usually, it's overkill, IMO.

Congrats on solving the problem.

[#9]

although solved, you can still use expression like this:

=IIf(CDate("0" & [txtInvDueDate]) Between Date() And Date()+20,"Payment is Pending","")

[#10]

thank you for the tip!
I was messing around with it yesterday and I used the following (using George's datevalue technique):
=IIf(IsDate([txtInvDueDate]),DateValue([txtInvDueDate]),"")
and once I did that then that seemed to clean it up and I could use the following without getting a #type!:
=IIf([txtInvDueDate] Between Date() And DateAdd("d",15,Date()),"Payment is Pending","")

I will add your expression to my cheat sheet. thanks!