Sorting With Negatives

[Kenny Robb]

I have written a query which pulls various bits of data from a number
of tables and then does some calculation based on information pulled
back. The results can either be positive or negative and I need to
sort this field decending....starting with the higest positive and
ending with the ;lowest negative. When I hit the sort field button it
seems to be sorting by the numbers so I ger -44 next to 44.

Any suggestions?

Thanks
Kenny

 

[Guest]

It's probably a text field, and this is why it sort the table in this order,
you need to change the sort to number

Select FieldName From TableName Order By Val(Nz(FieldName,0))


HTH, good luck

 

[David Lloyd]

Kenny:

You are seeing -44 next to 44 most likely because the field is being read as
a text field. Assuming all the values in this field are numeric, you can
convert the results of this field to a number, and then the sort should show
as desired. For example:

CLng([MySortField])

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have written a query which pulls various bits of data from a number
of tables and then does some calculation based on information pulled
back. The results can either be positive or negative and I need to
sort this field decending....starting with the higest positive and
ending with the ;lowest negative. When I hit the sort field button it
seems to be sorting by the numbers so I ger -44 next to 44.

Any suggestions?

Thanks
Kenny

 

[Kenny Robb]

Kenny:

You are seeing -44 next to 44 most likely because the field is being read as
a text field. Assuming all the values in this field are numeric, you can
convert the results of this field to a number, and then the sort should show
as desired. For example:

CLng([MySortField])

Excellent..this seems to have worked......but it has dropped the 2
decimal places...any way of getting them back...the MySortField is
actually a calculation

Calc_MV: Format(([Calc_V]+[applied_mv]+([T_won]*1)),"00.00")

I could do a bit of a workround by writing the results into a table
but at this point I don't want to have to do that.

Thanks in anticipation.....

Kenny

 

[John Spencer]

Use CCur vice CLng

Kenny:

You are seeing -44 next to 44 most likely because the field is being read
as
a text field. Assuming all the values in this field are numeric, you can
convert the results of this field to a number, and then the sort should
show
as desired. For example:

CLng([MySortField])

Excellent..this seems to have worked......but it has dropped the 2
decimal places...any way of getting them back...the MySortField is
actually a calculation

Calc_MV: Format(([Calc_V]+[applied_mv]+([T_won]*1)),"00.00")

I could do a bit of a workround by writing the results into a table
but at this point I don't want to have to do that.

Thanks in anticipation.....

Kenny

 

[Kenny Robb]

Use CCur vice CLng

Kenny:

You are seeing -44 next to 44 most likely because the field is being read
as
a text field. Assuming all the values in this field are numeric, you can
convert the results of this field to a number, and then the sort should
show
as desired. For example:

CLng([MySortField])

Excellent..this seems to have worked......but it has dropped the 2
decimal places...any way of getting them back...the MySortField is
actually a calculation

Calc_MV: Format(([Calc_V]+[applied_mv]+([T_won]*1)),"00.00")

I could do a bit of a workround by writing the results into a table
but at this point I don't want to have to do that.

Thanks in anticipation.....

Kenny

OK Chaps..one step closer.....it is now doing what I want but it has
converted it to Currency.....is there a list of what these conversions
do then I can have a try myself and not need to keep coming back and
bothering you.

Thanks
Kenny

 

[John Spencer]

Lookup Type conversion functions in the help. Here are three

CLng - Converts to a whole number (no fractional part)
CDbl - Converts to a floating point number (has a decimal or fractional
component)
CCur - Converts to a number with four decimal places

By the way, CCur is great for accuracy when you need no more than 4 decimal
places. The display format can be changed to eliminate the monetary
symbols.

Use CCur vice CLng

On Fri, 11 Nov 2005 08:38:16 -0500, "David Lloyd"

Kenny:

You are seeing -44 next to 44 most likely because the field is being
read
as
a text field. Assuming all the values in this field are numeric, you
can
convert the results of this field to a number, and then the sort should
show
as desired. For example:

CLng([MySortField])

Excellent..this seems to have worked......but it has dropped the 2
decimal places...any way of getting them back...the MySortField is
actually a calculation

Calc_MV: Format(([Calc_V]+[applied_mv]+([T_won]*1)),"00.00")

I could do a bit of a workround by writing the results into a table
but at this point I don't want to have to do that.

Thanks in anticipation.....

Kenny

OK Chaps..one step closer.....it is now doing what I want but it has
converted it to Currency.....is there a list of what these conversions
do then I can have a try myself and not need to keep coming back and
bothering you.

Thanks
Kenny

 

[Kenny Robb]

CDbl did the trick nicely....Thanks for all your help

Kenny

Lookup Type conversion functions in the help. Here are three

CLng - Converts to a whole number (no fractional part)
CDbl - Converts to a floating point number (has a decimal or fractional
component)
CCur - Converts to a number with four decimal places

By the way, CCur is great for accuracy when you need no more than 4 decimal
places. The display format can be changed to eliminate the monetary
symbols.

Use CCur vice CLng


On Fri, 11 Nov 2005 08:38:16 -0500, "David Lloyd"

Kenny:

You are seeing -44 next to 44 most likely because the field is being
read
as
a text field. Assuming all the values in this field are numeric, you
can
convert the results of this field to a number, and then the sort should
show
as desired. For example:

CLng([MySortField])

Excellent..this seems to have worked......but it has dropped the 2
decimal places...any way of getting them back...the MySortField is
actually a calculation

Calc_MV: Format(([Calc_V]+[applied_mv]+([T_won]*1)),"00.00")

I could do a bit of a workround by writing the results into a table
but at this point I don't want to have to do that.

Thanks in anticipation.....

Kenny

OK Chaps..one step closer.....it is now doing what I want but it has
converted it to Currency.....is there a list of what these conversions
do then I can have a try myself and not need to keep coming back and
bothering you.

Thanks
Kenny