'Order By' phrase for Report in Access 2003

[Roshan Abbasi via AccessMonster.com]

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.

 

[Dennis]

'Order By' is not valid syntax in the DoCmd.OpenReport command. To Order the
information on your report, go into the design view of the report and click

 

[Roshan Abbasi via AccessMonster.com]

Thanks Dennis for the reply.
I am trying to do it dynamically, based on the user choice - using
OptionButtons, for example.
Does it mean that we can not Order a report result based on the user
selection, as described above?

Further help will be appreciated.

 

[Dennis]

What you will need to do is put code in the report's On Open Event to change
the record source property because in there you can use the Order By
statement.
e.g.
If Forms![YourFormName]![ControlName]="Something" then
Me.RecordSource = "SELECT * FROM [QUERY] ORDER BY [Field]"
else
Me.RecordSource = .....
end if

 

[Allen Browne]

Dennis is correct in that you must use the Sorting And Grouping of the
report to get a reliable sort.

But you can programmatically set the ControlSource of the GroupLevel in the
Open event of the report. Details in:
Sorting Records in a Report at run-time
at:
http://allenbrowne.com/ser-33.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Roshan Abbasi via AccessMonster.com]

Thank you very much Dennis for the great help.
Regards.

 

[Roshan Abbasi via AccessMonster.com]

Thanks Allen Browne for further help.