Sorting

[Newbie]

Hi
I have a query that forms the basis for a report.
The query has a character field (this can't be changed) :-( For 99.9% of
the time this field contains a number In the query (SQL) I can make this
sort on the field as though it were a number field by making use of the
PATINDEX eg

Case patindex('%[^0-9]%',TX.PassportNo) when 0 then cast (TX.PassportNo as
int) end, TX.PassportNo

However, when I run the report it doesn't take any notice of how the query
is sorted so how can I achieve the same effect in a report as with a query
result set?
i.e the report is giving me
1
11
111
2
22
222

but I want (and this is what the query gives me)
1
2
11
22
111
222

Thanks