Sorting query produced by .sql= breaks it

[krausr79]

We made a nifty page that can construct a query for users by selecting checkboxes and entering info into fields on a form. Then VBA code builds it into an sql statement, assigns it to the .sql property of our universal query, and opens the query.

It works nicely until they try to sort on one of the fields, then everything says #Name?

What's wrong?

 

[krausr79]

The query built draws data from one of two places: a linked table in the _be and a query which draws data from our mainframe. Some testing suggests the failure only happens when the mainframe query data is involved.

Here's a couple constructed queries:
SELECT [qry_TR_All Truck Info].[Variant], [qry_TR_All Truck Info].[WBDOCO]
FROM [qry_TR_All Truck Info];

SELECT [New Register].[Branch], [qry_TR_All Truck Info].[Variant], [qry_TR_All Truck Info].[WBDOCO]
FROM [New Register] RIGHT JOIN [qry_TR_All Truck Info] ON [New Register].[JDE WO#] = [qry_TR_All Truck Info].WBDOCO;


Here's the build code:

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Status_Criteria")
SQLstring = "select"
While Not rs.EOF
    If rs![Include] Then
        'build select statement, decide which tables are used
        If rs![ID] > 41 Then
            SQLstring = SQLstring & " [qry_TR_All Truck Info].[" & rs![Column] & "], "
            FromJDE = True
        Else
            SQLstring = SQLstring & " [New Register].[" & rs![Column] & "], "
            FromRecords = True
        End If
    End If
    'build the where statement
    If rs![SearchFor] <> "" Then
        If rs![ID] > 41 Then
            SQLwhere = SQLwhere & " ucase([qry_TR_All Truck Info].[" & rs![Column] & "]) like '*" & UCase(rs![SearchFor]) & "*' and"
        Else
            SQLwhere = SQLwhere & " ucase([New Register].[" & rs![Column] & "]) like '*" & UCase(rs![SearchFor]) & "*' and"
        End If
    End If
rs.MoveNext
Wend
rs.Close
'drop last comma
SQLstring = Left(SQLstring, Len(SQLstring) - 2)
'add froms
If FromRecords Then
    If FromJDE Then
        SQLstring = SQLstring & " FROM [New Register] RIGHT JOIN [qry_TR_All Truck Info] ON [New Register].[JDE WO#] = [qry_TR_All Truck Info].WBDOCO"
    Else
        SQLstring = SQLstring & " FROM [New Register]" 'just register
    End If
Else
    If FromJDE Then
        SQLstring = SQLstring & " FROM [qry_TR_All Truck Info]" 'just jde
    Else
        BuildSQL = ""
        Exit Function 'nothing selected??
    End If
End If
'add where statement
If Len(SQLwhere) > 0 Then
    SQLwhere = Left(SQLwhere, Len(SQLwhere) - 3) 'last comma off
    SQLstring = SQLstring & " where " & SQLwhere
End If

 

[krausr79]

So how is the Order By being applied to this?

By the end user in the query output screen. They use the drop-down filters on the column header, and then the #NAMES?s show up.

 

[krausr79]

It was not the sql statement. It was trying to mix a snapshot query and a dynaset table into a dynaset query result.