Forums: » Register « |  Free Tools |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

New Free Tools on Dev Shed!

#1
October 19th, 2012, 06:06 AM
 Berrern
Registered User

Join Date: Oct 2012
Posts: 1
Time spent in forums: 15 m 29 sec
Reputation Power: 0
MS Query error - due to length?

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.

Andreas

 Viewing: Dev Shed Forums > Databases > MS SQL Development > MS Query error - due to length?