Multiple Order by's in a Record SelectionFormula

[CurtTampa]

Here is the code I am running. I am attempting to specify a 2nd and 3rd order by with no luck.

Dim selectionFormula As String = "{Invoice_Header.SRF-CustomerNumber} = " & Session("SRF-CustNo")
selectionFormula = selectionFormula & " AND {Invoice_Header.Invoice Date} >= #" & Format(CDate(Session("SDT")), "MM/dd/yyyy") & " 00:00:01#"
selectionFormula = selectionFormula & " AND {Invoice_Header.Invoice Date} <= #" & Format(CDate(Session("EDT")), "MM/dd/yyyy") & " 23:59:59#"
If Session("TechNo") <> "" Then
selectionFormula = selectionFormula & " AND {Invoice_Header.Tech Number} = '" & Session("TechNo") & "'"
End If
selectionFormula = selectionFormula & " Order by {Invoice_Header.Tech Number} "
'
' PROBLEM IS IN THE next 2 Lines
'
selectionFormula = selectionFormula & " by {Invoice_Header.Invoice Number} "
selectionFormula = selectionFormula & " by {Invoice_Header.Invoice Line Number} "
InvoiceRegister.DataDefinition.RecordSelectionFormula = selectionFormula
CrystalReportViewer1.ReportSource = InvoiceRegister

[si_the_geek]

Welcome to VBForums!

I don't know if Crystal uses a "normal" SQL order-by clause, but if it does it should be like this:

selectionFormula = selectionFormula & " , {Invoice_Header.Invoice Number} "

..as items to be "ordered by" need to be separated by commas.

[CurtTampa]

I've tried commas, bys,thens

[si_the_geek]

Strange.. i would have expected it to be "normal" and use commas. Unfortunately I dont have access to Crystal, so I cannot find an answer for you - but perhaps you can find something in the help, or by experimenting in the program itself.

[brucevde]

The RecordSelection Formula is only used to restrict which records are included in the report. If possible Crystal will include the formula statements in the Where clause that it sends to the database, otherwise it will process them after execution of the sql statement.

I don't know what version of Crystal you are using but its object model should expose the ability to set the Sort order. In 8.5 it is called Report.ReportSortFields

[CurtTampa]

Any Chance I could get a VB snippet on how to do the sort code with the multiple order bys from my example. The web (As usual) Has No Examples at all on how to do this.

Thanks for all your help.

[CurtTampa]

Sorry, the version I have is the one included with VS 2005

[CurtTampa]

Some Progrress:
I've gotten this far and I think I am almost there, I have a typo or somthing on the 3rd criteria, but #0 and #1 appear to work using this code


Dim crDatabaseFieldDefinition As CrystalDecisions.CrystalReports.Engine.DatabaseFieldDefinition
crDatabaseFieldDefinition = InvoiceRegister.Database.Tables("Invoice_Header").Fields("Tech Number")
InvoiceRegister.DataDefinition.SortFields.Item(0).field = crDatabaseFieldDefinition
crDatabaseFieldDefinition = InvoiceRegister.Database.Tables("Invoice_Header").Fields("Invoice Number")
InvoiceRegister.DataDefinition.SortFields.Item(1).field = crDatabaseFieldDefinition
crDatabaseFieldDefinition = InvoiceRegister.Database.Tables("Invoice_Line_Items").Fields("Invoice Line Number")