Acc 2003 ignores sort in SQL statement !

[DougP]

I created a report that needs to have numbers sorted
FD001 to FD1000. this is a mailing label report and that all that's on the label FD001 etc
my table has the Number as shown and then a ID autonumber field with the numbers 1 to 1000 sorted by ID Ascending.
But the FD1000 prints just after the FD100 not after FD999
Is there some other place to put the sort other than in the Reports record source. I have done this before in other versions of Access with no problems
this is very frustrating

DougP, MCP, A+

 

[Remou]

I think you must split the field before you sort it. Try sorting on:

Val(Mid([IDFieldName],3))

 

[DougP]

Sorry there are two fields
ID is autonumber
Number is text


DougP, MCP, A+

 

[Remou]

I do not quite understand. You wish to sort by a text field called Number so that the numeric part of the field sorts in numeric order, yes no? If that is the case, you need to use the numeric portion of the field to sort, that is, Mid(Number,3), furthermore, it needs to be converted to a number, hence Val(Mid(Number,3)). Please explain a little more, if I have misunderstood your point.

 

[Remou]

PS I hope that Number is not the real name of the field.

 

[DougP]

yes it is, so I changed it to TheNumber and now all the numbers are completely out of sequence like a random number generator ordered them when I do a print preview.
I know text normally sorts by each charater so 1,11, 100 and 1000 would be next to each other.
for some reason the report ignores the SQL statement???

SELECT Numbers.id, Numbers.TheNumber FROM Numbers ORDER BY Numbers.id;

I want this
FD001 FD002 FD003 FD004
FD005 FD006 FD007 FD008

but I get this now on the first page
FD348 FD375 FD342 FD341
FD340 FD339 FD338 FD337



DougP, MCP, A+

 

[traingamer]

Use 'sorting and grouping' on the report.

 

[DougP]

Ok I fixed it
Ok I created a whole new database and started over
I noticed when I used the Label wizard to create the report it asked how to sort it and I chose the ID number.
and now it works perfect.
I guess Access 2003 has this new feature of sorting by and if you don't use it when you are first creating the report you cannot change it later?
makes no sense

Thanks all

DougP, MCP, A+

 

[dhookom]

DougP,
Access reports can be set to sort in any way that you might want. The key is to ignore the sorting in your query. It makes absolutely no difference in your report.

Use the Sorting and Grouping dialog in your report. If you are using a text field to store numbers, you may need to set the field/expression to something like:
=Val([YourTextNumber])


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]