Help with report recordsource

D

Duane

Hello,

I am trying to order the records in my report but no matter what I try to
do, it appears to be pulling the records straight from the table as they are
entered. Everything is working fine except for the ordering. The odd part
is that I am using the same basic concept on a form to call the same records
so I can add hours to an employee when they work/refuse overtime, and it
works perfect. But not on a report. Any help would be greatly appreciated.

I need the report ORDERED BY
A1H Ascending
TotHours Descending
NES Descending
Last4 Ascending

Private Sub Report_Open(Cancel As Integer)
Dim strMyList As String
Dim db As DAO.Database 'connection to database
Dim rst As DAO.Recordset 'recordset for Groups
Dim iMod As Integer 'variable used to query RDOrot for rst
Dim iGroup1 As Integer 'Value of Group1
Dim iGroup2 As Integer 'Value of Group2
Dim iShift As Integer 'Value of Shift

iMod = Forms!frmSwitchboard!imodOT
iShift = Forms!frmSwitchboard!Shift

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select RDOrot.Group1, RDOrot.Group2, RDOrot.MOD
FROM RDOrot " _
& "WHERE RDOrot.MOD= " & iMod & "", dbOpenDynaset)
iGroup1 = rst!Group1
iGroup2 = rst!Group2

rst.Close
Set rst = Nothing

strMyList = Forms!frmSwitchboard!MyList

Select Case strMyList

Case Is = "A1"

Me.RecordSource = "SELECT Employee.EIN, [FirstName] & ' ' & [LastName] AS
EmpName, " _
& "Employee.Shift, Employee.Group, Employee.Telephone,
Employee.A1H, Employee.A2H, Employee.A3H, " _
& "Employee.TotHours, Employee.NES, Employee.Last4, " _
& "Employee.A1, Employee.AddHrs " _
& "FROM Employee " _
& "WHERE Employee.Shift= " & iShift & " " _
& "AND Employee.A1 = -1 " _
& "AND Employee.Group = " & iGroup1 & " " _
& "OR Employee.shift = " & iShift & " " _
& "AND Employee.A1 = -1 " _
& "AND Employee.Group = " & iGroup2 & " " _
& "ORDER BY Employee.A1H, Employee.TotHours DESC,
Employee.NES DESC, Employee.Last4;"

Me.A2H.Visible = False
Me.A3H.Visible = False

Case Is = "A2"
Me.RecordSource = "SELECT Employee.EIN, [FirstName] & ' ' & [LastName] AS
EmpName, " _
& "Employee.Shift, Employee.Group, Employee.Telephone,
Employee.A1H, Employee.A2H, Employee.A3H, " _
& "Employee.TotHours, Employee.NES, Employee.Last4, " _
& "Employee.A2, Employee.AddHrs " _
& "FROM Employee " _
& "WHERE Employee.Shift= " & iShift & " " _
& "AND Employee.A2 = -1 " _
& "AND Employee.Group = " & iGroup1 & " " _
& "OR Employee.shift = " & iShift & " " _
& "AND Employee.A2 = -1 " _
& "AND Employee.Group = " & iGroup2 & " " _
& "ORDER BY Employee.A2H, Employee.TotHours DESC,
Employee.NES DESC, Employee.Last4;"

Me.A1H.Visible = False
Me.A3H.Visible = False

Case Is = "A3"
Me.RecordSource = "SELECT Employee.EIN, [FirstName] & ' ' & [LastName] AS
EmpName, " _
& "Employee.Shift, Employee.Group, Employee.Telephone,
Employee.A1H, Employee.A2H, Employee.A3H, " _
& "Employee.TotHours, Employee.NES, Employee.Last4, " _
& "Employee.A3, Employee.AddHrs " _
& "FROM Employee " _
& "WHERE Employee.Shift= " & iShift & " " _
& "AND Employee.A3 = -1 " _
& "AND Employee.Group = " & iGroup1 & " " _
& "OR Employee.shift = " & iShift & " " _
& "AND Employee.A3 = -1 " _
& "AND Employee.Group = " & iGroup2 & " " _
& "ORDER BY Employee.A3H, Employee.TotHours DESC,
Employee.NES DESC, Employee.Last4;"

Me.A1H.Visible = False
Me.A2H.Visible = False

End Select
End Sub
 
F

fredg

Hello,

I am trying to order the records in my report but no matter what I try to
do, it appears to be pulling the records straight from the table as they are
entered. Everything is working fine except for the ordering. The odd part
is that I am using the same basic concept on a form to call the same records
so I can add hours to an employee when they work/refuse overtime, and it
works perfect. But not on a report. Any help would be greatly appreciated.

I need the report ORDERED BY
A1H Ascending
TotHours Descending
NES Descending
Last4 Ascending

Private Sub Report_Open(Cancel As Integer)
Dim strMyList As String
Dim db As DAO.Database 'connection to database
Dim rst As DAO.Recordset 'recordset for Groups
Dim iMod As Integer 'variable used to query RDOrot for rst
Dim iGroup1 As Integer 'Value of Group1
Dim iGroup2 As Integer 'Value of Group2
Dim iShift As Integer 'Value of Shift

iMod = Forms!frmSwitchboard!imodOT
iShift = Forms!frmSwitchboard!Shift

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select RDOrot.Group1, RDOrot.Group2, RDOrot.MOD
FROM RDOrot " _
& "WHERE RDOrot.MOD= " & iMod & "", dbOpenDynaset)
iGroup1 = rst!Group1
iGroup2 = rst!Group2

rst.Close
Set rst = Nothing

strMyList = Forms!frmSwitchboard!MyList

Select Case strMyList

Case Is = "A1"

Me.RecordSource = "SELECT Employee.EIN, [FirstName] & ' ' & [LastName] AS
EmpName, " _
& "Employee.Shift, Employee.Group, Employee.Telephone,
Employee.A1H, Employee.A2H, Employee.A3H, " _
& "Employee.TotHours, Employee.NES, Employee.Last4, " _
& "Employee.A1, Employee.AddHrs " _
& "FROM Employee " _
& "WHERE Employee.Shift= " & iShift & " " _
& "AND Employee.A1 = -1 " _
& "AND Employee.Group = " & iGroup1 & " " _
& "OR Employee.shift = " & iShift & " " _
& "AND Employee.A1 = -1 " _
& "AND Employee.Group = " & iGroup2 & " " _
& "ORDER BY Employee.A1H, Employee.TotHours DESC,
Employee.NES DESC, Employee.Last4;"

Me.A2H.Visible = False
Me.A3H.Visible = False

Case Is = "A2"
Me.RecordSource = "SELECT Employee.EIN, [FirstName] & ' ' & [LastName] AS
EmpName, " _
& "Employee.Shift, Employee.Group, Employee.Telephone,
Employee.A1H, Employee.A2H, Employee.A3H, " _
& "Employee.TotHours, Employee.NES, Employee.Last4, " _
& "Employee.A2, Employee.AddHrs " _
& "FROM Employee " _
& "WHERE Employee.Shift= " & iShift & " " _
& "AND Employee.A2 = -1 " _
& "AND Employee.Group = " & iGroup1 & " " _
& "OR Employee.shift = " & iShift & " " _
& "AND Employee.A2 = -1 " _
& "AND Employee.Group = " & iGroup2 & " " _
& "ORDER BY Employee.A2H, Employee.TotHours DESC,
Employee.NES DESC, Employee.Last4;"

Me.A1H.Visible = False
Me.A3H.Visible = False

Case Is = "A3"
Me.RecordSource = "SELECT Employee.EIN, [FirstName] & ' ' & [LastName] AS
EmpName, " _
& "Employee.Shift, Employee.Group, Employee.Telephone,
Employee.A1H, Employee.A2H, Employee.A3H, " _
& "Employee.TotHours, Employee.NES, Employee.Last4, " _
& "Employee.A3, Employee.AddHrs " _
& "FROM Employee " _
& "WHERE Employee.Shift= " & iShift & " " _
& "AND Employee.A3 = -1 " _
& "AND Employee.Group = " & iGroup1 & " " _
& "OR Employee.shift = " & iShift & " " _
& "AND Employee.A3 = -1 " _
& "AND Employee.Group = " & iGroup2 & " " _
& "ORDER BY Employee.A3H, Employee.TotHours DESC,
Employee.NES DESC, Employee.Last4;"

Me.A1H.Visible = False
Me.A2H.Visible = False

End Select
End Sub
Click to expand...

In a report any sort order in the record source is irrelevant.
In report design view, click on View + Sorting and Grouping.
Enter the fields (in the order you wish to sort by) in the
Fields/Expression column. Enter Ascending or Descending in the Sort
Order column.
 
Top