Replace parameter in SQL query

Question

I want to replace Query1.Period parameter "May 2018" with "Jun 2018".

SQL:

SELECT DISTINCTROW Query1.Period, Query1.Portfolio_1
FROM Query1
WHERE (((Query1.Period)="May 2018"))
ORDER BY Query1.Portfolio_2, Query1.Department, Query1.Position_NO;

VBA code attempt:

Private Sub newChangePeriodCriteria_Click()
Dim qdf As DAO.QueryDef
Dim qdfOLD As String
Set qdf = CurrentDb.QueryDefs("Query1_Mth")
With qdf
    qdf.SQL = sqlString
    
    .SQL = Replace(.SQL, "Period='May 2018'", "Period='Jun 2018'")
    ' Code to do stuff with SQL-string/query
    ' .SQL = qdfOLD ' Reset SQL to old setting
    qdfOLD = .SQL
      
    ' DoCmd.RunSQL (Replace("Period='May 2018'", "Period='Jun 2018'"))
End With
Set qdf = Nothing
End Sub

Possible duplicate of [Is it possible to pass parameters programmatically in a Microsoft Access update query?](https://stackoverflow.com/questions/16568461/is-it-possible-to-pass-parameters-programmatically-in-a-microsoft-access-update) — ashleedawg, Aug 26 '18 at 03:09

Wolfgang Kais · Answer 1 · 2018-08-27T13:37:13.967

0

Your code sets the SQL string of the query to a string from a variable sqlString (where does it come from?), then replaces "Period='May 2018'" by "Period='Jun 2018'" in the SQL of the query, then saves the final SQL in a variable qdfOld (which isn't used and which is a string variable, so the prefix qdf is misleading). The string you are trying to find and replace is not contained in your query, because in the query, Query1.Period is enclosed in brackets.

Supposed that the variable sqlString contains the original query string, you most likely wanted to do something like this:

Private Sub newChangePeriodCriteria_Click()

    With CurrentDb
        With .QueryDefs("Query1_Mth")
            .SQL = Replace(sqlString, "(Query1.Period)='May 2018'", "(Query1.Period)='Jun 2018'")
            .Close
        End With
    End With

End Sub

This is quite unusual, please take a look at query parameters (and the link that @ahleedawg provided).

Added:

Instead of opening the query in datasheet view, you can open a form in datasheet view. This will allow you to pass a WHERE clause.

Create a form ("PeriodsForm") based on the query Query1 and use something like the following code to open it:

Sub OpenPeriodForm()

    DoCmd.OpenForm FormName:="PeriodsForm", View:=acFormDS, WhereCondition:="Period='Jul 2018'"

End Sub

edited Aug 27 '18 at 13:37

answered Aug 26 '18 at 13:34

Wolfgang Kais

4,010
2
10
17

How can i change Period parameter 'Jul 2018' in VBA code as follows '**Private Sub PeriodChange_Click() Dim db As dao.Database Dim qdef As dao.QueryDef Dim strSQL As String Dim StrMth As String Set db = CurrentDb strSQL = "SELECT Query_Table_Period.Period FROM Query_Table_Period WHERE (Query_Table_Period.Period)= 'Jul 2018' " Set qdef = db.QueryDefs("Period") qdef.SQL = strSQL qdef.Close Set qdef = Nothing Set db = Nothing 'DoCmd.OpenQuery "Period", acViewNormal End Sub**' – SAM HO Aug 26 '18 at 21:28
Why would you open a query? Open a form instead. I'll add an example to my answer. – Wolfgang Kais Aug 26 '18 at 23:03
The problem arises that if I try to execute a query with criteria to a form control, it always result in an error ** “Too Few Parameters“** when trying run VBA to print report/export to PDF. It works fine if I do not execute query with criteria to a form Control. so I am looking for solution to change only Period parameter criteria through VBA in the SQL part in VBA code. – SAM HO Aug 27 '18 at 12:46
The problem is that you add more and more details after each comment you got. Aside from that I can't see anything useful in exporting a query result or datasheet to a PDF: Why don't you export the filtered form? If you insist to change the query, check my first code sample. – Wolfgang Kais Aug 27 '18 at 13:52

Replace parameter in SQL query

1 Answers1