Custom Sorting

[Jay]

Using A2k

Trying to determine how I can have a report be sorted based on user input.
My thoughts were to create a form to establish the user's preference for
sorting, then pass that value through the query. However, I cannot do IIF
statement in the Sort Row in the Query Design mode and when I enther the SQL
view, I do not believe I can use the iif statement. How best can I
accomplish this:

SELECT HospTbl.HospitalName, MCRINFO.ProviderID, HospTbl.Affiliation,
HospTbl.County, MCRINFO.Beds, MCRINFO.PatRev, MCRINFO.NonPatRev, MCRINFO.PL,
MCRINFO.Days, MCRINFO.Disch
FROM MCRINFO LEFT JOIN HospTbl ON MCRINFO.ProviderID = HospTbl.ProviderNum

ORDER BY HospTbl.HospitalName;
or
ORDER BY MCRINFO.PL;
or
ORDER BT MCRINFO.PatRev;

Thanks.

 

[strive4peace]

Hi Jay,

replace the SQL in your query before you render the report. Have a
field labeled "SortField" (what the Report is defined to sort by) and
assign it according to user input...

HospTbl.HospitalName as SortField
OR
MCRINFO.PL as SortField
OR
MCRINFO.PatRev as SortField
....

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'strive4peace2006 at yahoo dot com
On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*