Filter a Report by month and year (1 Viewer)

andy1968

Registered User.
Local time
Today, 02:25
Joined
May 9, 2018
Messages
131
I'm having trouble getting my filter to work.

I'm trying to get a report to filter the month and year from a date. I have fields for the month and the year on the report, x and y.

I'm extracting the month and year from the date field in the form.

I get a syntax erro on the code, and think I'm missing a quote of something:

Code:
dYear = DatePart("yyyy", Me.DateOfReport)
dMonth = Month(Me.DateOfReport)

DoCmd.OpenReport "rptMonthlyReportFlagging", acViewPreview, ,_
"x =" & dMonth And "y =" & dYear
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:25
Joined
Oct 29, 2018
Messages
21,357
Are x and y date fields in your table? If not, what are their data types? If Text, try using Format() instead of DatePart().

Or, try it this way:
Code:
DoCmd.OpenReport "rptMonthlyReportFlagging", acViewPreview, , "x ='" & dMonth & "' And y ='" & dYear & "'"

PS. If x & y are Numbers, try it this way:
Code:
DoCmd.OpenReport "rptMonthlyReportFlagging", acViewPreview, ,"x =" & dMonth & " And y =" & dYear
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:25
Joined
Sep 21, 2011
Messages
14,037
If you reaaly have x and y in the source

Code:
"x =" & dMonth  & " And "y =" & dYear
 

namliam

The Mailman - AWF VIP
Local time
Today, 10:25
Joined
Aug 11, 2003
Messages
11,696
DoCmd.OpenReport "rptMonthlyReportFlagging", acViewPreview, , "x ='" & dMonth And "' y ='" & dYear & "'"
Seems to be missing a little something ...
DoCmd.OpenReport "rptMonthlyReportFlagging", acViewPreview, , "x ='" & dMonth & "' And y ='" & dYear & "'"
 

andy1968

Registered User.
Local time
Today, 02:25
Joined
May 9, 2018
Messages
131
Are x and y date fields in your table? If not, what are their data types? If Text, try using Format() instead of DatePart().

Or, try it this way:
Code:
DoCmd.OpenReport "rptMonthlyReportFlagging", acViewPreview, , "x ='" & dMonth & "' And y ='" & dYear & "'"

PS. If x & y are Numbers, try it this way:
Code:
DoCmd.OpenReport "rptMonthlyReportFlagging", acViewPreview, ,"x =" & dMonth & " And y =" & dYear
The "x ='" & dMonth & "' And y ='" & dYear & "'" worked like a charm.

The x and y in the report extract the number of the month and the year from a date field as well.

Thanks as always for your help
 

IbrBadri

Member
Local time
Today, 11:25
Joined
May 24, 2020
Messages
35
Are x and y date fields in your table? If not, what are their data types? If Text, try using Format() instead of DatePart().

Or, try it this way:
Code:
DoCmd.OpenReport "rptMonthlyReportFlagging", acViewPreview, , "x ='" & dMonth & "' And y ='" & dYear & "'"

PS. If x & y are Numbers, try it this way:
Code:
DoCmd.OpenReport "rptMonthlyReportFlagging", acViewPreview, ,"x =" & dMonth & " And y =" & dYear
The same idea,

Try format:

dYear = Format(me.DateOfReport,"YYYY")
dMonth = Format(me.DateOfReport,"MM")
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:25
Joined
Oct 29, 2018
Messages
21,357
The "x ='" & dMonth & "' And y ='" & dYear & "'" worked like a charm.

The x and y in the report extract the number of the month and the year from a date field as well.

Thanks as always for your help
Hi. Glad to hear you got it to work. Good luck with your project.
 

Users who are viewing this thread

Top Bottom