Report/Query/Sorting using CLng function -- problem

  • Thread starter Thread starter DukeArgos
  • Start date Start date
D

DukeArgos

i'm a newb using Access 2002 and i can't figure this out.

I'm trying to create a report based upon a query. The query works jus
fine and sorts the data just the way i want it, but when i make
report for it -- the sort order isn't the same.

My query (which works fine) is this:
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.ST_NAME, Structures.ST_TYPE, Structures.PREDIR
Clng(Structures.ADD_NUM);

It is possible the sort order is off in the "report" because of th
"Structures.ADD_NUM" field which is a "text" field. In the "query"
am able to convert the text to numbers with the Clng function and thi
does make the query work just fine. However, when i make a repor
based upon the query, the resulting report converts back to a sor
relative to text.

i.e. i get the following incorrectly ordered results in the report:
1 MAIN ST
2 MAIN ST
22 MAIN ST
3 MAIN ST
44 MAIN ST
5 MAIN ST
6 MAIN ST

But as i said before, i am getting the correct sort order from runnin
the query by itself...

Does anyone know what i am doing wrong???

thanks for any help!!! :
 
DukeArgos said:
i'm a newb using Access 2002 and i can't figure this out.

I'm trying to create a report based upon a query. The query works just
fine and sorts the data just the way i want it, but when i make a
report for it -- the sort order isn't the same.

My query (which works fine) is this:
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.ST_NAME, Structures.ST_TYPE, Structures.PREDIR,
Clng(Structures.ADD_NUM);

It is possible the sort order is off in the "report" because of the
"Structures.ADD_NUM" field which is a "text" field. In the "query" i
am able to convert the text to numbers with the Clng function and this
does make the query work just fine. However, when i make a report
based upon the query, the resulting report converts back to a sort
relative to text.

i.e. i get the following incorrectly ordered results in the report:
1 MAIN ST
2 MAIN ST
22 MAIN ST
3 MAIN ST
44 MAIN ST
5 MAIN ST
6 MAIN ST

But as i said before, i am getting the correct sort order from running
the query by itself...
Click to expand...

Sorting the query has little, if any effect on a report.
The reliable way to sort a report is to use Sorting and
Grouping (View menu).
 
Sorting the query has little, if any
effect on a report. The reliable way
to sort a report is to use Sorting and
Grouping (View menu).
Click to expand...

Absolutely true! However, even using Sorting and Grouping isn't going to
change the sort order of a text field, which is exactly correct as the
original poster described. If that field is _always, only_ numeric, then
converting it to a number and sorting on the street name, then on the street
number may give him the sort order desired.

If it isn't always a number, e.g., 122A, or One, or Five Fifty Five, then
sorting it in the order desired would be a bit of an effort, if at all
possible.

Larry Linson
Microsoft Access MVP
 
Marshall,

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

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

I ended up editing the query to make a "newfield" that was numeric an
then sorting in the report by this newfield. This seems to work...

Larry,
99% of all the data contained in the text field is an integer value -


thanks for the help!!! :
 
Back
Top