Need HELP Graphing Query from SOL Visual Basic

[akneppers]

Been Trying to Figure out how to fix my code but im trying to graph data from my query "STBDFWDQuery" but it will only graph if i it links to the STBDFWD table and not the Query. I will reference my code below:

Option Compare Database
Option Explicit


Private Sub Date_Range_Click()


On Error GoTo Date_Range_Click_Err
DoCmd.OpenQuery "STBDFWDQuery", acViewNormal, acEdit
DoCmd.OpenQuery "STBDFWDQuery", acViewNormal, acReadOnly


Date_Range_Click_Exit:
Exit Sub


Date_Range_Click_Err:
MsgBox Error$
Resume Date_Range_Click_Exit


End Sub

Private Sub Check43_AfterUpdate()
RunRpt
End Sub


Private Sub Check44_AfterUpdate()
RunRpt
End Sub


Private Sub Check45_AfterUpdate()
RunRpt
End Sub


Private Sub Form_Open(Cancel As Integer)
Me.Move 0, 300
End Sub




Sub RunRpt()
' On Error GoTo Err_RunRpt
Dim stDocName As String
Dim strInclude As String
Dim strInclude1 As String
Dim strInclude2 As String
Dim strInclude3 As String
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String

' Check to see if we include the value for each checkbox
If Forms!DateQueryForm.Check43 Then
strInclude = "STBDFWD.CableCount,"
End If

If Forms!DateQueryForm.Check44 Then
strInclude1 = "STBDFWD.Dyno1Tension,"
End If

If Forms!DateQueryForm.Check45 Then
strInclude2 = "STBDFWD.Dyno2Tension,"
End If

If Forms!DateQueryForm.Check46 Then
strInclude3 = "STBDFWD.CableSpeed,"
End If

If strInclude <> "" Then
' cuts off the last comma
strInclude = Left(strInclude, Len(strInclude) - 1)
' adds syntax we need
strInclude = "In(" & strInclude & ")"


' creates the new SQL string for the chart's row source with strInclude concatenated in
'


strSQL = "TRANSFORM Avg(STBDFWD.CableCount) AS CountOfOrders " & _
"SELECT (Format([Opto22Time],'H:NNAMPM')) AS Expr1 " & _
"FROM STBDFWD WHERE STBDFWD.CableCount " & strInclude & _
"GROUP BY (Int([Opto22Time]*1440)),(Format([Opto22Time],'H:NNAMPM'))" & _
"PIVOT 'Cable Count';"

End If
' Report Name
stDocName = "OrdersWithChart"
' keep changes from being visible
Application.Echo False


' we have to open the report in design view in order to set the new row source
' of the chart. We do it hidden so nobody sees it happening.
DoCmd.OpenReport "OrdersWithChart", acViewDesign, , , acHidden
' sets the row source of the chart to be the one we are substituting
Reports!OrdersWithChart.MyGraph.RowSource = strSQL
' closes and saves the report
DoCmd.Close acReport, stDocName, acSaveYes
' reopens it, this time so the user can see it.
DoCmd.OpenReport stDocName, acViewPreview
Application.Echo True






Exit_RunRpt:
Application.Echo True
Exit Sub


Err_RunRpt:
MsgBox Err.Description
Resume Exit_RunRpt
End Sub

[ranman256]

build a chart,
apply your query,
edit the 'data' in the chart, and paste real data from your query.
format the chart as needed.
save the chart.

now the chart should change along with the query.

[akneppers]

Well is there a way to do it through a form. Currently the user clicks a box and then it outputs to a report with the graph. Only want certain date and times selected.

[June7]

Apparently, CROSSTAB query with filter criteria requires PARAMETERS clause. Review http://allenbrowne.com/ser-67.html#Param

I have built many graphs for my DBs and none require CROSSTAB - lucky me. Think they are all XYScatter type. I have helped with a pie chart, can't remember if that needed CROSSTAB.

[akneppers]

Im a little confused about the parameters, would either of the FROM statements work or do i have it wrong?

strSQL = "TRANSFORM Avg([CableCount]) AS CountOfOrders " & _
"SELECT (Format([Opto22Time],'H:NNAMPM')) AS Expr1 " & _
"FROM STBDFWD WHERE (((STBDFWD.Opto22Date)>=[Forms]![DateQueryForm]![Start_Date] AND (STBDFWD.Opto22Date)<=[Forms]![DateQueryForm]![End_Date]) AND ((STBDFWD.Opto22Time)>=[Forms]![DateQueryForm]![Start_Time] And (STBDFWD.Opto22Time)<=[Forms]![DateQueryForm]![End_Time]))" & strInclude & _
"GROUP BY (Int([Opto22Time]*1440)),(Format([Opto22Time],'H:NNAMPM'))" & _
"PIVOT 'Cable Count';"
' "FROM STBDFWD WHERE ((STBDFWD.Opto22Date Between #[Forms]![DateQueryForm]![Start_Date]# AND #[Forms]![DateQueryForm]![End_Date]#) AND (STBDFWD.Opto22Date Between #[Forms]![DateQueryForm]![Start_Time]# And #[Forms]![DateQueryForm]![End_Time]#))" & _

[June7]

What happens when you use either version?

What exactly do you not understand about the use of PARAMETERS? The article seems clear enough. Build CROSSTAB query object with the query designer to get the correct syntax to use in VBA. Switch to SQL View to see the SQL and how it is constructed with PARAMETERS clause.