Losing query order in report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.)
 
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
Click to expand...

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
 
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.
 
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
 
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?
 
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 said:
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
Click to expand...

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
Click to expand...
 
Thanks a lot, it works well!!!

fredg said:
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
Click to expand...
 
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.
 
Back
Top