'Order By' phrase for Report in Access 2003

[raabbasi]

One of my forms open a report as below:

DoCmd.OpenReport "rptTest", acViewPreview, "Order By '" & strIDNum & "'", strCriteria, acWindowNormal

The strIDNum is read from a textbox, in the form, with no problem. The strCriteria is a string working fine.

But the report does not Order the result as expected. I want to know the correct syntax for the 'Order By' phrase.

I tried concatinating the 'Order By' phrase with the Criteria as well, without success.

Seeking help.

 

[jebry]

Hi!

There is no Order By clause in the open report method. You can set the order by in the report open event referencing the information on the form.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[raabbasi]

Thanks Jebry for the reply.
Would you kindly elaborate a bit more on this way of doing it!

I want to do it dynamically, based on the user choice - using OptionButtons, for example.

Further help will be appreciated.

 

[raabbasi]

I tried the following way;

Me.OrderBy Forms!frmCriteria_Sales!txtOrderBy

But I received the Error message "Invalid use of property".

Please let me know, how can I rectify it.

 

[jebry]

Hi!

Try it like this:

Me.OrderByOn = True
Me.OrderBy = Forms!frmCriteria_Sales!txtOrderBy

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[dhookom]

I would never rely on the OrderBy property of a report since the sorting and grouping levels would supercede this property. The solution that I recommend is found at

http://allenbrowne.com/ser-33.html

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]

 

[raabbasi]

The solution by Jebry works very fine and it is so simple to apply.

 

[dhookom]

The solution by Jebry will work on reports that don't have anything set in their sorting and grouping dialog. I can't recall creating a report in the last 10 years that doesn't have something in the sorting and grouping levels.

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]

 

[jebry]

Hi Duane!

Absolutely correct. If you having grouping already you need to use the GroupLevel property and change its ControlSource to whatever you want to group/sort by.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst