• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

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
0
katzwhite
Asked:
katzwhite
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
NetminderCommented:
User resolved; points refunded and question closed.

Netminder
CS Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now