Losing query order in report

Hi,

I used an already ordered sql query for a report.
Everything is fine until I add running sums. I lose the order.

Can anybody help me?

Thanks

If you want the report sorted, you must specify the sort order in its
Sorting And Grouping box (View menu, in report design view.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mariebob" wrote in message
...

I used an already ordered sql query for a report.
Everything is fine until I add running sums. I lose the order.

Can anybody help me?

Thanks

On Thu, 19 Jul 2007 08:20:01 -0700, Mariebob wrote:

Hi,

I used an already ordered sql query for a report.
Everything is fine until I add running sums. I lose the order.

Can anybody help me?

Thanks

Any sort order done in a query is irrelevant to the sort order of a
report.

Use the report's Sorting and Grouping dialog to sort the report.

In report Design View:
View + Sorting and Grouping
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Well, the problem is I dynamicly build my query in the click event of a
button of a form in which the user can select the fields he wants, enter
conditions and the sort order of his report. I build an SQL statement from
his choices, and the sort (order) works in the report, as long as I don't add
running sums.

Since my code is pretty intricate, I'd prefer leaving it as is, but I'll try
to work with the orderbyon and orderby properties.

Thanks for the answers, if someone as another idea, it will be welcomed also.

"fredg" wrote:

On Thu, 19 Jul 2007 08:20:01 -0700, Mariebob wrote:

Hi,

I used an already ordered sql query for a report.
Everything is fine until I add running sums. I lose the order.

Can anybody help me?

Thanks

Any sort order done in a query is irrelevant to the sort order of a
report.

Use the report's Sorting and Grouping dialog to sort the report.

In report Design View:
View + Sorting and Grouping
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

If you need to do so, you can set the ControlSource of the GroupLevel.

Example in:
Sorting report records at runtime
at:
http://allenbrowne.com/ser-33.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mariebob" wrote in message
...
Well, the problem is I dynamicly build my query in the click event of a
button of a form in which the user can select the fields he wants, enter
conditions and the sort order of his report. I build an SQL statement from
his choices, and the sort (order) works in the report, as long as I don't
add
running sums.

Since my code is pretty intricate, I'd prefer leaving it as is, but I'll
try
to work with the orderbyon and orderby properties.

Thanks for the answers, if someone as another idea, it will be welcomed
also.

"fredg" wrote:

On Thu, 19 Jul 2007 08:20:01 -0700, Mariebob wrote:

Hi,

I used an already ordered sql query for a report.
Everything is fine until I add running sums. I lose the order.

Can anybody help me?

Thanks

Any sort order done in a query is irrelevant to the sort order of a
report.

Use the report's Sorting and Grouping dialog to sort the report.

In report Design View:
View + Sorting and Grouping
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Well, your example will be useful for me for another report, but since the
number of sorts can vary from 0 to 3 in this case, I won't be able to use it.

Thanks

"Allen Browne" wrote:

If you need to do so, you can set the ControlSource of the GroupLevel.

Example in:
Sorting report records at runtime
at:
http://allenbrowne.com/ser-33.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mariebob" wrote in message
...
Well, the problem is I dynamicly build my query in the click event of a
button of a form in which the user can select the fields he wants, enter
conditions and the sort order of his report. I build an SQL statement from
his choices, and the sort (order) works in the report, as long as I don't
add
running sums.

Since my code is pretty intricate, I'd prefer leaving it as is, but I'll
try
to work with the orderbyon and orderby properties.

Thanks for the answers, if someone as another idea, it will be welcomed
also.

"fredg" wrote:

On Thu, 19 Jul 2007 08:20:01 -0700, Mariebob wrote:

Hi,

I used an already ordered sql query for a report.
Everything is fine until I add running sums. I lose the order.

Can anybody help me?

Thanks

Any sort order done in a query is irrelevant to the sort order of a
report.

Use the report's Sorting and Grouping dialog to sort the report.

In report Design View:
View + Sorting and Grouping
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

By the way, I set the recordsource of the report in the openreport event,
using openargs.

I am now trying to send two strings (the sql statement and the orderby
clause) but it seems I cannot send an array as opening argument.

Anybody know anything about it?

"Allen Browne" wrote:

If you need to do so, you can set the ControlSource of the GroupLevel.

Example in:
Sorting report records at runtime
at:
http://allenbrowne.com/ser-33.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mariebob" wrote in message
...
Well, the problem is I dynamicly build my query in the click event of a
button of a form in which the user can select the fields he wants, enter
conditions and the sort order of his report. I build an SQL statement from
his choices, and the sort (order) works in the report, as long as I don't
add
running sums.

Since my code is pretty intricate, I'd prefer leaving it as is, but I'll
try
to work with the orderbyon and orderby properties.

Thanks for the answers, if someone as another idea, it will be welcomed
also.

"fredg" wrote:

On Thu, 19 Jul 2007 08:20:01 -0700, Mariebob wrote:

Hi,

I used an already ordered sql query for a report.
Everything is fine until I add running sums. I lose the order.

Can anybody help me?

Thanks

Any sort order done in a query is irrelevant to the sort order of a
report.

Use the report's Sorting and Grouping dialog to sort the report.

In report Design View:
View + Sorting and Grouping
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Thu, 19 Jul 2007 10:44:01 -0700, Mariebob wrote:

By the way, I set the recordsource of the report in the openreport event,
using openargs.

I am now trying to send two strings (the sql statement and the orderby
clause) but it seems I cannot send an array as opening argument.

Anybody know anything about it?

"Allen Browne" wrote:

If you need to do so, you can set the ControlSource of the GroupLevel.

Example in:
Sorting report records at runtime
at:
http://allenbrowne.com/ser-33.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mariebob" wrote in message
...
Well, the problem is I dynamicly build my query in the click event of a
button of a form in which the user can select the fields he wants, enter
conditions and the sort order of his report. I build an SQL statement from
his choices, and the sort (order) works in the report, as long as I don't
add
running sums.

Since my code is pretty intricate, I'd prefer leaving it as is, but I'll
try
to work with the orderbyon and orderby properties.

Thanks for the answers, if someone as another idea, it will be welcomed
also.

"fredg" wrote:

On Thu, 19 Jul 2007 08:20:01 -0700, Mariebob wrote:

Hi,

I used an already ordered sql query for a report.
Everything is fine until I add running sums. I lose the order.

Can anybody help me?

Thanks

Any sort order done in a query is irrelevant to the sort order of a
report.

Use the report's Sorting and Grouping dialog to sort the report.

In report Design View:
View + Sorting and Grouping
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail



You can send many strings in the one OpenArgs argument.
Here is one method that uses the "|" character to separate the various
parts within the OpenArgs argument.

Assuming you are using a newer version of Access that includes the
Split() function.

First Copy and Paste the following Function into a Module:

Public Function ParseText(TextIn As String, x) As Variant
On Error Resume Next
Dim Var As Variant
Var = Split(TextIn, "|", -1)
ParseText = Var(x)

End Function
========

To pass the multiple OpenArgs to the report (4 of them in this
example):

DoCmd.OpenReport"ReportName", , , , , "Hello|GoodBy|Mary|Lamb"
===========
Code the Open event of that Report:

If Not IsNull(Me.OpenArgs) Then
Dim strA As String
Dim strB As String
Dim strC As String
Dim strD As String

strA = ParseText(OpenArgs, 0)
strB = ParseText(OpenArgs, 1)
strC = ParseText(OpenArgs, 2)
strD = ParseText(OpenArgs, 3)

' Then do what you want with the resulting strings

End If
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Thanks a lot, it works well!!!

"fredg" wrote:

On Thu, 19 Jul 2007 10:44:01 -0700, Mariebob wrote:

By the way, I set the recordsource of the report in the openreport event,
using openargs.

I am now trying to send two strings (the sql statement and the orderby
clause) but it seems I cannot send an array as opening argument.

Anybody know anything about it?

"Allen Browne" wrote:

If you need to do so, you can set the ControlSource of the GroupLevel.

Example in:
Sorting report records at runtime
at:
http://allenbrowne.com/ser-33.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mariebob" wrote in message
...
Well, the problem is I dynamicly build my query in the click event of a
button of a form in which the user can select the fields he wants, enter
conditions and the sort order of his report. I build an SQL statement from
his choices, and the sort (order) works in the report, as long as I don't
add
running sums.

Since my code is pretty intricate, I'd prefer leaving it as is, but I'll
try
to work with the orderbyon and orderby properties.

Thanks for the answers, if someone as another idea, it will be welcomed
also.

"fredg" wrote:

On Thu, 19 Jul 2007 08:20:01 -0700, Mariebob wrote:

Hi,

I used an already ordered sql query for a report.
Everything is fine until I add running sums. I lose the order.

Can anybody help me?

Thanks

Any sort order done in a query is irrelevant to the sort order of a
report.

Use the report's Sorting and Grouping dialog to sort the report.

In report Design View:
View + Sorting and Grouping
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail



You can send many strings in the one OpenArgs argument.
Here is one method that uses the "|" character to separate the various
parts within the OpenArgs argument.

Assuming you are using a newer version of Access that includes the
Split() function.

First Copy and Paste the following Function into a Module:

Public Function ParseText(TextIn As String, x) As Variant
On Error Resume Next
Dim Var As Variant
Var = Split(TextIn, "|", -1)
ParseText = Var(x)

End Function
========

To pass the multiple OpenArgs to the report (4 of them in this
example):

DoCmd.OpenReport"ReportName", , , , , "Hello|GoodBy|Mary|Lamb"
===========
Code the Open event of that Report:

If Not IsNull(Me.OpenArgs) Then
Dim strA As String
Dim strB As String
Dim strC As String
Dim strD As String

strA = ParseText(OpenArgs, 0)
strB = ParseText(OpenArgs, 1)
strC = ParseText(OpenArgs, 2)
strD = ParseText(OpenArgs, 3)

' Then do what you want with the resulting strings

End If
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Fair enough.

If you need up to 3 sorts, one way to cheat is to put the same field in the
Sorting'n'Grouping box 3 times. You can then.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mariebob" wrote in message
...
Well, your example will be useful for me for another report, but since the
number of sorts can vary from 0 to 3 in this case, I won't be able to use
it.

Thanks

"Allen Browne" wrote:

If you need to do so, you can set the ControlSource of the GroupLevel.

Example in:
Sorting report records at runtime
at:
http://allenbrowne.com/ser-33.html