How to Pass this parameter to this type of query - I'm struggling

[Lateral]

Hi guys

I have the following query that displays the selected data as a Pivotchart.

DoCmd.OpenQuery "qPaymentsQueryJustMonth", acViewPivotChart

I action the about via an VBA Event.

This all works fine

The graph displays the "Sum of Payments Amount" on a monthly graph just for all the months of January for every year.

I want to use the same query but pass the month number (eg: 2,3,4,5,6,7,8,9,10,11,12)

I have tried to figure this out but can't.

Dim sWhere As String

sWhere = "[PaymentDate] = "DatePart("m",[PaymentDate]) = 1"


DoCmd.OpenQuery "qPaymentsQueryJustMonth", acViewPivotChart


Any help would be greatly appreciated.

Cheers
Greg

 

[arnelgp]

change the last Column:

Field: DatePart("m", [PaymentDate])
Total: Expression
Criteria: [p1]

''''''''''
now, before opening your query, supply the value of [p1]:

With DoCmd
    .SetParameter "p1", 1    'change 1 with the month number
    .OpenQuery "qPaymentsQueryJustMonth", acViewPivotChart
End With

 

[Lateral]

Thanks for the quick reply but I'm getting the following error when I compile:

Also, I'm using Access 2007.

 

[arnelgp]

oopps, sorry i am using higher version.
can you copy (and paste) this in a Module:

Public Function fnQueryParameter(ByVal strQuery As String, ByVal strParam As String, ByVal varParamValue As Variant)
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strSQL As String
    Set db = CurrentDb
    On Error Resume Next
    Set qd = db.QueryDefs("zt_" & strQuery)
    If Err Then
        Err.Clear
        DoCmd.CopyObject , "zt_" & strQuery, acQuery, strQuery
        db.QueryDefs.Refresh
        Application.RefreshDatabaseWindow
    End If
    qd.Close
    Set qd = Nothing
    strSQL = db.QueryDefs("zt_" & strQuery).SQL
    If InStr(1, strSQL, "[" & strParam & "]") <> 0 Then
        strSQL = Replace$(strSQL, "[" & strParam & "]", varParamValue)
    Else
        strSQL = Replace$(strSQL, strParam, varParamValue)
    End If
    Set qd = db.QueryDefs(strQuery)
    qd.SQL = strSQL
    qd.Close
    Set qd = Nothing
    Set db = Nothing
End Function

your program would now look like this:

    Call fnQueryParameter("qPaymentsQueryJustMonth", "p1", 1)    'change 1 with the month number
    DoCmd.OpenQuery "qPaymentsQueryJustMonth", acViewPivotChart

 

[Lateral]

oopps, sorry i am using higher version.
can you copy (and paste) this in a Module:

Public Function fnQueryParameter(ByVal strQuery As String, ByVal strParam As String, ByVal varParamValue As Variant)
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strSQL As String
    Set db = CurrentDb
    On Error Resume Next
    Set qd = db.QueryDefs("zt_" & strQuery)
    If Err Then
        Err.Clear
        DoCmd.CopyObject , "zt_" & strQuery, acQuery, strQuery
        db.QueryDefs.Refresh
        Application.RefreshDatabaseWindow
    End If
    qd.Close
    Set qd = Nothing
    strSQL = db.QueryDefs("zt_" & strQuery).SQL
    If InStr(1, strSQL, "[" & strParam & "]") <> 0 Then
        strSQL = Replace$(strSQL, "[" & strParam & "]", varParamValue)
    Else
        strSQL = Replace$(strSQL, strParam, varParamValue)
    End If
    Set qd = db.QueryDefs(strQuery)
    qd.SQL = strSQL
    qd.Close
    Set qd = Nothing
    Set db = Nothing
End Function

your program would now look like this:

    Call fnQueryParameter("qPaymentsQueryJustMonth", "p1", 1)    'change 1 with the month number
    DoCmd.OpenQuery "qPaymentsQueryJustMonth", acViewPivotChart

Thanks mate!

It works great!

I assume that SetParamter is not available in Access 2007 and this is a module that provides exactly the same functionality.

Thanks again!

Cheers
Greg

 

[Gasman]

@arnelgp
Now I am curious.? I have seen you post many times code like

P1= VariableName
P2 = VariableName

then run the query with those parameters?

Why did you not use that method this time please?

 

[arnelgp]

There is no .SetParameter method on DoCmd object in a2007

 

[Gasman]

There is no .SetParameter method on DoCmd object in a2007

I have 2007, remember.

I meant like in post #2 of your here? https://www.access-programmers.co.u...few-parameters-expected1.314177/#post-1724229

 

[arnelgp]

The op want to Run the query (pivot). It is a select query and not an action query.

 

[Gasman]

The op want to Run the query (pivot). It is a select query and not an action query.

Ah, OK, thank you