How to Pass this parameter to this type of query - I'm struggling (1 Viewer)

Lateral

Registered User.
Local time
Today, 06:13
Joined
Aug 28, 2013
Messages
388
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.

1625707472877.png


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

..forever waiting... waiting for jellybean!
Local time
Today, 21:13
Joined
May 7, 2009
Messages
19,175
change the last Column:

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

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

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

Lateral

Registered User.
Local time
Today, 06:13
Joined
Aug 28, 2013
Messages
388
Thanks for the quick reply but I'm getting the following error when I compile:

1625710830096.png



Also, I'm using Access 2007.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:13
Joined
May 7, 2009
Messages
19,175
oopps, sorry i am using higher version.
can you copy (and paste) this in a Module:

Code:
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:
Code:
    Call fnQueryParameter("qPaymentsQueryJustMonth", "p1", 1)    'change 1 with the month number
    DoCmd.OpenQuery "qPaymentsQueryJustMonth", acViewPivotChart
 

Lateral

Registered User.
Local time
Today, 06:13
Joined
Aug 28, 2013
Messages
388
oopps, sorry i am using higher version.
can you copy (and paste) this in a Module:

Code:
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:
Code:
    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

Enthusiastic Amateur
Local time
Today, 13:13
Joined
Sep 21, 2011
Messages
14,052
@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

..forever waiting... waiting for jellybean!
Local time
Today, 21:13
Joined
May 7, 2009
Messages
19,175
There is no .SetParameter method on DoCmd object in a2007
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:13
Joined
May 7, 2009
Messages
19,175
The op want to Run the query (pivot). It is a select query and not an action query.
 

Users who are viewing this thread

Top Bottom