Report Ascending order

[Sri99]

HI

I have a 2 fields in a report from & to and while printing values was not printing in order & printing all values although I write query like : "Between[Forms]![Report Query]![Lab_From ] And [Forms]![Report Query]![Lab_To]".
I was tried sortings aswell.
still not coming right. can any one suggest me what's the wrong? your help will be great helpfull.

Thanks
Sri

 

[JHB]

Look below.

 

[Sri99]

hi thanks for your reply. actually my values are ex: 10,20,30,40,100,1000,1200,1500 in the combo box.
while printing the values are coming as : 10,20,1000,200,1500,20,30,100.
so I need to print them as ascending order. again it can be great help to me.

Thanks & Regards
Sri

 

[Pat Hartman]

JHB showed you how to do this with A2003. The process is the same with newer versions of Access but the screen will look different.

Access ignores any sort order specified in the report's RecordSource query because it completely rewrites the query. In this regard, forms and reports are different since with forms, Access respects the sort order and fields selected by your query.

 

[Sri99]

HI

I was tried the way JHB explain me, but still the values showing same way.
Is any thing needs to be change in the Query?

 

[JHB]

Hmm - are you sure the number are numbers and not a text showed as numbers?
Try with this in the query:

CLng([ThefieldYouGetTheNumberFrom])

 

[Sri99]

Hmm - are you sure the number are numbers and not a text showed as numbers?
Try with this in the query:

Hi JHB, I was tried by input CLNG in the Crieteria field in the query. Is it the right way?but, still not came right>
can u plz explain me where should I mentioned it?

Thanks & Regards
Sri

 

[JHB]

Ok - then show the query (SQL-string) the report is based on.
CLng should be in the select not in the crieteria.
Like: SELECT CLng([ThefieldYouGetTheNumberFrom]) .... From YouTableName ....

 

[Sri99]

Ok - then show the query (SQL-string) the report is based on.
CLng should be in the select not in the crieteria.
Like: SELECT CLng([ThefieldYouGetTheNumberFrom]) .... From YouTableName ....

Hi JHB

I did like above query It came right, but not fully. Actually I have values in a combo box like: 1,2,3,4,10,20,1000,1001,1002. before the values came as 1,2,3,4,1000,1001,1002,10,20 so on. after wright as above, when I select from 1 to 20 came in order, but when i select 1 to 1002 again the order has been misplacing as 1,2,3,4,1000,1001,1002,10,20.
i wrote 2 different type of commands as: (1) SELECT CLng(Field Name) AS Expr1
FROM Table Name; and the other one is:
SELECT CLng(TableName.FieldName) AS Expr1
FROM [QueryName]
ORDER BY Val([TableName].[FieldName]);
Both are giving same result.

Thanks & Regards
Sri

 

[JHB]

Without the database/report no idea.

 

[Pat Hartman]

I'll say it AGAIN. Reports REQUIRE the sort option to be set at the report level NOT in the RecordSource query.

 

[JHB]

I was tried the way JHB explain me, but still the values showing same way.
.....

I presume that the report still contains the sort order!

 

[Pat Hartman]

Reports do not respect the sort order specified in the RecordSource query since Access completely rebuilds the query to its own specification and eliminates any sort you specified. It uses what you specify in the Report's Sorting/Grouping dialog as the sort order.

 

[JHB]

I do agree, I was pointing to the sorting/grouping order in the report, that I presume the report still contains! .

 

[Pat Hartman]

Maybe I'm missing something but both of you are posting SQL and modifying the SQL of the Report's RecordSource isn't going to change the sort order. JHB's earlier post showing the sorting/grouping is where the change needs to be made but since it is for an old version of Access perhaps the OP can't make the jump.

 

[JHB]

Yes then I think you are missing something.

Sri99 answered to the Sorting/grouping in the report I suggested.

I was tried the way JHB explain me, but still the values showing same way.
.....

Then it sound for me that it could be text showed as numbers.
My idea was to convert it into numbers with CLng which Sri99 did, but still tells Sri99 the result was not as expected.

My answer where:

Without the database/report no idea.

Then you wrote:

I'll say it AGAIN. Reports REQUIRE the sort option to be set at the report level NOT in the RecordSource query.

My answer to that where:

I presume that the report still contains the sort order!

Maybe I should have written it more clear: I presume that the report still contains the sorting/grouping order Sri99 once added to the report!
I know the RecordsSource sort order do not have any effect in the report.