Chart Help

[mattc66 via AccessMonster.com]

I have created a Chart. I want to add a button to my form and pull up the
chart. The data will be based on an item on the form. How would I change the
charts crosstab query to be based on the item from my form? It would require
the Where Criteria to be based on a variable from my form. See below -
"xxxxx" would be the Item number from my form.

TRANSFORM Sum(qryItemsConsumed.USED) AS SumOfUSED
SELECT qryItemsConsumed.SORT1 AS PERIODS
FROM qryItemsConsumed
WHERE (((qryItemsConsumed.ITEM)="xxxxx"))
GROUP BY qryItemsConsumed.SORT1
ORDER BY qryItemsConsumed.SORT1 DESC
PIVOT qryItemsConsumed.ITEM;

 

[Mark Andrews]

I have had problems using normal ways of passing criteria (when charts are
in the mix).

Normally you would use "WHERE (((qryItemsConsumed.ITEM)= """ &
Forms!frmYourForm!yourcontrol & """))"
in the query itself (and just have the chart refer to the query).

However I have been always rebuilding the query from a string that holds the
sql. Just build a string using the above method and then
rebuild the query with the exact value for the "xxxxx".

Similar to how this functions builds a query:

Public Sub ExportQueryToExcel(QueryName As String, sql As String, filepath
As String)
On Error GoTo Err_ExportQueryToExcel
'exports the specified query to the specified filepath
'Note: if sql <> "" then populate query with sql and save first before
exporting

Dim dbs As Database
Dim dbQueryDef As DAO.QueryDef

If (sql <> "") Then
Set dbs = CurrentDb()
Set dbQueryDef = dbs.QueryDefs(QueryName)
dbQueryDef.sql = sql
dbQueryDef.Close
End If
DoCmd.OutputTo acOutputQuery, QueryName, acFormatXLS, filepath
MsgBox "Export File Created: " & filepath, , "Export"

Exit_ExportQueryToExcel:
Set dbQueryDef = Nothing
Set dbs = Nothing
Exit Sub

Err_ExportQueryToExcel:
MsgBox Err.Description
Resume Exit_ExportQueryToExcel

End Sub

HTH,
Mark
RPT Software
http://www.rptsoftware.com