Why Negative Numbers don't sort correctly

I'm using the query below and the numbers are sorting as if all were positive numbers.  The sign is not being considered so negative numbers sort as if there was no sign.

SELECT [AA CostperBeq_Bll].AreaName, [AA CostperBeq_Bll].CostBeq
FROM [AA CostperBeq_Bll]
ORDER BY Left(AreaName,1), [AA CostperBeq_Bll].CostBeq DESC;


What do I need to do?

Thanks

Elton
katzwhiteAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
NetminderConnect With a Mentor Commented:
User resolved; points refunded and question closed.

Netminder
CS Moderator
0
 
BangerterCommented:
in your order by statment try:

ORDER BY val(left(areaname, 1))
0
 
BangerterCommented:
if it doesnt like that you could create another field in your query: MyOrder: val(left(areaname,1))

and then set your orderby to: Order By MyOrder

just a thought. are you sure that your left command is picking up the negative sign? where your parameter is only 1 it will only pick up the first character so if you have '-1' the left will only pick up the '-' and not the '1' as well.

is areaname a text field or a numeric field?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
katzwhiteAuthor Commented:
Sorry, I didn't explain better.   [AA CostperBeq_Bll].CostBeq is the numberic field that isn't sorting negatives property. AreaName is a text field being ordered by the first letter only and it is working properly.

Output from the sort can be seen at http://www.KatzWhite.com/SortError.gif

Thanks for the help.

Elton
0
 
gwgawCommented:
Your query sorts by AreaName first and then by CostBeq. Changing

ORDER BY Left(AreaName,1), [AA CostperBeq_Bll].CostBeq DESC;

to

ORDER BY [AA CostperBeq_Bll].CostBeq DESC, Left(AreaName,1);

will sort the numbers correctly. However, AreaName will no longer be in alphbetical order.
0
 
VolibrawlCommented:
Well, those might be queries that would work, unfortunately neither provides the order that katzwhile wants.

I see nothing wrong with your query and it should give you the correct results.  Check:

1. The type of field for COSTBEQ, either number or text should work ...but check it
2. Your negative number may not be entered correctly, re-enter it.
3. Try changing to ascending to see if it works with your data.

good luck
0
 
katzwhiteAuthor Commented:
I checked the originating table and its set up as double.  CostBeq is the output of a query that totals the purchases table by area.  No formating is applied to the CostBeq.

converted from A97 where I think its working correctly and am now running under Acess 2000.

I'll try sorting on Left(AreaName,1),  Left([AA CostperBeq_Bll].CostBeq,1),  [AA CostperBeq_Bll].CostBeq DESC;  and see if that will do the trick.

Looks like a bug in the sort to me.

Thanks

Elton
0
 
scorp8Commented:
Have you tried changing the data type in the query?

What's the current datatype in the table it's pulling from?

If you use CInt, CDbl, etc. it changes your datatype for that particular field in the query.

Example below:

SELECT [AA CostperBeq_Bll].AreaName, [AA CostperBeq_Bll].CostBeq
FROM [AA CostperBeq_Bll]
ORDER BY Left(AreaName,1), CInt([AA CostperBeq_Bll]).CostBeq DESC;


Place CInt([]) in front of [AA CostperBeq_Bll]

scorp8

0
 
katzwhiteAuthor Commented:
My own
suggestion,  Left(AreaName,1),  Left([AA CostperBeq_Bll].CostBeq,1),  [AA CostperBeq_Bll].CostBeq DESC;  did the trick.

Without " Left([AA CostperBeq_Bll].CostBeq,1)". it sorts as if all the numbers are positive.

Best

Elton

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.