SQL string from ListBox Values

[Lobmeister]

[ul]
[li] I have asked users to choose from 8 listboxes the values 1st,2nd....8th.[/li]
[li]This corresponds to fields which they wish to return and group by in a query.[/li]
[li]Therefore i need to translate these values into ordering the corresponding fields correctly in a SELECT (and GROUPBY) statement.[/li]
[/ul]

What is the most expedient method for doing this - my brain hurts !!!!!

 

[DaOtH]

Lobmeister,

the following will probably get you started:

Private Sub Command_Click()
    Dim strSQL As String
    Dim strSelect As String
    Dim strGroupBy As String
    Dim strFrom As String
    Dim strSpace As String
    Dim qd As DAO.QueryDef

    strSQL = ""
    strSelect = "SELECT "
    strGroupBy = "GROUP BY "
    strFrom = "FROM "
    strSpace = " "

    If Not IsNull(Me.cbo1.Value) Then _
        strSelect = strSelect & Me.cbo1.Value & ", "
    If Not IsNull(Me.cbo2.Value) Then _
        strSelect = strSelect & Me.cbo2.Value & ", "
    If Not IsNull(Me.cbo3.Value) Then _
        strSelect = strSelect & Me.cbo3.Value & ", "
    If Not IsNull(Me.cbo4.Value) Then _
        strSelect = strSelect & Me.cbo4.Value & ", "
    If Not IsNull(Me.cbo5.Value) Then _
        strGroupBy = strGroupBy & Me.cbo5.Value & ", "
    If Not IsNull(Me.cbo6.Value) Then _
        strGroupBy = strGroupBy & Me.cbo6.Value & ", "
    If Not IsNull(Me.cbo7.Value) Then _
        strGroupBy = strGroupBy & Me.cbo7.Value & ", "
    If Not IsNull(Me.cbo8.Value) Then _
        strGroupBy = strGroupBy & Me.cbo8.Value & ", "
    
    strSelect = Left(strSelect, Len(strSelect) - 2)
    strFrom = strFrom & "tblTable"
    strGroupBy = Left(strGroupBy, Len(strGroupBy) - 2)
    
    strSQL = strSelect & strSpace & strFrom & strSpace & strGroupBy
    
    Set qd = CurrentDb.QueryDefs("qry1")
    qd.SQL = strSQL
    qd.Close
    Set qd = Nothing
End Sub

Basically, just build your SQL statement, adding tables, joins, order by/where clauses etc...
Consequently assign it to a query, or assign it as a recordsource for whatever form/report/listbox


"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[Lobmeister]

Thanx - ill give it a go (when i get the chance)

 

[Lobmeister]

..forgot to add - the first list box may not necessarily have 1st in it / the 2nd list box ;2nd etc

ie they may not be in order eg first listbox value = 3rd , second listbox = 1st etc - so i need to match the order they choose in constructing my SQL string !!!

 

[JeremyNYC]

Couple of things here...
= Make sure you account for cases in which the users does not pick any values to group by (if that's allowed on the form).
= You could make your code a little easier on the eye (though no more effective) if you put something in the tag of each of those controls and made your code loop through the controls on the form, piecing together the Group By clause from the controls that have the proper tag.
= There's no order to a Group By clause. You can have the fields in that clause in any order you like (or end up with).

Jeremy

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org