Prev: Access 2007 and Outlook 2007 integration
Next: Memo Box Resize in Form View
From: AccessKay on 6 Apr 2010 12:16
Hi,

I created a query that pulls variances. I have parameters set-up for:

Enter Current Month
Enter Current Year
Enter Previous Month
Enter Previous Year

I want to put this criteria in a form command button to preview a report.
This is my SQL from the query:

SELECT [UNIONQRY-ODC].Category, [UNIONQRY-ODC].Group,
[UNIONQRY-ODC].Product, [UNIONQRY-ODC].PA_Descriptions,
[UNIONQRY-ODC].Credit_GL_Acct, Sum(IIf([Mnth]=1,[ODC_Cost],0)) AS ODCCurrent,
Sum(IIf([Mnth]=2,[ODC_Cost],0)) AS ODCPrevious, [ODCCurrent]-[ODCPrevious] AS
ODCDelta
FROM [UNIONQRY-ODC]
GROUP BY [UNIONQRY-ODC].Category, [UNIONQRY-ODC].Group,
[UNIONQRY-ODC].Product, [UNIONQRY-ODC].PA_Descriptions,
[UNIONQRY-ODC].Credit_GL_Acct
HAVING ((([UNIONQRY-ODC].Group)="ODC"));

This is the VBA that opens the report from my command button:
Private Sub VarianceRptODC_Click()

DoCmd.OpenReport "UNIONQRY-ODCRPT", acViewPreview, "", "", acNormal

End Sub

This is my feeble attempt to write the vba where I'm obviously missing a lot:

Private Sub VarianceRptODC_Click()
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long

strReport = "UNIONQRY-ODCRPT"
strDateField = "[Mnth]"
lngView = acViewPreview


DoCmd.OpenReport "UNIONQRY-ODCRPT", acViewPreview, "", "", acNormal

End Sub

Can anyone please help me out with writing this vba code? If I can do it
without the code then even better. And if I have not provided enough
information please let me know.

Many thanks,
Kay

 | 
Pages: 1
Prev: Access 2007 and Outlook 2007 integration
Next: Memo Box Resize in Form View