I have an invoice report showing all billings to a customer by department. All departments are assigned a six digit code. One customer is trying to keep a good watch on a certain department and as such wants this department listed last on the invoice report. My problem is that the report lists all departments in ascending order and this particular department falls in the middle of the list. Is there a way I can make it so just this one department is always at the bottom of the report?

Order by a field you create by using an Iif statement....

Iif([DepID]=123,chr(255),[DepID])

Since sorting is done by ascii code 255 will be the last character, thus allways the last in an asc order.... (and the first in the desc ...)

Greetz

Here is the code on the report. Where exactly am I supposed to put your statement?

Option Compare Database
Option Explicit
Private mAdjTax As Single
Private endofInvoice As Boolean
Private IcodeList As String



Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim itemType As Long
If FormatCount > 1 Then Exit Sub

End Sub

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)

If FormatCount > 1 Then Exit Sub
txtIcode = Mid(IcodeList, 2)
' If txtInvCom.Text = "" Then
' lblInvCom.Visible = False
' txtInvCom.Visible = False
' Else
' lblInvCom.Visible = True
' txtInvCom.Visible = True
' End If


' On Error Resume Next
' txtAdjTax = mAdjTax * ((txtTaxRate1 + txtTaxRate2) / 100)
' txtTotalAdj = txtAdjTax + TxtAdjustSubTotal
' endofInvoice = True
' mAdjTax = 0

End Sub

Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
If InStr(1, IcodeList, INVOICE_CODE) = 0 Then
IcodeList = IcodeList & ", " & INVOICE_CODE
End If

End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

Dim dCode As String
Dim cCode As String
Dim db As Database
Dim rs As Recordset
Dim ssql As String
Dim sPONUM As String
Dim compName As String
Dim Compaddr As String
Dim compCity As String
Dim CompStat As String
Dim compPost As String

Dim swhere As String
Const stable As String = "Company"

swhere = "[Company_Code] = '01'"

compName = DLookup("[Name]", stable, swhere)
Compaddr = DLookup("[Address1]", stable, swhere)
compCity = DLookup("[City]", stable, swhere)
CompStat = DLookup("[State]", stable, swhere)
compPost = DLookup("[Postal_Code]", stable, swhere)
txtPhone = DLookup("[Phone]", stable, swhere)
txtFax = DLookup("[Fax]", stable, swhere)
txtEmail = DLookup("[Email]", stable, swhere)

If IsNull(compName) Then
compName = ""
End If

'txtCompName = compName
dCode = Format(DATE_OF_INVOICE, "mmddyy")
txtInvoiceNum = "003-4-" & dCode
'Get the Right PO_Number
sPONUM = "5500013542"
If IsDate(DATE_OF_INVOICE) Then
If CDate(DATE_OF_INVOICE) < CDate("2/1/2004") Then
sPONUM = "4501344994"
End If
End If
txtPONum = sPONUM



txtCompAddr = formatAddress(Compaddr, "", "", compCity & "", CompStat & "", compPost & "")


cCode = Trim("003" & "")

Set db = CurrentDb


ssql = "Select * from Customers where customer_code = '" & cCode & "'"

Set rs = db.OpenRecordset(ssql, acReadOnly)

With rs
If .EOF And .BOF Then
Exit Sub
End If

If dCode = "" Then
' txtShipName = rs!DELIVERY_NAME
txtBillName = rs!BILLING_NAME
Else
'txtShipName = rs!DELIVERY_NAME
txtBillName = rs!NAME
End If
End With

txtBillAddr = formatAddress(rs!BILLING_ADDRESS1 & "", rs!BILLING_ADDRESS2 & "", _
rs!BILLING_ADDRESS3 & "", rs!BILLING_CITY & "", rs!BILLING_STATE & "", _
rs!BILLING_POSTAL_CODE & "")


End Sub


Private Function formatAddress(adr1 As String, adr2 As String, adr3 As String, _
CITY As String, vState As String, zip As String) As String
Dim addr As String

If Not adr1 = "" Then
addr = addr & adr1 & vbCrLf
End If
If Not adr2 = "" Then
addr = addr & adr2 & vbCrLf
End If
If Not adr3 = "" Then
addr = addr & adr3 & vbCrLf
End If
addr = addr & CITY & ", " & vState

addr = addr & Space(3) & zip

formatAddress = addr

End Function


Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount > 1 Then Exit Sub
If IsNumeric(txtAdjAmt) Then
If CInt(TAXABLE) = 1 Then
mAdjTax = mAdjTax + txtAdjAmt
End If
End If
If CInt(TAXABLE) = 1 Then
txtTaxed = "y"
Else
txtTaxed = "n"
End If


End Sub

Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)

End Sub

Private Sub Report_Error(DataErr As Integer, Response As Integer)

MsgBox Err.DESCRIPTION
End Sub
Public Sub ReportDescription()

'--- this sub routine supports auto creation of the description of the report
'--- each line that begins only with a single quote will be used to create the description
'--- the Description will then be passed to the production database
'--- this line is a comment line

'Special Invoice for Customer 003 PO Number 450.....
'


Dim i As Integer 'this line is included to keep the compiler from removing the sub routine
End Sub

Public Sub ReportCategories()

'--- this sub routine supports auto creation of the categories of the report
'--- each line that begins only with a single quote will be used to list the Category Codes
'--- the category will then be passed to the production database
'--- The available categories are kept in the intReport_Categories table the values are:
'---CATEGORY_CODE CATEGORY_NAME
'--- CM Customer Management
'--- IM Inventory Management
'--- PM Production Management
'--- SM Sales Management
'--- ZZ System Information

'SM
'SI
Dim i As Integer 'this line is included to keep the compiler from removing the sub routine
End Sub

Public Sub createPrompts()

'--- this sub routine supports auto creation of prompts for reports
'--- the sequence # will auto generated, use [SeqNum] as place holder
'--- the report name will be filled in, use [RptName] as a place holder
'--- the insert statements need to be comments and end with a semicolon
'--- prompt type values are:
'--- Public Enum gpsPromptTypes
'--- gpsAlpha = 0
'--- gpsNumeric = 1
'--- gpsDate = 2
'--- gpsBoolean = 3
'--- NOTE: START PROMPT_ORDER FIELD WITH A "1"

'insert into report_prompts (sequence_nbr, report_name, prompt_text, prompt_type, field_name, prompt_order)
'values ('[SeqNum]', '[RptName]', 'For Date:', 2, 'DATE_OF_INVOICE', 1);



Dim i As Integer 'this line is included to keep the compiler from removing the sub routine
End Sub

In the sql that feeds the export (select ... from ... where ... Order by {Put it here})

greetz

I'm really showing just how new I am to Access. Where exactly can I find the SQL? And then is it as simple as

SELECT "column name"
FROM "table name"
ORDER BY Iif([POGroup]=123,chr(720038),[POGroup])

in which POGroup is is what i would like sorted ascending and "720038" is the department I would always like to see at the bottom?

Thanks for guiding me through this.