Add SQL to saved querydef

[48Highlander]

I need to add "filterdesc" to the WHERE clause of an existing query. The code I have so far is giving me a 'type mismatch' error on the open recordset statement. "strqueryname" is a string variable containing the name of the saved query.

    Dim db As Database
    Dim rs As Recordset
    Dim nbr As Integer
    Dim strNumber As String
    Dim tempQuery As DAO.QueryDef
    Dim strSQL As String

    Set db = CurrentDb
    Set tempQuery = db.QueryDefs("strqueryname")
        
    If Me.cboReportSelected = 41 Then
        strSQL = tempQuery.SQL
        strSQL = strSQL & " AND " & FilterDesc
        tempQuery.SQL = strSQL
     Else
        strSQL = tempQuery.SQL
        tempQuery.SQL = strSQL
    End If
    
    Set rs = db.OpenRecordset(tempQuery, dbOpenDynaset)

What am I doing wrong?

Bill J

 

[dhookom]

Run the code and then view the query "strqueryname" in SQL view. You could also use

Msgbox tempQuery.SQL 
' or
Debug.Print tempQuery.SQL

This should provide a view or your after sql. I expect you might have to move the semi-colon to the end of the SQL statement or are having trouble with ORDER BY or GROUP BY in the SQL.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[48Highlander]

Thanks Duane. You were right about the semi-colon. However, I also found out that when you extract the SQL statement from the querydef using this method there are two hidden characters on each end of the statement that need to be extracted. I guessed that this might be the case so I took 2 characters off the each end of the SQL statement and then adjusted for the semi-colon and voila! it works.

Has anybody got an explanation or seen any documentation of this?

<Very frustrated programmer>

Bill J

 

[48Highlander]

Oops! On further testing, I found that the two hidden characters are only on the right hand side. I have to take three characters off in order to move the semi-colon.

Bill J

 

[dhookom]

The characters are probably Chr(13) and Chr(10) which are carriage return and line feed.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

A probably safer way:
strSQL = Left(strSQL, InStrRev(strSQL, ";") - 1) & " AND " & FilterDesc

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886