Hi,
I am trying to insert a calculated field into the SQL code of one of my queries (it's a simple query; just retrieving rows of invoices from an Excel sheet). This field is basically a bunch of nested IIF arguments, which calculates the age of each invoice.
The non-nested SQL code looks like this:
Code:
IIf(Month(`Invoice Date`)>Month(Monthend),'Future',)
(IIf(Month(`Invoice Date`)=Month(Monthend),'A.Current',))
(IIf((Month(`Invoice Date`)+1)=Month(Monthend),'B.31-60',))
(IIf((Month(`Invoice Date`)+2)=Month(Monthend),'C.61-90',))
(IIf((Month(`Invoice Date`)+3)=Month(Monthend),'D.91-120',))
(IIf((Month(`Invoice Date`)+4)=Month(Monthend),'E.121-150',))
(IIf((Month(`Invoice Date`)+5)=Month(Monthend),'F.151-180',))
(IIf((Month(`Invoice Date`)+6)=Month(Monthend),'G.181-210',))
(IIf((Month(`Invoice Date`)+7)=Month(Monthend),'H.211-240',))
(IIf((Month(`Invoice Date`)+8)=Month(Monthend),'I.241-270',))
(IIf((Month(`Invoice Date`)+9)=Month(Monthend),'J.271-300',))
(IIf((Month(`Invoice Date`)+10)=Month(Monthend),'K.301-330',))
(IIf((Month(`Invoice Date`)+11)=Month(Monthend),'L.331-1Y',))
(IIf((Month(`Invoice Date`)+12)<=Month(Monthend),'M.>1Y'))
Now, when nesting these together, and adding it to the SQL code in MS Query, it works fine up until the '211-240' row. When I nest in the next row, I get the following error:
http://www.stormskritt.com/msq1.jpg
(EDIT: As I'm a new user, I'm not allowed to link in images or URL's. The error I'm getting reads: "Incorrect column expression:").
This leads me to believe that there is some sort of length or # of character limit when it comes to these SQL statements.
I'm completely new to MS Query, but have quite a bit of experience in BRIO/Hyperion (which for various reasons I can't use anymore).
Any help would be greatly appreciated.
Thanks in advance,
Andreas
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
