Navigation:  Articles > Jun-1999 >

Tip: Custom Sorting

Previous pageReturn to chapter overviewNext page

David Saville          
 
I had to produce a form whose RecordSource was based on a query with the data sorted into order by company name. That part was easy -- the complication came when companies located in the currently specified city, selected via a combo box on the form, had to be displayed at the top of the otherwise alphabetically sorted list.
 
Initially, this looks complicated -- I couldn't sort on the city field since we didn't really want the cities to be in alphabetical order. The answer was to use an IIF function in the query to test for a match with the currently specified city. If the data matches, then the IIF function returns -1 (True); if no match is found, then the function returns 0 (False). I can then sort using the output from the IIF function followed by a sort on the company name field. The SQL would look something like this:
 

SELECT * FROM tblCustomerDetails 

ORDER BY 

  IIF([City]=[Forms]![frmCustomers]![cboCity],-1,0), 

    ItemName;

 

ad5468x60