Not Sorting Numbers Correctly in a Report

[norseman]

I have a database that stores data about different kits that I build. The data structure is

Field Name	Data Type	Explanation of what is stored in the field
ID	AutoNumber	Key
Kit	Short Text	Kit Name
PartNo	Short Text	Manufacturer Part Number
Man_Id	Number	Manufacturer ID Number
Book_lid	Number	Book ID where Instructions and key information are kept
Pg	Number	Page number where Instructions are
Qty	Number	Quantity of the kit
Dr	Number	What Drawer Kit is stored
Sl	Number	What Slot in Drawer where kit it stored
Finish	Short Text	What type of finish has been applied to kit
UpperDrill	Short Text	Drill bit size for main
LowerDrill	Short Text	Drill bit size if there are two drills needed
Bushings	Short Text	ID of the bushings I will use to work on the kit
Tubes	Short Text	Size of tubes (if needed) for kit

I am building a report where I want to sort all the kits by the Drawer and Slot where they are stored
The report is built with a sort (ascending) on drawer and then a sort (ascending) on Slot.

When I view the report, the drawer sort comes out as

Drawer	Slot
1	1
1	2
1	3
1	4
1	5
10	1
10	2
10	3
10	4
10	5
11	1
11	2
11	3
11	4
11	5
2	1
2	2
2	3
2	4
2	5

I want it to sort the drawer number correctly: 1,2,3,4,5,6,7,8,9,10,11, etc.. then sort the slot the same way
I know this is not a major type problem, but it is an annoyance when I need the report

I have tried both storing the drawer number as text or as a number



I have looked around in this forum as well as the internet in general, but I have not found a way to fix this problem.

What am I not doing that I should be doing

[Micron]

You say

Dr

Number

What Drawer Kit is stored

yet for some reason, Drawer is text. That's why 10 comes before 2.

[June7]

Are you positive those fields are defined as number type in table?

Is report RecordSource a query? Post the SQL statement.

Could provide db for analysis. Follow instructions at bottom of mypost.

[norseman]

You say

Dr

Number

What Drawer Kit is stored

yet for some reason, Drawer is text. That's why 10 comes before 2.

I do not understand why you say the Drawer is text? Maybe this will clear things up. In the table the first column is the name of the field the second column is what the field is stored as, and the third is an explanation of what the field is. I will edit my post to make this more clear.

In the current db Drawer is stored as a number. I have tried restructuring the db so that Drawer is stored as text but it still did not print the way I want.

The Dr stands for the Drawer where the kit is stored. They are numbered 1 thru 13. Each Drawer has up to 11 slots where the actual kit is stored. Given kit xyz can be found in drawer 3, slot 9

[Gicu]

You should not be doing the sorting (by the numeric Drawer and Slot) in the report's recordsource, but in the report's Design view by going to Grouping and Sorting.
Cheers,

[norseman]

I figured it out, my mistake. It was your question about the RecordSource that got me checking it with a fine tooth comb. The RecordSource query was attached to the wrong db.

When I started fooling around with this problem I made a copy of the db and the report (thus the query with it). I thought I had changed the db in the query to the copy but it was still attached to the original db where Dr was defined as short text. When I changed the everything to the correct db, with the correct query RecordSource for the report to make Dr and Sl numbers, and used the correct query (where I told it to sort Dr (ascending), and attached to the correct copy of the report, everything came out as I wanted it to.

Thank you for your reply, it got me started looking in the correct place

[Welshgasman]

Recordsouce sort sequence is ignored by report, as mentioned by Vlad.
You need to do the sort in the report.

[Micron]

I do not understand why you say the Drawer is text?

because that is how numbers that are text data type are sorted. It was a dead giveaway. If it turned out that the source data type was in fact integer or some other number type then it would have to be that your query makes use of a conversion function that converts number types to text. I can think of no other possibility for why 10 would sort ascending before 2.

[Welshgasman]

because that is how numbers that are text data type are sorted. It was a dead giveaway. If it turned out that the source data type was in fact integer or some other number type then it would have to be that your query makes use of a conversion function that converts number types to text. I can think of no other possibility for why 10 would sort ascending before 2.

If you make you feel any better, lots of so called 'profressional' software does exactly the same thing.

I use Xero and that does that.
I used to use the SSAFA CMS system and that did that as well. :-(

[June7]

A field defined as text type permits digits, letters, punctuation, symbols (all are just characters in a text field) therefore alpha sort rules must apply. This means ordering is determined one character at a time.