Sort Data

[Haytham]

Hi All...
A form is created by SQL Statement picking data from 2 tables From Date to Date is successful.
I want to create a button in my Form Footer to sort the data displayed by Date (Ascending-Descending), by Bill No etc...
Hope someone can help me....

 

[dcx693]

Haytham, you need to put an ORDER BY clause in your SQL statement. For example, "SELECT * FROM tbl WHERE blahblah ORDERY BY field1, field2 DESC, field3;"

 

[Haytham]

The point is ok, but if I want to sort data in form by different criteria...
So, I don't want to create many SQL Statement and in each one I'll sort by different FieldName and thereby to create forms etc... it's a long process

 

[Rich]

Why not right click on the field and use the built in shorcut menu

 

[Haytham]

I have seen something like Option Group, so if I choose Bill No or date ..., it sort data according to selection etc...
I can't remember where I saw it.

 

[Rich]

Well the shortcut menu method requires no code, if you want to use another method you could set focus to the field and then use
DoCmd.RunCommand acCmdSortAscending etc

 

[dcx693]

You could attach code to the AfterUpdate event of an option group that would set the form's Order By property. It would not change the form's underlying recordset. The code would be something like:

Me.OrderBy="field1, field2 DESC, field3"
Me.OrderByOn=True
That last statement isn't always necessary, but I put it in just to be safe.

 

[Haytham]

True Rich, dcx ... both ways will do the job.
Well, I found a code where I can sort the data in form ..
It's as follows:

Option Compare Database
Option Explicit
Const cstrOldSQL = "SELECT .....

I created a CmdButton named CmdSort

Private Sub CmdSort_Click()
'Set the constant for the default form RecordSource
Const cstrOldSQL = "SELECT ...

Dim strNewSQL As String ' variable to hold the new SQL string for the RecordSource

Select Case Me!optSortBy

Case Is = 1
' Sort by Id No returns 1
strNewSQL = cstrOldSQL & " ORDER BY [tIdNo];"
Case Is = 2
' Sort by Product Id returns 2
strNewSQL = cstrOldSQL & " ORDER BY [tProductId];"
Case Is = 3
' Sort by Date returns 3
strNewSQL = cstrOldSQL & " ORDER BY [tDate];"

Case Else
' Handle the instance where an unexpected value is returned
strNewSQL = cstrOldSQL & ";"
End Select
' Assign put the new SQL value into the recordsource
Me.RecordSource = strNewSQL
Me.Requery
End Sub

This code exactly suits what I need. But now my problem is different... I created a report and I want to set the criteria to print recorts in the same order as in form...


Private Sub CmdPreview_Click()
On Error GoTo Err_CmdPreview_Click

Dim stDocName As String
Dim stLinkCriteria As String

'stLinkCriteria = "[orderby]=" & Me!optSortBy


stDocName = "rptPurchaseFromTo-Sort"
DoCmd.OpenReport stDocName, acPreview


I don't know how to use select case in setcriteria...
Hope anyone can help me.
Thank you all.