.SQL Replace (1 Viewer)

dj59 · Apr 15, 2015

I have a make table query in MS Access.
I want a variable in this query to change based on an array value.
For some reason the .SQL Replace statement is not changing the value, even though when I debug and hover over the "x" array value it is there.

My code is:

Code:

        Set qdf = CurrentDb.QueryDefs("i_qry_446_1")
        With qdf
 
            qdfOLD = .SQL
        Debug.Print .SQL
            .SQL = Replace(.SQL, "(STPID.CNTY_CD)='01'", "(STPID.CNTY_CD)= " & "'" & x & "'")
        Debug.Print .SQL
                'create the table
                DoCmd.SetWarnings False
                DoCmd.OpenQuery "i_qry_446_1"
                DoCmd.Close
                DoCmd.SetWarnings True
                DoCmd.OpenQuery "i_qry_446"
                DoCmd.Close
                'export report 446
              .SQL = qdfOLD ' Reset SQL to old setting
        End With '446

If it would help to see my entire sql statement let me know.
If you have any suggestions to change how this is written let me know.

Thanks

GohDiamond · Apr 15, 2015

Is the EXACT substring "(STPID.CNTY_CD)='01'", including the spacing and punctuation, present in your QueryDefs("i_qry_446_1")?

If there is a a space on the Left and/or Right side of your "=" sign in the original string expression .SQL then it may not be matching the substring, therefore not replacing it.

Just a thought,
Cheers
Goh

dj59 · Apr 15, 2015

Yes, I was particular about that. I copied it right out of the sql statement.

I have the criteria of "01" in the design view of the query, would that matter? I would not think so, but I am at a loss on why this doesn't work.

MarkK · Apr 15, 2015

What's happening is that you open the query here, and assign it to a variable . . .

Code:

        Set qdf = CurrentDb.QueryDefs("i_qry_446_1")

. . . then you edit the SQL of that query, located at qdf . . .

Code:

            (qdf).SQL = Replace(.SQL, "(STPID.CNTY_CD)='01'", "(STPID.CNTY_CD)= " & "'" & x & "'")

. . . but when you do this . . .

Code:

                DoCmd.OpenQuery "i_qry_446_1"

. . . you are not using the edit you did to qdf, you are opening a clean copy of the query as hosted by the DoCmd.OpenQuery function.

Here's an example of the approach I would take with a parameterized query, which, let's say, is called Query1 and has SQL like . . .

Code:

INSERT INTO tblTable ( Field1 )
VALUES ( p0 )

Then, similar to the code you have, open the query, but rather than edit the SQL directly, we just assign a value to the parameter "p0", and run the Execute method . . .

Code:

with CurrentDb.QueryDefs("Query1")
   .parameters("p0") = x
   .execute
end with

. . . so your completed code could look like . . .

Code:

Private Sub Test12938497()
[COLOR="Green"]    'edit your base query to "(STPID.CNTY_CD) = prm0 "[/COLOR]
    With CurrentDb
        With .QueryDefs("i_qry_446_1")
            .Parameters("prm0") = x
            .Execute
        End With
    
        .QueryDefs("i_qry_446").Execute
    End With
End Sub

Hope this helps,

dj59 · Apr 16, 2015

This code looks great.
I'm trying to use it, but I keep getting the error "3625 - Item not found in this collection". I have the parameter value set as "01", not "prmo", does that matter? What am I not seeing?

My code:

Code:

      Set qdf = CurrentDb.QueryDefs("i_qry_446_1")
    'edit your base query to "(STPID.CNTY_CD) = prm0 "
    With CurrentDb
        With .QueryDefs("i_qry_446_1")
            .Parameters("01") = x
            .Execute
        End With
    
        .QueryDefs("i_qry_446").Execute
    End With

MarkK · Apr 16, 2015

If you have a number, or a delimited string, in your SQL, the query parser will not recognize it as a parameter, so you must edit your query for this approach to work.

dj59 · Apr 16, 2015

Thank You All !
This is all good information.
In the end....this is what worked.

I had to use a ! instead of . in my .SQL replace statement.
I had to change my base sql query to use to single (') quotes, rather than double quotes (").

The new line looks like this:

Code:

.SQL = Replace(.SQL, "(STPID!CNTY_CD)='01'", "(STPID!CNTY_CD)= " & "'" & x & "'")

.SQL Replace (1 Viewer)

dj59

Registered User.

GohDiamond

"Access- Imagineer that!"

dj59

Registered User.

MarkK

bit cruncher

dj59

Registered User.

MarkK

bit cruncher

dj59

Registered User.

Similar threads

Users who are viewing this thread