formatting a text box control for use in query criteria? (1 Viewer)

[SirStevie3]

I have a totals query that provides an avg for each month. i'd like to be able to use a text box control (named "Date") on a form (named "Report Runner") to show only a certain month and it's avg.

I tried using this as criteria on the "MonthGroupPMC" field:

Format([Forms]![Report Runner]![Date], "yyyy-mm")

but the results came up blank.

how can i filter the results of this query to show only one month, specified by the [Forms]![Report Runner]![Date] control?

 

[pr2-eugin]

Try this WHERE to your SQL.

WHERE Month(Date_Sent) = Month([Forms]![Report Runner]![Date]);

 

[SirStevie3]

got an error (attached screenshot)

tried making the Format(Date_Sent,"yyyy-mm") match your Month(Date_Sent) but got the same error...

 

[Mihail]

Not for sure but I suspect that the "Date" name cause the troubles.
The "Date" is a reserved word. So, Access expect some arguments after this (reserved) word that you use in the SQL string.
Try to change all names in something like StartDate or EndDate etc.

 

[SirStevie3]

tried changing it to Date1 and Text63 but neither worked.

what is a syntax error anyway?

 

[SirStevie3]

guess i had the SQL statements in the wrong order.

I got it!! thank you guys!