Query Help

[all7holly]

Hello,

I am trying to return records that are past due if not paid right away when the invoice terms are “Due Upon Receipt” below is the code I’m using. I keep getting an error saying you entered invalid syntax. Any ideas?

PaymentStatus: IIf(Date()-[invoice date sent]>=1 And [tbl report and invoices sending]=[due upon receipt], “Due Upon Receipt Past Due”,

Thanks,
Holly

 

[NickHa]

“Due Upon Receipt Past Due”

appears to have the wrong quotation mark characters. Try

"Due Upon Receipt Past Due"

instead.
Is your syntax fully represented? There is a trailing comma which should have the else part of the IIf statement. It should be something like

PaymentStatus: IIf(Date()-[invoice date sent]>=1 And [tbl report and invoices sending]=[due upon receipt],"Due Upon Receipt Past Due","alternative choice")

 

[all7holly]

This is the full code.


PaymentStatus: IIf(Date()-[invoice date sent]>=1 And [tbl report and invoices sending]=[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 ","Current"))))))))

 

[plog]

When you get this nested with IIF statements its time to create a function in a module.

 

[NickHa]

Did changing the quotation marks help, or did the problem move elsewhere?