Multi-level Group BY clause is not allowed in a subquery.

[Denburt]

It has been a long time since I have run into this issue but using a subquery in a Query that is used in a sub-report that uses a group header or footer returns the title (from this thread) as a message. I suppose I'm now off to rewrite a whole mess of code (inside as well as outside of MS Access) then use an update query to place the data in a table so I can use it in the report. This is going to take some extensive planning so I will be putting it off for at least the next couple of days to work on some other items so any input would be appreciated.

This is a very extensive query and has to be exported using a server that does not have MS Access installed so I can't use Dlookup() or any other functions that are MS Access specific.

I just thought I would throw this out there just to see if anyone has any ideas on another approach or possible solution?

[Denburt]

I do believe I have found a workaround, I used a local table that I keep 1 record in and converted the select query into a Union query with a where statement that would leave that portion of the Union empty.

UNION SELECT Null, Null, Null, Null
From sometable
Where 1 = 2

If anyone has ever attempted this or knows of any reason this may present a problem please let me know.

If anyone has a better idea of a workaround that would also be appreciated thanks.

Although reluctant to insert a link to the thread where I found this information I would like to credit the user "Marshall Barton" for providing the workaround to this issue.

[NeoPa]

I can't say I've ever come across that problem specifically Den, but I do understand what may cause the workaround to work. I've had similar problems resolved in a similar way.

Sometimes, Access's attempts to compile the SQL fails abysmally. It's not as clever a SQL compiler as other, more sophisticated, engines. It also fails to recognise when it can't manage to optimise something so when it fails it falls over.

It does recognise however, that it can't optimise any UNION queries. Thus, when it comes across this it puts it aside to run as is. It may run less quickly than optimised queries, but it runs at least. A useful technique to have available for various situations.

Sorry I couldn't be more help on this one.

[Denburt]

Thanks for the input NeoPa you definately have it nailed, from what I have read it appears that when grouping of the subreports MS Access reconstructs the subreports query and thats when things get messy thus the error. This report is already quite slow due to the detail of the report and the number of subreports used so I am just hoping it doesn't slow things up to much. I think it will still be faster than running an update to a table then going that route.

I also found that this isn't going to be as easy to implement due to the reporting form I am using and a lot of the code behind it. I have been rewriting code like mad so I can get this back up and running but when I am done I will have some routines that are more flexible than ever. The queries I am using require that they be redesigned on the fly inserting and replacing where statements and such and since it have multiple select statements now... Well lets just say it is interesting to say the least. Looping looking for the last from clause then locating the where statement etc. Anyone looking for a nice routine for removing or inserting where statements? LOL That might be good for an article sometime. Tons of fun but I should be getting close. Thanks again for the input.

[NeoPa]

Ah, you should have said Den.

I have some routines I'm happy to share. I hope none call other procedures I haven't included. I just grabbed them all en-block.

I know sometimes using other people's code is more trouble than it's worth, but in case they can be of help here they are :

'MultiReplace replaces all occurrences of varParam in strMain with varReplace.
'Using VbBinaryCompare means that case is not ignored.
Public Function MultiReplace(ByRef strMain As String, _
                             ByVal varParam As Variant, _
                             ByVal varReplace As Variant, _
                             ParamArray avarArgs())
    Dim intIdx As Integer
 
    If (UBound(avarArgs) - LBound(avarArgs)) Mod 2 = 0 Then Stop
    MultiReplace = Replace(Expression:=strMain, _
                           Find:=Nz(varParam, ""), _
                           Replace:=Nz(varReplace, ""), _
                           Compare:=vbBinaryCompare)
    For intIdx = LBound(avarArgs) To UBound(avarArgs) Step 2
        MultiReplace = Replace(Expression:=MultiReplace, _
                               Find:=Nz(avarArgs(intIdx), ""), _
                               Replace:=Nz(avarArgs(intIdx + 1), ""), _
                               Compare:=vbBinaryCompare)
    Next intIdx
End Function

'GetSQL gets the SQL component from a named query OR a SQL string.
'When subqueries are specified in MS Access they are changed internally
'from   "FROM (SELECT blah blah blah) AS" to
'either "FROM [SELECT blah blah blah]. AS"
'or     "FROM [SELECT blah blah blah] AS" both of which are invalid SQL.
'This code assumes any effected subquery will start with " [SELECT ".
'This reverts SQL to correct format and loses ';' at end if requested.
' 11/11/2004    Updated to detect second problem and to use InStr
Public Function GetSQL(strQuery As String, _
                       Optional blnLoseSC As Boolean = True) As String
    Dim intDepth As Integer
    Dim lngLeft As Long, lngOpen As Long, lngRight As Long, lngAdjust As Long
 
    'If param passed is already SQL then leave as is - Otherwise get from query
    If Left(strQuery, 1) = "(" Then
        GetSQL = strQuery
    Else
        On Error Resume Next
        GetSQL = CurrentDb.QueryDefs(strQuery).SQL
    End If
    lngLeft = -7&
    Do
        'Check for corrupted subquery
        lngLeft = InStr(lngLeft + 8&, GetSQL, " [SELECT ", vbTextCompare)
        If lngLeft = 0& Then Exit Do
        'To find end correctly we must treat '[' & ']' as matched pairs
        intDepth = 1
        lngRight = lngLeft + 8&
        lngOpen = -lngRight
        Do
            'Find next ']'
            lngRight = InStr(lngRight + 1&, GetSQL, "]", vbBinaryCompare)
            If lngRight = 0& Then
                GetSQL = ""
                Exit Function
            End If
            intDepth = intDepth - 1
            Do
                'For lngOpen negative numbers mean that item has been counted
                'If already counted get next one - Otherwise drop through
                If lngOpen < 0& Then _
                    lngOpen = InStr(-lngOpen + 1&, GetSQL, "[", vbBinaryCompare)
                'we're only interested (now) if it found one BEFORE the ']'
                If lngOpen > lngRight Or lngOpen = 0& Then Exit Do
                intDepth = intDepth + 1
                lngOpen = -lngOpen
            Loop
        Loop While intDepth > 0
        'If '].' found then be sure to drop the '.' too
        lngAdjust = IIf(Mid(GetSQL, lngRight + 1&, 1) = ".", 1&, 0&)
        GetSQL = Left(GetSQL, lngLeft) & "(" & _
                 Mid(GetSQL, lngLeft + 2&, lngRight - lngLeft - 2&) & ")" & _
                 Right(GetSQL, Len(GetSQL) - (lngRight + lngAdjust))
    Loop
    'Lose ";" at end if requested and it exists
    If blnLoseSC And Right(GetSQL, 3) = ";" & vbCrLf Then _
        GetSQL = Left(GetSQL, Len(GetSQL) - 3)
End Function

'ChangeWhere changes the WHERE clause (in strSQL) for the field found in
'strWhere to match the criteria specified by varFrom & varTo.
'20/1/2004  Added strTemp to restart search in right place.
Public Sub ChangeWhere(strSQL As String, strWhere As String, _
                       strType As String, strCmp As String, _
                       Optional ByVal varFrom As Variant = Null, _
                       Optional ByVal varTo As Variant = Null)
    Dim intLeft As Integer, intRight As Integer
    Dim strAND As String, strTemp As String
 
    intRight = 1
    Do
        intLeft = InStr(intRight, strSQL, strWhere, vbTextCompare) - 1
        If intLeft > 0 Then
            intRight = GetRight(strSQL, intLeft, strAND)
            If strAND = "L" Then intLeft = intLeft - 5
        Else
            If intRight > 1 Then Exit Do
            intLeft = InStr(1, strSQL, "WHERE (", vbTextCompare) + 6
            If intLeft < 7 Then Exit Do
            intRight = GetRight(strSQL, intLeft - 1, strAND) - 1
            If intLeft < (intRight - 1) Then
                intLeft = intRight - 1
                strAND = "L"
            End If
        End If
        strTemp = NewWhere(strWhere, strType, strAND, strCmp, varFrom, varTo)
        strSQL = Left(strSQL, intLeft) & strTemp & Mid(strSQL, intRight)
        intRight = intLeft + Len(strTemp) + 1
    Loop
End Sub

'GetRight finds the rightmost position of the current field (where intLeft is
'pointing) in the strSQL WHERE clause.
Public Function GetRight(strSQL As String, ByVal intLeft As Integer, _
                         ByRef strAND As String) As Integer
    Dim intParenth As Integer, intClose As Integer
 
    strAND = IIf(intLeft > 5 And _
                 Mid(strSQL, intLeft - 4, 5) = " AND ", "L", "N")
    Do
        intClose = InStr(intLeft + 1, strSQL, ")", vbBinaryCompare)
        intParenth = intParenth - 1
        Do
            intLeft = InStr(intLeft + 1, strSQL, "(", vbBinaryCompare)
            Select Case intLeft
                Case 0, Is > intClose
                    intLeft = intClose
                Case Else
                    intParenth = intParenth + 1
            End Select
        Loop While intLeft < intClose
    Loop Until intParenth <= 0
    GetRight = intClose + 1
    If strAND = "N" And Mid(strSQL, GetRight, 5) = " AND " Then
        strAND = "R"
        GetRight = GetRight + 5
    End If
End Function

'NewWhere formulates a new section of a WHERE clause for strField.
'15/3/2005  Added support for the 'Like' comparison.
Public Function NewWhere(strField As String, strType As String, _
                         strAND As String, strCmp As String, _
                         Optional ByVal varFrom As Variant = Null, _
                         Optional ByVal varTo As Variant = Null) As String
    If IsNull(varFrom) Then
        If IsNull(varTo) Then
            NewWhere = ""
            Exit Function
        End If
        varFrom = varTo
        varTo = Null
    End If
    If strCmp = "Not" Then strCmp = "<>"
    If Not IsNull(varTo) _
    And StrComp(varFrom, varTo, vbTextCompare) <> 0 Then
        If strCmp = "=" Then strCmp = "Between"
        If strCmp = "<>" Then strCmp = "Not Between"
    End If
    If VarType(varFrom) = vbDate Then _
        varFrom = SQLDate(varDate:=varFrom, blnHash:=False)
    If VarType(varTo) = vbDate Then _
        varTo = SQLDate(varDate:=varTo, blnHash:=False)
    Select Case strCmp
        Case "=", "<", "<=", ">", ">=", "<>"
            NewWhere = strField & strCmp & strType & varFrom & strType & ")"
        Case "In", "Not In"
            NewWhere = strField & " " & strCmp & "(" & varFrom & "))"
        Case "Between", "Not Between"
            NewWhere = strField & " " & strCmp & " " & _
                       strType & varFrom & strType & " And " & _
                       strType & varTo & strType & ")"
        Case "Like"
            NewWhere = strField & " Like '" & varFrom & "')"
    End Select
    NewWhere = IIf(strAND = "L", " AND ", "") & _
               NewWhere & _
               IIf(strAND = "R", " AND ", "")
End Function

[Denburt]

Thanks for the routines i haven't had a chance to read through them all just yet I swear some of it is a mirror image to what I have, at least in some ways. I have about 4 projects I am working (at this moment) so it's going to be a busy day. When I can I will try and implement the routines you have so graciously provided and let you know how things go. Thanks again

[NeoPa]

If you feel you can work with them sure Den. Otherwise just cherry-pick the bits you think will help.

It's no big deal, I've had to develop them myself for my own uses, and they've served me well. I'm happy for anyone to use them.

I have more for other things. If you ever fancy looking through any just ask me. Specifically or generally, whatever works for you.