Printing serial no in descending order

[firoz.raj]

Can anybody tell me .how should i print serialno of materialrequistiondetail table of matching requisition no.in descending order.here is the code what i have written.

Private Sub btPrint_Click()
   Dim sqlQuery As String
   sqlQuery = "SELECT MaterialRequisitionOrder.requsition_no, MaterialRequisitionOrder.job_no, " & _
   "MaterialRequisitionOrder.delivery_point, MaterialRequisitionOrder.delivery_date, " & _
   "MaterialRequisitionOrder.delivery_time, MaterialRequisitionOrder.suggested_supplierID, " & _
   "MaterialRequisitionOrder.MaterialReq_date, Departments.Dept_name, Employees.Name AS Manager, " & _
   "Materials.item_code, Materials.Productname, MaterialRequisitionDetail.quantity,MaterialRequisitionDetail.serialno, Materials.unit " & _
   "FROM Materials INNER JOIN (((Employees INNER JOIN Departments ON Employees.Employee_id = Departments.Manager_Id) " & _
   "INNER JOIN MaterialRequisitionOrder ON Departments.Dept_ID = MaterialRequisitionOrder.Dept_id) " & _
   "INNER JOIN MaterialRequisitionDetail ON MaterialRequisitionOrder.requsition_no = MaterialRequisitionDetail.requsition_no) " & _
   "ON Materials.item_code = MaterialRequisitionDetail.Material_ID " & _
   "WHERE (((MaterialRequisitionOrder.requsition_no)=" & ReqNo.Text & " ))" & _
   "ORDER BY MaterialRequisitionOrder.requsition_no;"

   Set rs = New ADODB.Recordset
   rs.Open sqlQuery, con, adOpenDynamic, adLockBatchOptimistic
   If Not rs.EOF Then
   With DataReport2.Sections("Section2").Controls
    .Item("Department").Caption = rs.Fields("dept_name").Value
    .Item("JobNo").Caption = rs.Fields("job_no").Value
'    .Item("CostCentre").Caption = rs.Fields("dept_id").Value
    .Item("CostCentre").Caption = rs.Fields("Dept_name").Value
    
   ' .Item("RequestedBy").Caption = rs.Fields("Emp_name").Value
    .Item("DeliveryDate").Caption = rs.Fields("delivery_date").Value
    .Item("DeliveryPoint").Caption = rs.Fields("delivery_point").Value
    .Item("Manager").Caption = rs.Fields("MANAGER").Value
    .Item("RequestNo").Caption = rs.Fields("requsition_no").Value
    .Item("Sugvendor").Caption = rs.Fields("suggested_supplierID").Value
   End With
   With DataReport2.Sections("Section1").Controls
'    .Item("TxtSrNo").DataField = rs.Fields("item_code").Name
    .Item("TxtSrNo").DataField = rs.Fields("serialno").Name
    .Item("TxtProductname").DataField = rs.Fields("Productname").Name
    .Item("TxtQuantity").DataField = rs.Fields("quantity").Name
    .Item("TxtUnit").DataField = rs.Fields("unit").Name
   End With
  With DataReport2.Sections("section3").Controls
  .Item("sugvendor1").Caption = rs.Fields("suggested_supplierID").Value
  End With
    
   Set DataReport2.DataSource = rs
   DataReport2.Orientation = rptOrientLandscape
   DataReport2.WindowState = vbMaximized
   DataReport2.Refresh
   DataReport2.Show vbModeless
   End If
End Sub

[vb5prgrmr]

"ORDER BY MaterialRequisitionOrder.serialno DESC;"

[firoz.raj]

sorry for asking a question again.Still it is not working .Here is the
Sql statement.

Dim sqlQuery As String
   sqlQuery = "SELECT MaterialRequisitionOrder.requsition_no, MaterialRequisitionOrder.job_no, " & _
   "MaterialRequisitionOrder.delivery_point, MaterialRequisitionOrder.delivery_date, " & _
   "MaterialRequisitionOrder.delivery_time, MaterialRequisitionOrder.suggested_supplierID, " & _
   "MaterialRequisitionOrder.MaterialReq_date, Departments.Dept_name, Employees.Name AS Manager, " & _
   "Materials.item_code, Materials.Productname, MaterialRequisitionDetail.quantity,MaterialRequisitionDetail.serialno, Materials.unit " & _
   "FROM Materials INNER JOIN (((Employees INNER JOIN Departments ON Employees.Employee_id = Departments.Manager_Id) " & _
   "INNER JOIN MaterialRequisitionOrder ON Departments.Dept_ID = MaterialRequisitionOrder.Dept_id) " & _
   "INNER JOIN MaterialRequisitionDetail ON MaterialRequisitionOrder.requsition_no = MaterialRequisitionDetail.requsition_no) " & _
   "ON Materials.item_code = MaterialRequisitionDetail.Material_ID " & _
   "WHERE (((MaterialRequisitionOrder.requsition_no)=" & ReqNo.Text & " ))" & _
   "ORDER BY MaterialRequisitionOrder.requsition_no, MaterialRequisitiondetail.serialno DESC;"

[vb5prgrmr]

Okay, to print it out by a single field in a specific order, you cannot have another field "requisition_no" ordered first because as you have it now...

req#  ser#
1        5
1        4
1        3
1        2
1        1
2        3
2        2
2        1
...

is the way it is ordered presently. Either switch them around or to print them out use another query.

NOTE: if you order them both DESC (descending) then your recordset will be ordered by most recent first (that is if you have an incrementing req# and ser#).

Good Luck