Expression too complex in query expression 1

[jondel81]

I have a field in which has the number of months back the date was. I need to group the months in certain groups, ie 0-3, 4-6, 7-12, etc. I get an error when trying to use this switch statement below (says the expression is two complex). Any ideas on how to make the switch statement less complex?

SELECT Switch([monthsback] Between 0 And 3,"0-3",[monthsback] Between 4 And 6,"4-6",[monthsback] Between 7 And 9,"7-9",[monthsback] Between 10 And 12,"10-12",[monthsback] Between 13 And 15,"13-15",[monthsback] Between 16 And 18,"16-18",[monthsback] Between 19 And 21,"19-21",[monthsback] Between 22 And 24,"22-24",[monthsback] Between 25 And 27,"25-27",[monthsback] Between 28 And 30,"28-30",[monthsback] Between 31 And 33,"31-33",[monthsback] Between 34 And 36,"34-36",[monthsback] Between 37 And 42,"37-42",[monthsback] Between 43 And 48,"43-48",[monthsback]>48,">48") AS Recency, Query1.Freq, Query1.Monetary
FROM Query1;

------

http://www.carealotpets.com

 

[ByteMyzer]

Try:
[tt]
SELECT Switch(CDate([monthsback]) Between 0 And 3,"0-3",CDate([monthsback]) Between 4 And 6,"4-6",CDate([monthsback]) Between 7 And 9,"7-9",CDate([monthsback]) Between 10 And 12,"10-12",CDate([monthsback]) Between 13 And 15,"13-15",CDate([monthsback]) Between 16 And 18,"16-18",CDate([monthsback]) Between 19 And 21,"19-21",CDate([monthsback]) Between 22 And 24,"22-24",CDate([monthsback]) Between 25 And 27,"25-27",CDate([monthsback]) Between 28 And 30,"28-30",CDate([monthsback]) Between 31 And 33,"31-33",CDate([monthsback]) Between 34 And 36,"34-36",CDate([monthsback]) Between 37 And 42,"37-42",CDate([monthsback]) Between 43 And 48,"43-48",CDate([monthsback])>48,">48") AS Recency, Query1.Freq, Query1.Monetary
FROM Query1;
[/tt]

 

[jondel81]

same error, Expression too complex

------

http://www.carealotpets.com

 

[lespaul]

have you tried adding each switch clause individually to see if you can get it to work with just 2 groups then add each subsequent group?

Leslie

 

[mpastore]

I would just use a reference table called tbMonthsBack like:

monthsback desc
0 0-3
1 0-3
3 0-3
4 4-6
5 4-6

etc...

Then you can just join to this table to get your description...


Mike Pastore

Hats off to (Roy) Harper

 

[jondel81]

Thanks Mike, that worked.

------

http://www.carealotpets.com