.SQL Replace problem

[dj59]

In MS Access 2013, I have a make table query with a myField criteria of "01".


In VBA, I have an array of values that I loop through and want to replace value "01", with the value of 'x'.
When I hover over the 'x' value in debug mode, I see the correct value, however in the immediate window (debug.print) it still reflects as "01".

Why is this not working? and/or what else could I use to make this work?

I am using the .SQL Replace code below.

        Set qdf = CurrentDb.QueryDefs("i_qry_446_1")
        With qdf
            qdfOLD = .SQL
        Debug.Print .SQL
            .SQL = Replace(.SQL, "(PID.CNTY_CD)='01'", "(PID.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

Thank you for any help.

[June7]

Not seeing Debug.Print in code. When is this invoked? What is 'it' that reflects 01?

I would not modify QueryDef nor run query object. I would execute the SQL action statement in VBA.

CurrentDb.Execute "sql statement here"

[dj59]

The Debug.Print is there (4th line and 6th line).
Can you be more detailed.
If I use CurrentDb.Execute "sql statement here", how does that change each time I loop through my array?

I need the value of the variable I called MyField to change each time it loops through my array.
This is why I chose to replace the value in the .sql.

[ssanfu]

What is the SQL of the query "i_qry_446_1"?

[Rawb]

I've run into a similar issue myself in modifying the SQL statement of a saved Query using VBA. The only way I was able to solve it was to .Close the QueryDef, which apparently forces Access to save your changes. Then, when I reopened it, everything would be correct.

You might try that and see if it works.

And if anyone else has a better solution, I'd love to know as well

[June7]

Sorry, see them now.

But not seeing any code that sets value of x. Not seeing loop. Is there more code?

I also would not use MAKE TABLE as this modifies database design. I do use 'temp' tables - tables are actually permanent (in the frontend) and records are temporary.

What is the sql of both query objects?

[dj59]

Rawb, I put the .Close in, but that isn't working either. Thanks for your help though! I'll keep looking and if I find the answer I'll post it.

June7, I did not post the entire code because it is a couple pages long. This was the "piece" of code that was not working.

Here is an example of another way to do this that I found, however I don't have this working either I'm not sure how change my base query.

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

[dj59]

Thank You everyone.
Here is what worked:
1. use the .Close to close QueryDef
2. changed my sql to use single quotes, rather than double quotes
3. changed my vba code above as follows (use a !, rather than . )

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

Thanks again!