SQL string not working

[DarrenB]

I'm relatively new to SQL. Im having a problem setting a charts row source value with an SQL string built using an event procedure. The chart is based on a table with a date field, and about 10 other number fields. I have a form from which I want the user to be able to select which elements are included in the chart. On this form are:

from date
to date

and a list of 10 checkboxes, each relating to a field from the table.

Then I want the SQL to include all records between the from and to dates, and only select those fields which are checked on the form. Heres my event procedure:


Dim strSQl As String
Dim fromdate, todate As Date


fromdate = [Forms]![charting]![from]
todate = [Forms]![charting]![to]


strSQl = "SELECT [dates]"

If Forms![charting]![line_speed] = -1 Then
strSQl = strSQl + ",Sum([line_speed]) AS [SumOfline_speed]"
End If

If Forms![charting]![motor_current] = -1 Then
strSQl = strSQl + ",Sum([motor_current]) AS [SumOfmotor_current]"
End If

If Forms![charting]![stenter_pre_tension] = -1 Then
strSQl = strSQl + ",Sum([stenter_pre_tension]) AS [SumOfstenter_pre_tension]"
End If

If Forms![charting]![film_thickness] = -1 Then
strSQl = strSQl + ",Sum([film_thickness]) AS [SumOffilm_thickness]"
End If

If Forms![charting]![hydraulic_pressure] = -1 Then
strSQl = strSQl + ",Sum([hydraulic_pressure]) AS [SumOfhydraulic_pressure]"
End If

If Forms![charting]![FS_elongation] = -1 Then
strSQl = strSQl + ",Sum([FS_elongation]) AS [SumOfFS_elongation]"
End If

If Forms![charting]![BS_elongation] = -1 Then
strSQl = strSQl + ",Sum([BS_elongation]) AS [SumOfBS_elongation]"
End If

If Forms![charting]![max_elongation] = -1 Then
strSQl = strSQl + ",Sum([max_elongation]) AS [SumOfmax_elongation]"
End If

If Forms![charting]![max_hyd_pressure] = -1 Then
strSQl = strSQl + ",Sum([max_hyd_pressure]) AS [SumOfmax_hyd_pressure]"
End If

If Forms![charting]![chain_difference] = -1 Then
strSQl = strSQl + ",Sum([chain_difference]) AS [SumOfchain_difference]"
End If

strSQl = strSQl + "FROM [51_stenter_checks] WHERE ([dates]>=#" & fromdate & "# And [dates]<=#" & todate & "#) GROUP BY (Year([dates])*12 + Month([dates])-1);"

DoCmd.OpenForm "51_stenter_chart"

Forms![51_stenter_chart]![51_stenter_chart].RowSource = strSQl

When I run it, on opening the chart, i get the message: 'You tried to execute a query that doesnt include the specified expression 'dates' as part of an agregate function'.

I know this is long winded - but does anyone have any ideas?

cheers

[This message has been edited by DarrenB (edited 05-23-2000).]

 

[bobjames]

You need the same fields in your SELECT that are in your group by. In your SELECT you should include "(Year([Dates])*12 + Month([Dates])-1)AS Expr1 " and Get rid of "[dates]". Of course, these result will not show [Dates], but I'm not sure if that's important.

[This message has been edited by bobjames (edited 05-23-2000).]

 

[adj]

Bob,
I think you also have to tell the SQL Select (field) From (table name). That might help some of you errors.

Anne