Past Due Invoices by payment terms

[all7holly]

Hello,
The query I have below. Is returning records with the Payment Terms for NET 30 are showing as “15-30 Days Past Due”. The NET 30 payment terms should show as “31-60 Days Past Due” and the NET 15 payment terms should show as “15-30 Days Past Due”. I hope I’m explaining myself? Any ideas?
Holly

PaymentStatus: IIf([tbl report and invoices sending]![payment terms]="Due Upon Receipt","Due Upon Receipt Past Due",IIf(Date()-[invoice date sent]>=365,"365 Days Past Due",IIf(Date()-[invoice date sent]>=180,"180 Days Past Due",IIf(Date()-[invoice date sent]>=150,"150 Days Past Due",IIf(Date()-[invoice date sent]>=120,"120 Days Past Due",IIf(Date()-[invoice date sent]>=90,"90 Days Past Due",IIf(Date()-[invoice date sent]>=60 And Date()-[invoice date sent]<90,"61-90 Days Past Due",IIf(Date()-[invoice date sent]>=30 And Date()-[invoice date sent]<60,"31-60 Days Past Due",IIf(Date()-[invoice date sent]>=15 And [invoice date sent]>31,"15-30 Days Past Due","Current")))))))))

 

[Alansidman]

What happens if you change it to read:

PaymentStatus: IIf([tbl report and invoices sending]![payment terms]="Due Upon Receipt","Due Upon Receipt Past Due",IIf(Date()-[invoice date sent]>=365,"365 Days Past Due",IIf(Date()-[invoice date sent]>=180,"180 Days Past Due",IIf(Date()-[invoice date sent]>=150,"150 Days Past Due",IIf(Date()-[invoice date sent]>=120,"120 Days Past Due",IIf(Date()-[invoice date sent]>=90,"90 Days Past Due",IIf(Date()-[invoice date sent]>=60 ,"61-90 Days Past Due",IIf(Date()-[invoice date sent]>=30 ,"31-60 Days Past Due",IIf(Date()-[invoice date sent]>=15 ,"15-30 Days Past Due","Current")))))))))

 

[Mr. B]

It appears that you may have a problem in the last statement of your nested IIF statement in the last line as posted above:

PaymentStatus: IIf([tbl report and invoices sending]![payment  terms]="Due Upon Receipt","Due Upon Receipt Past  Due",IIf(Date()-[invoice date sent]>=365,"365 Days Past  Due",IIf(Date()-[invoice date sent]>=180,"180 Days Past  Due",IIf(Date()-[invoice date sent]>=150,"150 Days Past  Due",IIf(Date()-[invoice date sent]>=120,"120 Days Past  Due",IIf(Date()-[invoice date sent]>=90,"90 Days Past  Due",IIf(Date()-[invoice date sent]>=60 And Date()-[invoice date  sent]<90,"61-90 Days Past Due",IIf(Date()-[invoice date sent]>=30  And Date()-[invoice date sent]<60,"31-60 Days Past  Due",IIf(Date()-[invoice date sent]>=15 And [invoice date  sent]>31,"15-30 Days Past Due","Current")))))))))

It is checking to see if the invoice date sent is > 31. I think this should be <= 31:

PaymentStatus: IIf([tbl report and invoices sending]![payment  terms]="Due Upon Receipt","Due Upon Receipt Past  Due",IIf(Date()-[invoice date sent]>=365,"365 Days Past  Due",IIf(Date()-[invoice date sent]>=180,"180 Days Past  Due",IIf(Date()-[invoice date sent]>=150,"150 Days Past  Due",IIf(Date()-[invoice date sent]>=120,"120 Days Past  Due",IIf(Date()-[invoice date sent]>=90,"90 Days Past  Due",IIf(Date()-[invoice date sent]>=60 And Date()-[invoice date  sent]<90,"61-90 Days Past Due",IIf(Date()-[invoice date sent]>=30  And Date()-[invoice date sent]<60,"31-60 Days Past  Due",IIf(Date()-[invoice date sent]>=15 And [invoice date  sent]<=31,"15-30 Days Past Due","Current")))))))))

 

[all7holly]

how about if I enter the payment terms? Does this make more sense?

PaymentStatustest:
IIf([tbl report and invoices sending]![payment terms]="Due Upon Receipt","Due Upon Receipt Past Due",
IIf(Date()-[invoice date sent]>=365,"365 Days Past Due",
IIf(Date()-[invoice date sent]>=180,"180 Days Past Due",
IIf(Date()-[invoice date sent]>=150,"150 Days Past Due",
IIf(Date()-[invoice date sent]>=120,"120 Days Past Due",
IIf(Date()-[invoice date sent]>=90,"90 Days Past Due",
iif([payment terms]=“net 30” and Date()>61<90,“61-90 Days Past Due”,
iif([payment terms]=“net 30” and Date()>31<60,“31-60 Days Past Due”,
iif([payment terms]=“net 15” and Date()>15<30,“15-30 Days Past Due”,“CURRENT”)))))))))

 

[Alansidman]

Does it work when you make the change? That is what is important and only you can tell us.

 

[Mr. B]

It was not a matter of not making sense, I really thought that is was a simple modification to the criteria for the 15-30 days part.

 

[all7holly]

Its fixed! Thank you very much for your help!