Sorting text field with some numbers

[lissajo64]

I have some data that I am putting into a report and most of it has a letter as the start but I have a few records that are for example 4-C Construction that I would like to be able to keep in that format but still show up when sorting in my report without having to spell out Four. Access seems to be ignoring those entries Is there any way to accomplish this? I am using grouping in alphabetical order with headers of the first letter.
Thanks in advance!

[ItsMe]

The only way I know to go about this is to add an alias in a query. With this extra column, you can display the original value with the alpha stripped OR the original value with a specific alpha character, like A, added. Finally, another alias would combine and sort. You may need to use subqueries, I can't remember.

[lissajo64]

I didn't know you could do that! I will check into how that is done...

[ItsMe]

Within the Query Designer, an alias is created by adding a new column/field. Create a unique name MyAlias and add a colon after it MyAlias:


With this you have the start of an alias. After the name, you can add an expression. I would start by trying to strip the alpha characters or add an alpha to the number. Then, worry about the rest.


To add an alpha, you would create an expression that concatenates a letter in front of the number. To determine if there are alpha characters you could employ the IsNumeric() function (should give the results needed). So maybe an IIf using the IsNumeric() and in the True area of the IIf you can include the original field name with the concatenated alpaha character.


To build an expression to strip an alpha, I would use the InStr() function. It may be more suitable for your needs to add an alpha to a number, in order to control where the numbers appear in the sort.