calculate monthly trend from sql query

You want the months to be grouped, so you need to create an alias for the group.
I use something similar in a query I have which joins 3 months worth of data together:

In the query, you need to have something like this:
5MnthCum: IIf(Month Between DateSerial(Year([Month1]), Month([Month1])-5,1) and DateSerial(Year([Month1]), Month([Month1])+1,0),1,0)
and
CurMnth: IIf(Month Between DateSerial(Year([Month1]), Month([Month1]),1) and DateSerial(Year([Month1]), Month([Month1])+1,0),1,0)

You should then be able to do calculations on those figures.

hi harfang!

thanks for the immediate reply. I tried this code (below) and was given an error, access cant find the field 'txtThisMonth' referred to in your expression.

Private Sub cmdSubmit_Click()

Dim txtThisMonth As String

txtThisMonth = CDate(cboYear & " " & cboMonth)

DoCmd.OpenQuery "qryTest"

End Sub

What I meant: Your query needs to convert cboYear and cboMonth to a date several times over. This makes the query hard to read and maintain, and a bit slower. Instead, open your form frmMerchants, create a new text box, and type this expression as control source:

    = CDate(cboYear & ' ' & cboMonth)

Now, you query can use txtThisMonth as date parameter directly, no need to perform the same conversion twice on every other line. The query I posted used that single parameter.

You tried to create a VB variable, not a control, and variables aren't visible from queries.

(°v°)

harfang,

It's giving me an error "3270, property not found. i tried to fix the apostrophes and everything and double-checked it. Seems stuck. Any suggestions?

PARAMETERS [Forms]![frmMerchants]![txtThisMonth] DateTime;
TRANSFORM Sum(tblMSalesComplete.daa_total_gms) AS SumOfdaa_total_gms
SELECT tblMSalesComplete.friendlyname AS Merchants, Sum(tblMSalesComplete.daa_total_gms) AS [Total GMS]
FROM tblMSalesComplete
WHERE [dates] Between DateAdd("m",-4,Forms!frmMerchants!txtThisMonth) And Forms!frmMerchants!txtThisMonth
GROUP BY tblMSalesComplete.friendlyname
PIVOT 'mm'+DateDiff("m",tblMSalesComplete.[dates],Forms!frmMerchants!txtThisMonth) In ("mm4","mm3","mm2","mm1","mm0");

Hmm. The only issue I see is that the parameter isn't *exactly* the same in the declaration and in the WHERE section. Try to copy-paste, so that you have everywhere:

    [Forms]![frmMerchants]![txtThisMonth]

Then again, "property not found" looks like a VB error message. Do you get it when you try to open your cross-tab query from the interface, or when using a button? For example, the click event should now be reduced to:

Private Sub cmdSubmit_Click()
    DoCmd.OpenQuery "qryTest"   ' <--- name of your lastest test
End Sub

(°v°)

hi harfang!

this project has been moved til next month because i have to deal with another one right now. I'll close this one for now and just give you the points. thanks so much for the help Sir!