Sorting in a nonnumeric field

[Guest]

I have created a d-base and query that sorts according to grade level (K-12),
but I can't seem to get it to hold the sort in the reports I've made. Also,
I had to make the column in the original table a text one so that I could
enter K for Kindergarten, so now it sorts in numeric form (1, 10, 11, 12, 2,
3, etc). How do I get a report to sort according to this grade level column?

 

[Guest]

Try splitting the field into two column for the sort

Select TableName.*, left([GradeLevelFieldName],1) as Level1,
Mid([GradeLevelFieldName],3) as Level2 from TableName

In the sorting and grouping of the report, select Level1 as FirstSort and
Level2 as Second sort
I assume in my example that the field is always start with a letter with a
dash, and then the number

 

[Van T. Dinh]

Create a Calculated Column in your Query:

TranslatedGrade: Iif(IsNumeric([Grade]), CInt([Grade]), 0)

In the Report, set this Field as a Sort Field in the Report's "Sorting and
Grouping".

 

[John Vinson]

On Tue, 18 Oct 2005 14:16:11 -0700, "Sara VCS" <Sara

I have created a d-base and query that sorts according to grade level (K-12),
but I can't seem to get it to hold the sort in the reports I've made. Also,
I had to make the column in the original table a text one so that I could
enter K for Kindergarten, so now it sorts in numeric form (1, 10, 11, 12, 2,
3, etc). How do I get a report to sort according to this grade level column?

Add a calculated field in the query

SortGrade: Val([Grade Level])

This will be 0 for nonnumeric entries ("K") and the numeric value of
the grade for numbers. Sort the report using this calculated field.

John W. Vinson[MVP]