Report ? (based upon query with CLng sort function)

I am trying to create a report based upon the following query:

SELECT Structures.ADD_NUM, Structures.PREDIR, Structures.ST_NAME, Structures.ST_TYPE, Structures.NOTES, Structures.OADDRESS, Structures.OSPREFIX, Structures.OSNAME, Structures.OSTYPE, Structures.HOUSEHEAD, Structures.COMMUNITY
FROM Structures
WHERE (((Structures.JURISDICTION)="MY CITY"))
ORDER BY Structures.COMMUNITY, Structures.ST_NAME, CLng(Structures.ADD_NUM);


The query works just fine and i am getting the desired results and the sorted order is the desired order.

However, i built a report based upon the query -- and the report is resorting the data different from the query.

The undesireable results seems to deal with the field Structures.ADD_NUM. This field is a text field but it primarily contains numbers. I am using the CLng function in the query to convert the text field to numbers so the sort will work the way i want. However, the report is reordering the data back to the text order (an undesireable order).

Can anyone offer any suggestions on what i am doing wrong and how to correct this problem?

thanks for any and all help

Have you tried using the Sorting and Grouping options?

are you saying there are sorting options in the "report"?

I was only using the "order by" in the sql query...

how do i do a sort in a report?

thanks for the help!!!

Yes, you have to use the Sorting Grouping options for the report, ordering by in the underlying query is ignored by Reports.
The sorting grouping etc is on the toolbar when in design mode

Rich,

I checked the sorting and grouping options and the report had already defaulted to sorting the same way as the query. I'm guessing this was already done because i used the wizard to initially create the report.

I tried using the clng function in the report sorting and grouping options and this did not work.

I ended up editing the query to make a "newfield" that was numeric and then sorting in the report by this newfield. (this was originally suggested by Mile-O-Phile, but at the time i didn't quite understand how to use this information with the "report").

This seems to work now...

thanks for all the help!!!