Column Sort on Calculated Fields

[David Long]

If I try to sort a calculated field column by clicking the column header, the sort only partially works. If it is in decending order I get something like the following:
100
100
71.33
78.57
85
93
...
the "100" values are grouped at the top, but then the sort gets messed up.
Thanks

[Frank]

David,
aspDB insert the ORDER BY clause into the SQL statement to perform sorting. There was a glitch and since fixed that when the fieldname in the formula has embedded space then it would cause a problem. That doesn`t sound like your problem or do you have embedded spaces in fieldnames in the formula. There must be something else in it. Can you state your version number and then try duplicate the problem using either NorthWind or Pubs.

Frank

On 10/6/98 10:04:44 AM, David Long wrote:
> If I try to sort a calculated field column by clicking the column header,
> the sort only partially works. If it is in decending order I get something
> like the following:
100
100
71.33
78.57
85
93
...
the "100" values
> are grouped at the top, but then the sort gets messed up.
Thanks

[John]

David,

Is that field a TEXT field? If so, that would explain your problem! Text fields sort by the characters received. The "1" in "100" comes before the "9" in "90" so it sorted FIRST. Convert it to a Numeric field, or if that`s not possible, pad with LEADING SPACES or ZEROES: eg: "100"," 90", " 1" or "100", "090" "001", etc.



John


On 10/6/98 2:43:32 PM, Frank wrote:
> David,
aspDB insert the ORDER BY clause into the SQL statement to perform
> sorting. There was a glitch and since fixed that when the fieldname in the
> formula has embedded space then it would cause a problem. That doesn`t
> sound like your problem or do you have embedded spaces in fieldnames in the
> formula. There must be something else in it. Can you state your version
> number and then try duplicate the problem using either NorthWind or
> Pubs.

Frank

On 10/6/98 10:04:44 AM, David Long wrote:
> If I try to
> sort a calculated field column by clicking the column header,
> the sort
> only partially works. If it is in decending order I get something
> like
> the following:
100
100
71.33
78.57
85
93
...
the "100" values
>
> are grouped at the top, but then the sort gets messed up.
Thanks

[David Long]

John,
Thanks...after looking deeper I found you were correct.
I was orriginally having problems getting my calculated numerical values to return with just 2 decimal places (rather than 10+). I ended up converting the numerical field within Access to a character string using the following formula:
rating: CStr(Sgn((100-([3rdfiscal98web]![Rej]+[3rdfiscal98web]![Comp]/[3rdfiscal98web]![Lots]*100)))*Int(Abs((100-([3rdfiscal98web]![Rej]+[3rdfiscal98web]![Comp]/[3rdfiscal98web]![Lots]*100)))*(10^2)+0.5)/(10^2))

Bingo!

I`m going to see again if I can solve the long numerics problem.
For an example of one of my pages that was set up on the asp-db server for a previous problem see:
http://www.aspdb.com/test/lucent/Eng1.asp?aspDBBut_1=aspDBgridRowMore::100#ASPDB_1
ASP-db Version: 1.0922
Thanks again
On 10/6/98 11:30:36 PM, John wrote:
> David,

Is that field a TEXT field? If so, that would explain your
> problem! Text fields sort by the characters received. The "1" in "100"
> comes before the "9" in "90" so it sorted FIRST. Convert it to a Numeric
> field, or if that`s not possible, pad with LEADING SPACES or ZEROES: eg:
> "100"," 90", " 1" or "100", "090" "001", etc.



John


On 10/6/98
> 2:43:32 PM, Frank wrote:
> David,
aspDB insert the ORDER BY clause into
> the SQL statement to perform
> sorting. There was a glitch and since
> fixed that when the fieldname in the
> formula has embedded space then it
> would cause a problem. That doesn`t
> sound like your problem or do you
> have embedded spaces in fieldnames in the
> formula. There must be
> something else in it. Can you state your version
> number and then try
> duplicate the problem using either NorthWind or
> Pubs.

Frank

On
> 10/6/98 10:04:44 AM, David Long wrote:
> If I try to
> sort a
> calculated field column by clicking the column header,
> the sort
>
> only partially works. If it is in decending order I get something
> like
>
> the following:
100
100
71.33
78.57
85
93
...
the "100" values
>
>
> are grouped at the top, but then the sort gets messed up.
Thanks

[David Long]

I found the rather simple but eluding (for me) solution to displaying my numerical field to decimal places of my choice. Here is the portion of dbMagicCell line that is inserted to control the decimal places:
;column_name,align=center,<Font Size=1 Face=ARIAL Color=Black Decimalplaces=2>format=[fixed]</Font>;

On 10/6/98 11:30:36 PM, John wrote:
> David,

Is that field a TEXT field? If so, that would explain your
> problem! Text fields sort by the characters received. The "1" in "100"
> comes before the "9" in "90" so it sorted FIRST. Convert it to a Numeric
> field, or if that`s not possible, pad with LEADING SPACES or ZEROES: eg:
> "100"," 90", " 1" or "100", "090" "001", etc.



John


On 10/6/98
> 2:43:32 PM, Frank wrote:
> David,
aspDB insert the ORDER BY clause into
> the SQL statement to perform
> sorting. There was a glitch and since
> fixed that when the fieldname in the
> formula has embedded space then it
> would cause a problem. That doesn`t
> sound like your problem or do you
> have embedded spaces in fieldnames in the
> formula. There must be
> something else in it. Can you state your version
> number and then try
> duplicate the problem using either NorthWind or
> Pubs.

Frank

On
> 10/6/98 10:04:44 AM, David Long wrote:
> If I try to
> sort a
> calculated field column by clicking the column header,
> the sort
>
> only partially works. If it is in decending order I get something
> like
>
> the following:
100
100
71.33
78.57
85
93
...
the "100" values
>
>
> are grouped at the top, but then the sort gets messed up.
Thanks