filtering a query for charts in a form

  • Thread starter Thread starter pat67
  • Start date Start date
P

pat67

Hi I have a form with 3 different charts that i am trying to show the
totals for unless they are filtered by vendor. i have a combo called
box cmbVendor where i choose the vendor from. In the row source i
have
the following sql

SELECT qryExc_Mesage_Weekly.[Extraction Date],
Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly
WHERE (((qryExc_Mesage_Weekly.[Vendor Code])=[Me].[cmbVendor]))
GROUP BY qryExc_Mesage_Weekly.[Extraction Date];


the issue is i am always being asked for Me.cmbVendor. And unless i
put a vendor number in I get a blank chart.


What am i doing wrong?
 
Try putting in a full reference to the form.

SELECT qryExc_Mesage_Weekly.[Extraction Date],
Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly
WHERE qryExc_Mesage_Weekly.[Vendor Code]=
[Forms]![NameOfTheForm]![cmbVendor]
GROUP BY qryExc_Mesage_Weekly.[Extraction Date];



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Try putting in a full reference to the form.

SELECT qryExc_Mesage_Weekly.[Extraction Date],
Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly
WHERE qryExc_Mesage_Weekly.[Vendor Code]=
[Forms]![NameOfTheForm]![cmbVendor]
GROUP BY qryExc_Mesage_Weekly.[Extraction Date];

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

SELECT qryExc_Mesage_Weekly.[Extraction Date],
Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly
WHERE (((qryExc_Mesage_Weekly.[Vendor Code])=[Me].[cmbVendor]))
GROUP BY qryExc_Mesage_Weekly.[Extraction Date];- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Ok I tried that and i no longer get a input box, but I also do not get
any data.
 
If you leave out the where line, do you get data?

If so, check that Vendor Code is a valid field and has the type of data that
the bound column of cmbVendor is returning.

Try extracting the SQL into a query and hand entering the criteria.


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

Try putting in a full reference to the form.

SELECT qryExc_Mesage_Weekly.[Extraction Date],
Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly
WHERE qryExc_Mesage_Weekly.[Vendor Code]=
[Forms]![NameOfTheForm]![cmbVendor]
GROUP BY qryExc_Mesage_Weekly.[Extraction Date];

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

SELECT qryExc_Mesage_Weekly.[Extraction Date],
Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly
WHERE (((qryExc_Mesage_Weekly.[Vendor Code])=[Me].[cmbVendor]))
GROUP BY qryExc_Mesage_Weekly.[Extraction Date];- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Ok I tried that and i no longer get a input box, but I also do not get
any data.
Click to expand...
 
Back
Top