Filter a Report by month and year

[andy1968]

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:

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

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

 

[theDBguy]

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:

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

PS. If x & y are Numbers, try it this way:

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

 

[Gasman]

If you reaaly have x and y in the source

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

 

[namliam]

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]

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:

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

PS. If x & y are Numbers, try it this way:

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]

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:

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

PS. If x & y are Numbers, try it this way:

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]

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.