SQL Troubles

[dccjr]

I am trying to create a query to count the records between for the past 3 months. This is what I have so far:

"SELECT Format(TBDate,'mmmm') as Month, COUNT (TBDate) as cnt INTO ChartTable FROM Turnbacks WHERE TBDate BETWEEN DateAdd(""m"", -3, Date()) AND Date();"

I am gettting:

Syntax error (missing operator) in query expression 'TBDate BETWEEN DateAdd(""mmmm"", -3, Date()) AND Date()'.



I am just not sure what I am missing. This is the first time I have tried building an SQL statement this "complex".

[pbaldy]

Try replacing all the quotes around the m with single quotes.

[dccjr]

Tried the suggested change, and now I am getting:

You tried to execute a query that does not include the specified expression 'Format(TBDate,'mmmm')' as part of the aggregate function.

I am not even sure what this means. What I am trying to do is query all of the records that are for the past 3 months into a table and count them by month. I need the month text in one column and the count in the other. maybe I am going about this all wrong, Suggestions?

[pbaldy]

Normally a totals query requires that any field not aggregated (sum, count, etc) must be in a GROUP BY clause. Try adding this to the end:

GROUP BY Format(TBDate,'mmmm')

[dccjr]

Thank you so much. That was exactly what I needed.

[pbaldy]

No problem.