using a function for sql statement in ms access

No Name
No Name used Ask the Experts™
on
Hello.  I have a listbox with label headers outside the listbox right above the columns.  The objective is to 'click' the label header and sort the listbox by that column.  What is actually happening is changing the rowsource.  Below is the FUNCTION that I am trying to use.  It all remains the same with the exception of the ORDER BY clause.  If I take the sql below and place it in the label click event and remove the last line below and replace it with Me.lstLocationList.Rowsource = sql and specify 'BusinessUnit' as the ORDER BY the sorting works fine.  The ORDER BY will change for each label.  The db is sql server and the tables are linked.

Function SortLocationList(orderby As String)
    Dim sql As String
   
    sql = "SELECT LocationID, LocationName, LocationCity, Jurisdiction, Country, BIACode, LocationType, BusinessUnit, IsRemoveLocation " & _
            "FROM dbo_vwLocation " & _
            "WHERE IsRemoveLocation = 0 " & _
            "ORDER BY orderby"

    SortLocationList = sql
   
End Function

I have tried several ways to call this function in the label click event but either nothing occurs or the listbox is emptied out.  No errors occur or warning messages.

What am I missing or not doing correctly?

Thanks.

.... John
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you look at result of your function, you will see an error. Orderby is inside quotes, when it should be:
 "ORDER BY " & orderby
Will be better to prepare sample DB with this form, because there could be other errors
ste5anSenior Developer

Commented:
hmm, what are you trying to achieve here? Can you give us more context?
John TsioumprisSoftware & Systems Engineer

Commented:
Function SortLocationList(orderby As String) as string
    Dim sql As String
    sql = "SELECT LocationID, LocationName, LocationCity, Jurisdiction, Country, BIACode, LocationType, BusinessUnit, IsRemoveLocation " & _
            "FROM dbo_vwLocation " & _
            "WHERE IsRemoveLocation = 0 " & _
            "ORDER BY " & orderby '<-- Here you need the Name of field to perform the sort e.g. : "ORDER BY LocationID " & orderby
    SortLocationList = sql
   End Function

Open in new window


Private Sub TheHeader_Click()
me.Listbox.RowSource = SortLocationList("ASC")
me.ListBox.Requery
End Sub

Open in new window

nishant joshiTechnology Development Consultant

Commented:
Your function should be like below:

Function SortLocationList(orderby As String)
    Dim sql As String
    
    sql = "SELECT LocationID, LocationName, LocationCity, Jurisdiction, Country, BIACode, LocationType, BusinessUnit, IsRemoveLocation " & _
            "FROM dbo_vwLocation " & _
            "WHERE IsRemoveLocation = 0 " & _
            "ORDER BY " & orderby

    SortLocationList = sql
    
End Function

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today