Losing query order in report

[Mariebob]

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

 

[Allen Browne]

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

 

[fredg]

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

 

[Mariebob]

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.

 

[Allen Browne]

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

 

[Mariebob]

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

 

[Mariebob]

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?

 

[fredg]

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?

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

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

 

[Mariebob]

Thanks a lot, it works well!!!

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

 

[Allen Browne]

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.