Sorting in Report, Text Vs. Numbers

[Romper]

I'm designing a report so I've put the thread here, but it could almost be a Table question also. Okay, within one of my tables, I record a catalogue number. The column though is set to Text because the catalogue number could be "878b" for example. I've built my queries for that table and everything works fine........BUT.
In designing the report, I want to sort the report firstly by "Country" and then by "CalatolgueNumber". So I group the report by "Country" no probs, but when I sort next by catalogue number it comes out in the following order:-

112
1156
126

I thought this might be because of the text setting in the Table and proved this by changing the table & query setting for this field to number, fixed, 0 decimals and the report then works brilliantly. My issue is, now that the setting is number, it won't accept a numeric-alpha catalogue number such as 878b, and in my situation this type of catalogue number is frequently used.

How can I either get the catalogue number to display numbers in a text setting in their proper order instead of calculating 1's first and so on like the example above, or change the table field setting to a number but allow alphabetical additions so that with letters added to the end of a catalogue number or not the correct order would appear:-

112
126
1156

Please help.

 

[plog]

112
126
1156

Where does 117C4 go in the above list?

 

[boblarson]

Text sorts differently than numbers. Add a field like this to your query.

MySort: Val([YourFieldNameHere])

And sort on it instead.

 

[mdlueck]

To support mixed numeric and alpha Revisions in an application, I developed an elaborate Base 36 <---> Base 10 storage system. Revisions are always stored as Base 10 in the table, thus the database may sort it correctly. The data is then converted to Base 36 for display.

Now, in one query, I was dynamically querying to fill a Combo Box. Said Combo Box entries were in a virtual table. It was IMPERATIVE to cast the Revision column as Integer in order to get it to sort properly. (Casting something that only exists virtually?! )

Example from that query:

[B]CInt([/B]Nz([t].[rev], -1)[B])[/B] AS [rev]
-- snip
ORDER BY [B][cb].[rev][/B],[cb].[ver],[cb].[vendortitle],[cb].[id];

 

[Romper]

112
126
1156

Where does 117C4 go in the above list?

The order should the read:-
112
117C4
126
1156

Your example of 117C4 in my situation would basically state that the catalogue number was 117 - variety C, so therefore it should rightly appear between 112 & 126.

Having said that, the catalogue numbers I have to use never have another digit added to the end if a letter has already been placed. The letter will always be the last reference.

 

[Romper]

To support mixed numeric and alpha Revisions in an application, I developed an elaborate Base 36 <---> Base 10 storage system. Revisions are always stored as Base 10 in the table, thus the database may sort it correctly. The data is then converted to Base 36 for display.

Now, in one query, I was dynamically querying to fill a Combo Box. Said Combo Box entries were in a virtual table. It was IMPERATIVE to cast the Revision column as Integer in order to get it to sort properly. (Casting something that only exists virtually?! )

Example from that query:

[B]CInt([/B]Nz([t].[rev], -1)[B])[/B] AS [rev]
-- snip
ORDER BY [B][cb].[rev][/B],[cb].[ver],[cb].[vendortitle],[cb].[id];

Thanks,
I'm sorry but you are talking way above my skill set. Whilst I really do appreciate your help, it's something I find hard to comprehend.
But thank you.

 

[Romper]

Text sorts differently than numbers. Add a field like this to your query.

MySort: Val([YourFieldNameHere)

And sort on it instead.

Thanks Bob,
Should there be a box close after Yourfieldnamehere? I'm guessing so. I'll give this a try and let you know the outcome.

 

[boblarson]

Thanks Bob,
Should there be a box close after Yourfieldnamehere? I'm guessing so. I'll give this a try and let you know the outcome.

Yeah, I did a 'typo'.

 

[Romper]

Hi Bob,
I've just tried what you suggested. It works.......BUT....... it doesn't display the letter on the end if there is one. I've noticed however, when I add the MySort field, it automatically defines it as a number field, could this be the issue? How do I fix it?

Thanks

 

[boblarson]

Hi Bob,
I've just tried what you suggested. It works.......BUT....... it doesn't display the letter on the end if there is one. I've noticed however, when I add the MySort field, it automatically defines it as a number field, could this be the issue? How do I fix it?

Thanks

The MySort NEEDS to be numeric. That is why it strips the letter. You don't need to display this made up field. You just use it to sort on. You would use your regular field for the actual use, just the made up one to sort with.

 

[Romper]

Okay, we're getting closer, but still not quite right. The test I've just tried will indeed sort the numbers correctly but not the letter component. My trial resulted in :-

12e
12b
1123c

Any other ideas?

 

[boblarson]

Okay, we're getting closer, but still not quite right. The test I've just tried will indeed sort the numbers correctly but not the letter component. My trial resulted in :-

12e
12b
1123c

Any other ideas?

Add the original field into the query again but this time AFTER the made up field (uncheck the Show checkbox on this second instance) and sort on it too.

 

[Romper]

Going along the lines of what you just suggested, perhaps I should let you know that when I added the MySort field to the query, I inserted it's column just before the original field, would this have made a difference?

 

[Romper]

Okay, i've uploaded a cut down version of the database in both '07 & '03 Access versions for you to look at if you get the time. You'll notice that the report is generated from QRY_CWUsedQtyTotXVal.

As previously mentioned, with your help i've managed to get the report to sort the "SGCatNo" field properly by number BUT not in it's entirety (inclusion of the end letter of the CatNo).

I need the report to sort it in the following order:-
12b
12e
1123c

and not as it is doing currently which is :-
12e
12b
1123c

Appreciate the help with this.

 

[boblarson]

 

[Romper]

Forgive me for being stupid, but that's why I'm seeking help. I've downloaded the revision that you've done and it doesn't work. The picture you posted shows that you have the field "MySort: VAL([SGCatNo]) but the actual query just has it as "VAL([SGCatNo]) and the report won't run because it needs parameter value MySort. The query won't let me rename it either, well it will, but then just reverts back. Did you test it?

Thanks.

 

[boblarson]

Did you test it?

Thanks.

Yes, I tested it.

 

[boblarson]

Okay, so for a report you do need the fields to be in the record source and you do need to set the sorts there as well. See the revised attached file.

 

[Romper]

Thanks Bob,

That's just what I needed. Works beautifully now.

I appreciate you perservering with me.

Cheers

 

[plog]

Sorry to return late to the party only to crap on it, but:

Your example of 117C4 in my situation would basically state that the catalogue number was 117 - variety C, so therefore it should rightly appear between 112 & 126.

Having said that, the catalogue numbers I have to use never have another digit added to the end if a letter has already been placed. The letter will always be the last reference.

Your table isn't properly structured. Every discrete peice of data needs to be in its own field. You are jamming 2 peices of data (Category Number and Variety) into SGCatNo and then doing some Frankenstein sorting to overcome it. You need to break this data out into 2 fields, one for the numeric Category Number value and one for the text Variety value. You do that and sorting this like you want it becomes trivial.