Sort Order

H

hdfixitup

I currently use two reports so I can sort on two different fields. I would
like to have one report with two fields (Number1 and Number2). From a Option
Group on a form, I would select Sort Order - either Number 1 or Number 2.
When the report opens, it would sort based on my selection in ascending
order. What do I need to place in the Sorting and Group Options on the report
and what code needs to be added to the OnOpen event of the report? Can
anyone help me? I have tried numerous possibilities with no such luck.
 
H

hdfixitup

Here's where I am at so far:

Function Print_Report()
On Error GoTo Print_Report_Err
Dim Counter As Integer
Dim ReportDest As String
'Hide the form PrintReportsDialog
Forms![PrintReportsDialog].Visible = False
'Destination is Print Preview
If Forms![PrintReportsDialog]![Type of Output] = 1 Then
ReportDest = acNormal
Else 'Destination is Printer
ReportDest = acPreview
End If
With CodeContextObject
' Attached to the Print button on the PrintReportsDialog form

Select Case .[Report to Print]
Case 1 'Orders
'Print Order Report
Do While Counter < .[Number of Copies]
If Forms![PrintReportsDialog]![Select Order Type] = 1 And
Forms![PrintReportsDialog].[Select Number Both] = 1 Then
DoCmd.OpenReport "rptOrder", ReportDest, ,
Eval("IIf(Forms![PrintReportsDialog]![Select List] Is Null,"""",""[OrderID] =
Forms![PrintReportsDialog]![Select List]"")")
End If
If Forms![PrintReportsDialog]![Select Order Type] = 1 And
Forms![PrintReportsDialog].[Select Number Both] = 2 Then
DoCmd.OpenReport "rptOrderJ", ReportDest, ,
Eval("IIf(Forms![PrintReportsDialog]![Select List] Is Null,"""",""[OrderID] =
Forms![PrintReportsDialog]![Select List]"")")
End If

I currently have 20 Cases that follow this one. In this example, I want to
eliminate the second If statement rptOrderJ and use rptOrder only for Case 1.
I will add an option group on the PrintReportsDialog form allowing a sort
choice(either Number 1 or Number 2). I read your Case example that you
pointed me to. How do I incorporate another Case 1 or Case 2 within my Case
1 already? I am listing 4 group levels on the Sort/Grouping Dialog Box of the
report. Group - Ascending Subgroup - Ascending Number1 - Ascending
Number2 Ascending. I feel I am so close. Any help appreciated. Thanks
 
A

Allen Browne

No, the idea is to place the Select Case in Report_Open

Your Print_Report() just does the OpenReport.

The report's Open event test if the dialog form is open, and uses a Select
Case to set the ControlSource of the GroupLevel.

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

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

hdfixitup said:
Here's where I am at so far:

Function Print_Report()
On Error GoTo Print_Report_Err
Dim Counter As Integer
Dim ReportDest As String
'Hide the form PrintReportsDialog
Forms![PrintReportsDialog].Visible = False
'Destination is Print Preview
If Forms![PrintReportsDialog]![Type of Output] = 1 Then
ReportDest = acNormal
Else 'Destination is Printer
ReportDest = acPreview
End If
With CodeContextObject
' Attached to the Print button on the PrintReportsDialog form

Select Case .[Report to Print]
Case 1 'Orders
'Print Order Report
Do While Counter < .[Number of Copies]
If Forms![PrintReportsDialog]![Select Order Type] = 1 And
Forms![PrintReportsDialog].[Select Number Both] = 1 Then
DoCmd.OpenReport "rptOrder", ReportDest, ,
Eval("IIf(Forms![PrintReportsDialog]![Select List] Is
Null,"""",""[OrderID] =
Forms![PrintReportsDialog]![Select List]"")")
End If
If Forms![PrintReportsDialog]![Select Order Type] = 1 And
Forms![PrintReportsDialog].[Select Number Both] = 2 Then
DoCmd.OpenReport "rptOrderJ", ReportDest, ,
Eval("IIf(Forms![PrintReportsDialog]![Select List] Is
Null,"""",""[OrderID] =
Forms![PrintReportsDialog]![Select List]"")")
End If

I currently have 20 Cases that follow this one. In this example, I want
to
eliminate the second If statement rptOrderJ and use rptOrder only for Case
1.
I will add an option group on the PrintReportsDialog form allowing a sort
choice(either Number 1 or Number 2). I read your Case example that you
pointed me to. How do I incorporate another Case 1 or Case 2 within my
Case
1 already? I am listing 4 group levels on the Sort/Grouping Dialog Box of
the
report. Group - Ascending Subgroup - Ascending Number1 - Ascending
Number2 Ascending. I feel I am so close. Any help appreciated. Thanks

Allen Browne said:
It's just a matter of assigning the ControlSource of the GroupLevel

Example here:
Sorting report records at runtime
at:
http://allenbrowne.com/ser-33.html
Click to expand...
Click to expand...
 
H

hdfixitup

Allen Browne said:
No, the idea is to place the Select Case in Report_Open

Your Print_Report() just does the OpenReport.

The report's Open event test if the dialog form is open, and uses a Select
Case to set the ControlSource of the GroupLevel.

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

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

hdfixitup said:
Here's where I am at so far:

Function Print_Report()
On Error GoTo Print_Report_Err
Dim Counter As Integer
Dim ReportDest As String
'Hide the form PrintReportsDialog
Forms![PrintReportsDialog].Visible = False
'Destination is Print Preview
If Forms![PrintReportsDialog]![Type of Output] = 1 Then
ReportDest = acNormal
Else 'Destination is Printer
ReportDest = acPreview
End If
With CodeContextObject
' Attached to the Print button on the PrintReportsDialog form

Select Case .[Report to Print]
Case 1 'Orders
'Print Order Report
Do While Counter < .[Number of Copies]
If Forms![PrintReportsDialog]![Select Order Type] = 1 And
Forms![PrintReportsDialog].[Select Number Both] = 1 Then
DoCmd.OpenReport "rptOrder", ReportDest, ,
Eval("IIf(Forms![PrintReportsDialog]![Select List] Is
Null,"""",""[OrderID] =
Forms![PrintReportsDialog]![Select List]"")")
End If
If Forms![PrintReportsDialog]![Select Order Type] = 1 And
Forms![PrintReportsDialog].[Select Number Both] = 2 Then
DoCmd.OpenReport "rptOrderJ", ReportDest, ,
Eval("IIf(Forms![PrintReportsDialog]![Select List] Is
Null,"""",""[OrderID] =
Forms![PrintReportsDialog]![Select List]"")")
End If

I currently have 20 Cases that follow this one. In this example, I want
to
eliminate the second If statement rptOrderJ and use rptOrder only for Case
1.
I will add an option group on the PrintReportsDialog form allowing a sort
choice(either Number 1 or Number 2). I read your Case example that you
pointed me to. How do I incorporate another Case 1 or Case 2 within my
Case
1 already? I am listing 4 group levels on the Sort/Grouping Dialog Box of
the
report. Group - Ascending Subgroup - Ascending Number1 - Ascending
Number2 Ascending. I feel I am so close. Any help appreciated. Thanks

Allen Browne said:
It's just a matter of assigning the ControlSource of the GroupLevel

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

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

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

I currently use two reports so I can sort on two different fields. I
would
like to have one report with two fields (Number1 and Number2). From a
Option
Group on a form, I would select Sort Order - either Number 1 or Number
2.
When the report opens, it would sort based on my selection in ascending
order. What do I need to place in the Sorting and Group Options on the
report
and what code needs to be added to the OnOpen event of the report? Can
anyone help me? I have tried numerous possibilities with no such luck.
Click to expand...
Click to expand...
Click to expand...
 
H

hdfixitup

Works perfectly NOW. Thanks again for your assistance.

Allen Browne said:
No, the idea is to place the Select Case in Report_Open

Your Print_Report() just does the OpenReport.

The report's Open event test if the dialog form is open, and uses a Select
Case to set the ControlSource of the GroupLevel.

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

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

hdfixitup said:
Here's where I am at so far:

Function Print_Report()
On Error GoTo Print_Report_Err
Dim Counter As Integer
Dim ReportDest As String
'Hide the form PrintReportsDialog
Forms![PrintReportsDialog].Visible = False
'Destination is Print Preview
If Forms![PrintReportsDialog]![Type of Output] = 1 Then
ReportDest = acNormal
Else 'Destination is Printer
ReportDest = acPreview
End If
With CodeContextObject
' Attached to the Print button on the PrintReportsDialog form

Select Case .[Report to Print]
Case 1 'Orders
'Print Order Report
Do While Counter < .[Number of Copies]
If Forms![PrintReportsDialog]![Select Order Type] = 1 And
Forms![PrintReportsDialog].[Select Number Both] = 1 Then
DoCmd.OpenReport "rptOrder", ReportDest, ,
Eval("IIf(Forms![PrintReportsDialog]![Select List] Is
Null,"""",""[OrderID] =
Forms![PrintReportsDialog]![Select List]"")")
End If
If Forms![PrintReportsDialog]![Select Order Type] = 1 And
Forms![PrintReportsDialog].[Select Number Both] = 2 Then
DoCmd.OpenReport "rptOrderJ", ReportDest, ,
Eval("IIf(Forms![PrintReportsDialog]![Select List] Is
Null,"""",""[OrderID] =
Forms![PrintReportsDialog]![Select List]"")")
End If

I currently have 20 Cases that follow this one. In this example, I want
to
eliminate the second If statement rptOrderJ and use rptOrder only for Case
1.
I will add an option group on the PrintReportsDialog form allowing a sort
choice(either Number 1 or Number 2). I read your Case example that you
pointed me to. How do I incorporate another Case 1 or Case 2 within my
Case
1 already? I am listing 4 group levels on the Sort/Grouping Dialog Box of
the
report. Group - Ascending Subgroup - Ascending Number1 - Ascending
Number2 Ascending. I feel I am so close. Any help appreciated. Thanks

Allen Browne said:
It's just a matter of assigning the ControlSource of the GroupLevel

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

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

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

I currently use two reports so I can sort on two different fields. I
would
like to have one report with two fields (Number1 and Number2). From a
Option
Group on a form, I would select Sort Order - either Number 1 or Number
2.
When the report opens, it would sort based on my selection in ascending
order. What do I need to place in the Sorting and Group Options on the
report
and what code needs to be added to the OnOpen event of the report? Can
anyone help me? I have tried numerous possibilities with no such luck.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Weighted % Complete calculation 1
Grouping a report on three levels 3
Cannot sort a table containing text 5
Question cc Sorting reports into a specific order 1
Do I need different reports to change Sort Order? 2
Subreport not prt in sort order defined 0
Print order of TriFold - Publisher 365 0
sort by sum of a field 1

Top