Using Choose function in query

[Rick]

Hi,
I am getting unexpected results when trying to sort a
query. I am using the Choose command to gather input from
an option group on a form:
SortBy: Choose(Forms!StuListsSelectionF!GrpSortBy,
[Homeroom],[NumberGrade],[StuName])

Depending on the option that is selected, one of the three
fields is used to sort the query in ascending order. The
Homeroom and StuName fields work fine, but NumberGrade
does not. For high school students for example, it
displays grades 10, 11, 12, then 09. The data type for
this field is number, so it should put 09 first. If I
just use the NumberGrade field in the query (with no
Choose function) and sort on it, it works fine. The
Choose function seems to screw things up. Thanks for any
help,
-Rick

 

[Duane Hookom]

Convert NumberGrade to text
SortBy: Choose(Forms!StuListsSelectionF!GrpSortBy, [Homeroom],
Format([NumberGrade],"00"), [StuName])

 

[John Vinson]

The data type for
this field is number, so it should put 09 first. If I
just use the NumberGrade field in the query (with no
Choose function) and sort on it, it works fine. The
Choose function seems to screw things up. Thanks for any
help,

The problem is that since some of the fields are Text, Access must use
Text as the datatype of the Choose function. You can convert a number
to a text string (8 -> "8") but not vice versa; so Text is the lowest
common denominator.

As suggested... use the Format() function to explicitly convert the
number to a text string. Setting the Format property of the number
field in the table won't be enough.