Skip to main content
Solved

use of IIF statement in Generic Inquiry with Today()

  • 12 October 2021
  • 4 replies
  • 1000 views

I have an “On Hold” AP Bills Generic inquiry to present same in a dashboard.  I tried to use the two example expressions in the Style column of a Generic Inquiry for APInvoice.Due.Date to shade invoice due dates pink (bad) when an AP Bill is past due.  However, what I’m getting is pink (bad) only when the DocDate and DueDate are equal dates or when the DueDate is blank (example: for a Debit Adj).  

 

Here are the two Style IIf statements that I tried.

 

=IIf(>APInvoice.DueDate]>Today(),'good','bad')

=IIf({APInvoice.DueDate]>@Today,’good’,’bad’)

 

Looking for the correct Style expression to shade the APInvoice.DueDate pink (bad) when an AP Bill is past its due date.  Thank you.

Perhaps you should try to use the DateDiff function, in order to determine if [DueDate] is later than today.

I wrote a similar one before. XML attached.  It’s filtered by BILL type.

The expression I used was:

=IIF ( ISNULL(LAPInvoice.DueDate],Today()) >= Today(), 'good','bad')

The ISNULL will work for other document types without a Due Date.

I hope this helps.

@bob39  Yes, it should work with this DateDiff fuction. Here is the sample example.

=  DateDiff( 'd', ,ARInvoice.DocDate], Today() )

 

 

So, I used DateDiff (Data Field) in the GI to create a Days Overdue column:

=IIf(DateDiff('d',[APInvoice.DueDate], Today())>0,DateDiff('d',[APInvoice.DueDate], Today()),0)

And then applied the desired Style this way:

=IIf(DateDiff('d',[APInvoice.DueDate], Today())>0,'bad','good')

The above ideas were new to me and work.  What I finally noticed is that someone manually edited one due date to be roughly 100 years from now.  LOL.  I’ll fix that data edit gaff, and I think we’re good.

I appreciate all the great ideas and data concepts shared.  Thank you all.

 

 

Reply


Terms & ConditionsCookie settings