Crosstab ordering string

[Andy]

I have a crosstab query where the Column heading is 'Wait: IIf([Clock Stops
in Period Flatfile]![Wait(Weeks)]<0,"0",IIf([Clock Stops in Period
Flatfile]![Wait(Weeks)]>52,"52+",CStr([Clock Stops in Period
Flatfile]![Wait(Weeks)])))'

I used CStr so that I could have the '52+' criteria, but now that the
returned value is a string, the ordering goes to pot as 6,7,8 and 9 is at the
end of the query.
How can I get the columns ordered, but with '52+' at the end?

 

[John Spencer]

You can specify the column headings which will specify the order. You will
have to type in every possible value. When you specify the column headings
only the column headings you specify will be returned in the results AND
every column heading you specify will be returned. If you make a typing
error then the column will appear with no data. So if the query would
return "01" and you type "1", You will get a column labeled 1 with NO data
and you won't get a column "01" displayed.

PIVOT xxx IN ("0","1","2","3",...,"10","11",...,"51","52","52+"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Andy]

painful, but as needs must!

Thanks

You can specify the column headings which will specify the order. You will
have to type in every possible value. When you specify the column headings
only the column headings you specify will be returned in the results AND
every column heading you specify will be returned. If you make a typing
error then the column will appear with no data. So if the query would
return "01" and you type "1", You will get a column labeled 1 with NO data
and you won't get a column "01" displayed.

PIVOT xxx IN ("0","1","2","3",...,"10","11",...,"51","52","52+"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a crosstab query where the Column heading is 'Wait: IIf([Clock Stops
in Period Flatfile]![Wait(Weeks)]<0,"0",IIf([Clock Stops in Period
Flatfile]![Wait(Weeks)]>52,"52+",CStr([Clock Stops in Period
Flatfile]![Wait(Weeks)])))'

I used CStr so that I could have the '52+' criteria, but now that the
returned value is a string, the ordering goes to pot as 6,7,8 and 9 is at
the
end of the query.
How can I get the columns ordered, but with '52+' at the end?

 

[John Spencer]

I did think of another way. That is to use FORMAT to force all the numbers
to have leading zeroes.

IIf([Clock Stops in Period Flatfile]![Wait(Weeks)]<0,"00",
IIf([Clock Stops in Period Flatfile]![Wait(Weeks)]>52,"52+",
FORMAT([Clock Stops in Period Flatfile]![Wait(Weeks)],"00")))

That should work to return 00 01 02 03 04 05 ..50 51 52 52+

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

painful, but as needs must!

Thanks

You can specify the column headings which will specify the order. You
will
have to type in every possible value. When you specify the column
headings
only the column headings you specify will be returned in the results AND
every column heading you specify will be returned. If you make a typing
error then the column will appear with no data. So if the query would
return "01" and you type "1", You will get a column labeled 1 with NO
data
and you won't get a column "01" displayed.

PIVOT xxx IN ("0","1","2","3",...,"10","11",...,"51","52","52+"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a crosstab query where the Column heading is 'Wait: IIf([Clock
Stops
in Period Flatfile]![Wait(Weeks)]<0,"0",IIf([Clock Stops in Period
Flatfile]![Wait(Weeks)]>52,"52+",CStr([Clock Stops in Period
Flatfile]![Wait(Weeks)])))'

I used CStr so that I could have the '52+' criteria, but now that the
returned value is a string, the ordering goes to pot as 6,7,8 and 9 is
at
the
end of the query.
How can I get the columns ordered, but with '52+' at the end?