syntax error - missing operator

[rschultz]

I'm getting "syntax error - missing operator" with the following code. I think the problem is in the GROUP BY part. I copied it out of the ROWSOURCE on a Chart and it had "MMM \'YY" but the double quotes won't work in a SELECT statement so I had to change them to single quotes which I think screwed up the single quote before YY. Yes? So how do I make this work? I think I'm close to making the chart work.
STRSOURCE = "SELECT [Bill-To DATE], [Therms] FROM [swg] " & _
"WHERE (([swg].[Bill-To DATE]) Between [Forms]![SWG Query]![startdate] " & _
" And [Forms]![SWG Query]![EndDate])" & _
" AND (([swg].Dept)=[Forms]![SWG Query]![combo17])" & _
"GROUP BY (Year([Bill-To Date])*12 + Month([Bill-To Date])-1),(Format([Bill-To Date],'MMM \'YY'))" & _
"ORDER BY ([swg].[Bill-To DATE])"

 

[simongallop]

Don't know if this answer works for your problem, but I found that it solved my apostrophe problem in strings in reports (creating HTML Code)

Create a function:

Function InsApos()
InsApos = "'"
End Function

Then place it in your string ie:
([Bill-To Date],'MMM \" & InsApos() & "YY'


HTH

 

[rschultz]

Harry: Thanks for the suggestion. I tried the double quotes:

(Format([Bill-To Date],'MMM \" & InsApos() & "YY'))

and I get the following error message:

"Syntax error, missing operator in query expression"

If I use single quotes:

(Format([Bill-To Date],'MMM \' & InsApos() & 'YY')

I get the following error message:

"You tried to execute a query that doesn't include the specific expression 'Bill-To Date' as part of an aggregate function.

So I'm still confused.

 

[Rich]

Try MMM"\"YY

 

[rschultz]

I took out the:

'MMM \" & InsApos() & "YY'

and put in:

MMM"\"YY

and I get "Type Mismatch" error.

thinking that I misunderstood,I tried:

MMM" \' & InsApos() & '"YY))"

and I get: Complie error, expected expression.

So do I still not understand what you meant?

 

[rschultz]

okay, I tried to get rid of all t he stuff I don't understand anyway and just tried:

Group By Month([Bill-To Date])

but I still get;

"You tried to execute a query that doesn't include the specific expression 'Bill-To Date' as part of an aggregate function."

Does that make it any easier to solve for anyone? I woiuld have thought that was breaking the problem down to it simplest form.

 

[Rich]

Is Bill To Date an actual Date field?

 

[rschultz]

>>Is Bill To Date an actual Date field?

yes

 

[rschultz]

okay, if I run this query, and I enter June 1st as the starting date and December 30 as the ending date and Fire for the department I get the data totals, for Therms, for the Fire department, by month. But when I try to create a chart and reference this query I get nothing on the bar graph. If I use one of the other charts I created I get one only 1 bar. I don't know if that means I'm geting closer or not.

SELECT Month([Bill-To Date]) AS expr, Sum(SWG.Therms) AS SumOfTherms, SWG.Dept
FROM SWG
GROUP BY Month([Bill-To Date]), SWG.Dept
HAVING (((Month([Bill-To Date])) Between [Forms]![SWG Query]![startdate] And [Forms]![SWG Query]![EndDate]) AND ((SWG.Dept)=[Forms]![SWG Query]![combo17]))
ORDER BY Month([Bill-To Date]);

 

[simongallop]

Copy the SQL view of the query that works and paste it into the Row Source property of the graph.

 

[rschultz]

I've already got this code in the Form_Load procedure of the form:

If Len(Me.OpenArgs) Then
Me!Graph0.RowSource = OpenArgs
End If

I also found this in a message in this forum which works great:

Dim sOffice As String
sOffice = Forms![swg Query].Combo17

Me!Graph0.ChartTitle.Text = "Chart for " & sOffice & " Department "

now I just need to display the data<g>

I've got it working on another chart with another Select statement but it doesn't group by month so if I ask for to large a timeframe I get too many data points.