need to show unfiltered data on chart

[pat67]

Hi all. I have form that contains charts. I have them currently set up
to show when they are filtered for a vendor. what I want is to show
the data when it is unfiltered. Here is the sql from the row source
for the chart.

SELECT qryExc_Mesage_Weekly.Date, Sum(qryExc_Mesage_Weekly.Cancel) AS
[Canx Value]
FROM qryExc_Mesage_Weekly
WHERE (((qryExc_Mesage_Weekly.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum]))
GROUP BY qryExc_Mesage_Weekly.Date;

I assume it must be done through code, but i am not sure exactly.

Thanks

 

[John W. Vinson]

Hi all. I have form that contains charts. I have them currently set up
to show when they are filtered for a vendor. what I want is to show
the data when it is unfiltered. Here is the sql from the row source
for the chart.

SELECT qryExc_Mesage_Weekly.Date, Sum(qryExc_Mesage_Weekly.Cancel) AS
[Canx Value]
FROM qryExc_Mesage_Weekly
WHERE (((qryExc_Mesage_Weekly.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum]))
GROUP BY qryExc_Mesage_Weekly.Date;

I assume it must be done through code, but i am not sure exactly.

Thanks

Just remove the WHERE clause if you don't want it limited by vendor:

SELECT qryExc_Mesage_Weekly.Date, Sum(qryExc_Mesage_Weekly.Cancel) AS
[Canx Value]
FROM qryExc_Mesage_Weekly
GROUP BY qryExc_Mesage_Weekly.Date;
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John Spencer]

SELECT qryExc_Mesage_Weekly.Date
, Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly

WHERE qryExc_Mesage_Weekly.[Vendor Name]=
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null
GROUP BY qryExc_Mesage_Weekly.Date;

Or if txtVendorName can have a value like "ALL" as a choice.

WHERE qryExc_Mesage_Weekly.[Vendor Name] =
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null

OR if txtVendorName can be null and Vendor name field always has a value
WHERE qryExc_Mesage_Weekly.[Vendor Name]Like
Nz([Forms]![frmExc_Mess_Charts]![txtVendorNum],"*")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[pat67]

SELECT qryExc_Mesage_Weekly.Date
, Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly

WHERE qryExc_Mesage_Weekly.[Vendor Name]=
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null
GROUP BY qryExc_Mesage_Weekly.Date;

Or if txtVendorName can have a value like "ALL" as a choice.

WHERE qryExc_Mesage_Weekly.[Vendor Name] =
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null

OR if txtVendorName can be null and Vendor name field always has a value
WHERE qryExc_Mesage_Weekly.[Vendor Name]Like
Nz([Forms]![frmExc_Mess_Charts]![txtVendorNum],"*")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SELECT qryExc_Mesage_Weekly.Date, Sum(qryExc_Mesage_Weekly.Cancel) AS
[Canx Value]
FROM qryExc_Mesage_Weekly
WHERE (((qryExc_Mesage_Weekly.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum]))
GROUP BY qryExc_Mesage_Weekly.Date;- Hide quoted text -

- Show quoted text -

Thanks I will try that.

 

[pat67]

SELECT qryExc_Mesage_Weekly.Date
, Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly

WHERE qryExc_Mesage_Weekly.[Vendor Name]=
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null
GROUP BY qryExc_Mesage_Weekly.Date;

Or if txtVendorName can have a value like "ALL" as a choice.

WHERE qryExc_Mesage_Weekly.[Vendor Name] =
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null

OR if txtVendorName can be null and Vendor name field always has a value
WHERE qryExc_Mesage_Weekly.[Vendor Name]Like
Nz([Forms]![frmExc_Mess_Charts]![txtVendorNum],"*")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SELECT qryExc_Mesage_Weekly.Date, Sum(qryExc_Mesage_Weekly.Cancel) AS
[Canx Value]
FROM qryExc_Mesage_Weekly
WHERE (((qryExc_Mesage_Weekly.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum]))
GROUP BY qryExc_Mesage_Weekly.Date;- Hide quoted text -

- Show quoted text -

Ok That works. Thanks. The other thing I was wondering is if I could
use something like this

SELECT qryExc_Mesage_Weekly_1.Date, Sum(qryExc_Mesage_Weekly_1.[Cancel
Value]) AS [Canx Value]
FROM qryExc_Mesage_Weekly_1
WHERE (((qryExc_Mesage_Weekly_1.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum]))
GROUP BY qryExc_Mesage_Weekly_1.Date
OR
SELECT qryExc_Mesage_Weekly.Date
, Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly
WHERE [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null
GROUP BY qryExc_Mesage_Weekly.Date;

This particular sql does not work but maybe you can fix it. What I
want to do is show one query when it is unfiltered nut if i filter,
show another. reason is the overall values are in the millions and the
query I use shows as say 6.5M as opposed to 6,500,000. But by vendor
it will be smaller i.e. 50,000 ro something like that. Is what I am
asking possible?

 

[John Spencer]

The only way I know to do this is to call different queries depending on the
value of the control.

If you are using the queries as the source of a report and you call the report
from the form, you can change the report's source in the call for the report.

So if you have a button that launches a report you would have code in the
button's click event like

If IsNull(Me.TxtVendorNum) Then
DoCmd.OpenReport "MyReport",acViewPreview,"QueryNameOne"
ELSE
DoCmd.OpenReport "MyReport",acViewPreview,"QueryNameTwo"
End If



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SELECT qryExc_Mesage_Weekly.Date
, Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly

WHERE qryExc_Mesage_Weekly.[Vendor Name]=
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null
GROUP BY qryExc_Mesage_Weekly.Date;

Or if txtVendorName can have a value like "ALL" as a choice.

WHERE qryExc_Mesage_Weekly.[Vendor Name] =
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null

OR if txtVendorName can be null and Vendor name field always has a value
WHERE qryExc_Mesage_Weekly.[Vendor Name]Like
Nz([Forms]![frmExc_Mess_Charts]![txtVendorNum],"*")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SELECT qryExc_Mesage_Weekly.Date, Sum(qryExc_Mesage_Weekly.Cancel) AS
[Canx Value]
FROM qryExc_Mesage_Weekly
WHERE (((qryExc_Mesage_Weekly.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum]))
GROUP BY qryExc_Mesage_Weekly.Date;- Hide quoted text -

- Show quoted text -

Ok That works. Thanks. The other thing I was wondering is if I could
use something like this

SELECT qryExc_Mesage_Weekly_1.Date, Sum(qryExc_Mesage_Weekly_1.[Cancel
Value]) AS [Canx Value]
FROM qryExc_Mesage_Weekly_1
WHERE (((qryExc_Mesage_Weekly_1.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum]))
GROUP BY qryExc_Mesage_Weekly_1.Date
OR
SELECT qryExc_Mesage_Weekly.Date
, Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly
WHERE [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null
GROUP BY qryExc_Mesage_Weekly.Date;

This particular sql does not work but maybe you can fix it. What I
want to do is show one query when it is unfiltered nut if i filter,
show another. reason is the overall values are in the millions and the
query I use shows as say 6.5M as opposed to 6,500,000. But by vendor
it will be smaller i.e. 50,000 ro something like that. Is what I am
asking possible?