Using Switch Function to Group Column Headings in a Crosstab Query

[Guest]

I am trying to group column headings on a crosstab query. Most of the column headings are grouping except for the ones I am trying to put into the "2Wks" column heading. It is not grouping all the values. It is putting some of them into the ">2 Wks column heading. Here is the expression
Expr1: Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like "[3]","48 hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-6-7-8]","1 wk",[Turnaround] Like "[9-10-11-12-13-14-15]","2 wks",True,">2 wks")

Any help will be greatly appreciated.

 

[Gary Walter]

Hi Karen,

I would think you would use

Expr1: Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like "[3]","48
hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-8]","1 wk",[Turnaround]
Like "[9-15]","2 wks",True,">2 wks")

I didn't look it up though. I hate using Access Help.

Good luck,

Gary Walter

I am trying to group column headings on a crosstab query. Most of the column

headings are grouping except for the ones I am trying to put into the "2Wks" column
heading. It is not grouping all the values. It is putting some of them into the ">2
Wks column heading. Here is the expression

Expr1: Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like "[3]","48

hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-6-7-8]","1
wk",[Turnaround] Like "[9-10-11-12-13-14-15]","2 wks",True,">2 wks")

 

[Gary Walter]

Sorry Karen,

We're talking about *string comparison* here.
What was I thinking?!
Help gives one "crafty" example of "[0-100]"
which is misleading when you think in terms
of how text is sorted.

try:

Expr1: Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like "[3]","48
hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-8]","1 wk",[Turnaround]
In ('9','10','11','12','13','14','15'),"2 wks",True,">2 wks")

I tired this, but it doesn't seem to like any number higher than 9. When I group

9-13, I don't get the correct grouping. It still dumps it into the ">2wks" heading.
I tried each one separate 10, 11 so forth and no heading will appear. To test , I
put quotations around the 10 and got the correct count and heading. The problem is,
I need to group the double digit numbers with the single digit number.

I am stumped.

Hi Karen,

I would think you would use

Expr1: Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like "[3]","48
hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-8]","1 wk",[Turnaround]
Like "[9-15]","2 wks",True,">2 wks")

I didn't look it up though. I hate using Access Help.

Good luck,

Gary Walter

I am trying to group column headings on a crosstab query. Most of the column

headings are grouping except for the ones I am trying to put into the "2Wks" column
heading. It is not grouping all the values. It is putting some of them into the ">2
Wks column heading. Here is the expression

Expr1: Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like "[3]","48

hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-6-7-8]","1
wk",[Turnaround] Like "[9-10-11-12-13-14-15]","2 wks",True,">2 wks")

Any help will be greatly appreciated.

 

[Gary Walter]

or this works:

[Turnaround] Like "[1][0-5]","2 wks"

Sorry Karen,

We're talking about *string comparison* here.
What was I thinking?!
Help gives one "crafty" example of "[0-100]"
which is misleading when you think in terms
of how text is sorted.

try:

Expr1: Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like "[3]","48
hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-8]","1 wk",[Turnaround]
In ('9','10','11','12','13','14','15'),"2 wks",True,">2 wks")

I tired this, but it doesn't seem to like any number higher than 9. When I group

9-13, I don't get the correct grouping. It still dumps it into the ">2wks" heading.
I tried each one separate 10, 11 so forth and no heading will appear. To test , I
put quotations around the 10 and got the correct count and heading. The problem is,
I need to group the double digit numbers with the single digit number.

I am stumped.

Hi Karen,

I would think you would use

Expr1: Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like "[3]","48
hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-8]","1 wk",[Turnaround]
Like "[9-15]","2 wks",True,">2 wks")

I didn't look it up though. I hate using Access Help.

Good luck,

Gary Walter

I am trying to group column headings on a crosstab query. Most of the column
headings are grouping except for the ones I am trying to put into the "2Wks" column
heading. It is not grouping all the values. It is putting some of them into

the

">2

Wks column heading. Here is the expression
Expr1: Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like "[3]","48
hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-6-7-8]","1
wk",[Turnaround] Like "[9-10-11-12-13-14-15]","2 wks",True,">2 wks")

Any help will be greatly appreciated.

 

[Gary Walter]

or this works:

[Turnaround] Like "[9]","2 wks",[Turnaround] Like "[1][0-5]","2 wks"

Sorry Karen,

We're talking about *string comparison* here.
What was I thinking?!
Help gives one "crafty" example of "[0-100]"
which is misleading when you think in terms
of how text is sorted.

try:

Expr1: Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like "[3]","48
hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-8]","1 wk",[Turnaround]
In ('9','10','11','12','13','14','15'),"2 wks",True,">2 wks")

I tired this, but it doesn't seem to like any number higher than 9. When I

group
9-13, I don't get the correct grouping. It still dumps it into the ">2wks" heading.
I tried each one separate 10, 11 so forth and no heading will appear. To test , I
put quotations around the 10 and got the correct count and heading. The problem is,
I need to group the double digit numbers with the single digit number.

I am stumped.

:

Hi Karen,

I would think you would use

Expr1: Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like "[3]","48
hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-8]","1 wk",[Turnaround]
Like "[9-15]","2 wks",True,">2 wks")

I didn't look it up though. I hate using Access Help.

Good luck,

Gary Walter

I am trying to group column headings on a crosstab query. Most of the column
headings are grouping except for the ones I am trying to put into the "2Wks" column
heading. It is not grouping all the values. It is putting some of them into

the

">2
Wks column heading. Here is the expression
Expr1: Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like "[3]","48
hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-6-7-8]","1
wk",[Turnaround] Like "[9-10-11-12-13-14-15]","2 wks",True,">2 wks")

Any help will be greatly appreciated.