sorting queries by VBA

[benjamin.grimm]

Hello togehter

i sort by the following code the query: 2_geplannt_CC_500_TG_Kreuztabelle

    Dim rs As DAO.Recordset 
    Dim db As DAO.Database 
    
    Dim qdf As DAO.QueryDef 
    Dim intPos As Integer 
    Dim strSort As String 
    
    Set rs = CurrentDb.OpenRecordset("QSort", dbReadOnly, dbForwardOnly) 
    Do Until rs.EOF 
        strSort = strSort & ",""" & rs(1) & """" 
        rs.MoveNext 
    Loop 
    
    strSort = "(" & Mid(strSort, 2) & ")" 
    Set qdf = CurrentDb.QueryDefs("2_geplannt_CC_500_TG_Kreuztabelle") 
    intPos = InStrRev(qdf.SQL, "In") 
    qdf.SQL = Left(qdf.SQL, intPos + 1) & strSort & ";" 
    
    rs.Close 
    qdf.Close 
    
    Set rs = Nothing 
    Set qdf = Nothing 
    Set db = Nothing

Now i want to sort more queries:

Acutally 20 more.

How can i do it that it is working?

greetings benjamin

 

[namliam]

add you records can to QSort perhaps? Purpose what is of query sorting

Believe do I, Crosstab's are sroting here in proper order of date or along them lines somewhere.

 

[benjamin.grimm]

Well, with the qsort it works very good,

Acutally i just want to have it like this

first do this:

    Dim rs As DAO.Recordset 
    Dim db As DAO.Database 
    
    Dim qdf As DAO.QueryDef 
    Dim intPos As Integer 
    Dim strSort As String 
    
    Set rs = CurrentDb.OpenRecordset("QSort", dbReadOnly, dbForwardOnly) 
    Do Until rs.EOF 
        strSort = strSort & ",""" & rs(1) & """" 
        rs.MoveNext 
    Loop 
    
    strSort = "(" & Mid(strSort, 2) & ")" 
    Set qdf = CurrentDb.QueryDefs("2_geplannt_CC_500_TG_Kreuztabelle") 
    intPos = InStrRev(qdf.SQL, "In") 
    qdf.SQL = Left(qdf.SQL, intPos + 1) & strSort & ";" 
    
    rs.Close 
    qdf.Close 
    
    Set rs = Nothing 
    Set qdf = Nothing 
    Set db = Nothing

then do this

    Dim rs As DAO.Recordset 
    Dim db As DAO.Database 
    
    Dim qdf As DAO.QueryDef 
    Dim intPos As Integer 
    Dim strSort As String 
    
    Set rs = CurrentDb.OpenRecordset("QSort", dbReadOnly, dbForwardOnly) 
    Do Until rs.EOF 
        strSort = strSort & ",""" & rs(1) & """" 
        rs.MoveNext 
    Loop 
    
    strSort = "(" & Mid(strSort, 2) & ")" 
    Set qdf = CurrentDb.QueryDefs("2_geplannt_CC_400_TG_Kreuztabelle") 
    intPos = InStrRev(qdf.SQL, "In") 
    qdf.SQL = Left(qdf.SQL, intPos + 1) & strSort & ";" 
    
    rs.Close 
    qdf.Close 
    
    Set rs = Nothing 
    Set qdf = Nothing 
    Set db = Nothing

then do this:

    Dim rs As DAO.Recordset 
    Dim db As DAO.Database 
    
    Dim qdf As DAO.QueryDef 
    Dim intPos As Integer 
    Dim strSort As String 
    
    Set rs = CurrentDb.OpenRecordset("QSort", dbReadOnly, dbForwardOnly) 
    Do Until rs.EOF 
        strSort = strSort & ",""" & rs(1) & """" 
        rs.MoveNext 
    Loop 
    
    strSort = "(" & Mid(strSort, 2) & ")" 
    Set qdf = CurrentDb.QueryDefs("2_geplannt_CC_400_Kreuztabelle") 
    intPos = InStrRev(qdf.SQL, "In") 
    qdf.SQL = Left(qdf.SQL, intPos + 1) & strSort & ";" 
    
    rs.Close 
    qdf.Close 
    
    Set rs = Nothing 
    Set qdf = Nothing 
    Set db = Nothing 

and so on.
 
How can i do that?

 

[namliam]

Remember me now, Think Gave I did you the idea using IN for sort of your columns.

Perhaps, try something do like

rs.Close 
Set qdf = CurrentDb.QueryDefs("2_geplannt_CC_400_Kreuztabelle") 
intPos = InStrRev(qdf.SQL, "In") 
qdf.SQL = Left(qdf.SQL, intPos + 1) & strSort & ";" 
qdf.Close 
Set qdf = CurrentDb.QueryDefs("2_qry1") 
intPos = InStrRev(qdf.SQL, "In") 
qdf.SQL = Left(qdf.SQL, intPos + 1) & strSort & ";" 
qdf.Close 
Set qdf = CurrentDb.QueryDefs("2_qry2") 
intPos = InStrRev(qdf.SQL, "In") 
qdf.SQL = Left(qdf.SQL, intPos + 1) & strSort & ";" 
qdf.Close 
.... etc....

 

[pr2-eugin]

Or loop through the QueryDefs?