RE: Graph won't show

It looks like you have two combo boxes on the form. If they are both Null
then record must meet this criteria:
PL Is Null AND CT="TOTALS"
You might want to consider using Nz() like:
CT = Nz([Forms]...![cmbCT],"TOTALS")

--
Duane Hookom
Microsoft Access MVP


"Lydia" wrote:

Hi Duane,

Thanks for taking time to look into it.

I modified my query and it still won't take any null value from the criteria
entry form. However when I select "Totals", graph shows, and "Totals" means
the same as Null in my case. So I just made sure "Totals" always kicks in if
user doesn't select anything from the drop down box. This way the graph works
fine.

I still can't figure out why no selection of criteria results in graph not
showing. If you may have some suggestion, I would love to hear it. Otherwise,
things work for me for now.

Thanks a lot.

Lydia
--
Access/VB Programmer



"Duane Hookom" wrote:

I would use a saved query as the Row Source of the graph. You could use a
little code in the frmOverallReportCriteria that would change the SQL
property of the saved query based on the values selected in the combo boxes.
Your DAO code might look something like:

Dim strSQL as String
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cmbCT) Then
strWhere = strWhere & " AND CT = """ & Me.cmbCT & """ "
End IF
' --- other criteria building ---
strSQL = "SELECT Product, PL, CT, Val([MOP]) AS NMOP, MOP, " & _
"Val([MIS/Miles]) AS NMIS, [YTD/CMP], [C/1000], " & _
"Format([C/1000],"0.00") AS C1000, ModelYear " & _
"FROM qryC1000DataMOPChryslerI WHERE " & strWhere

CurrentDb.QueryDefs("qselForGraph").SQL = strSQL
'--- then open the report with the graph ---
--
Duane Hookom
Microsoft Access MVP


"Lydia" wrote:

Hi Juane,

I feel sorry to trouble you with this. It is much easier for me look at it
in the design screen.

The crosstab query is based on a query that has two other queries in it.
These two other queries are the ones that contain the criteria.

The following is the SQL from the first of the two queries that contain
criteria. If it is too much trouble, please ignore it. I guess I will try
harder myself to see if I can find out something is wrong.
Thanks.

PARAMETERS [forms]![frmOverallReportCriteria]![cmbPL] Text ( 255 ),
[forms]![frmOverallReportCriteria]![cmbCT] Text ( 255 );
SELECT qryC1000DataMOPChryslerI.Product, qryC1000DataMOPChryslerI.PL,
qryC1000DataMOPChryslerI.CT, Val([MOP]) AS NMOP,
qryC1000DataMOPChryslerI.MOP, Val([MIS/Miles]) AS NMIS,
qryC1000DataMOPChryslerI.[YTD/CMP], qryC1000DataMOPChryslerI.[C/1000],
Format([C/1000],"0.00") AS C1000, qryC1000DataMOPChryslerI.ModelYear
FROM qryC1000DataMOPChryslerI
WHERE
(((qryC1000DataMOPChryslerI.PL)=[forms]![frmOverallReportCriteria]![cmbPL])
AND
((qryC1000DataMOPChryslerI.CT)=[forms]![frmOverallReportCriteria]![cmbCT])
AND (Not ([forms]![frmOverallReportCriteria]![cmbCT]) Is Null) AND (Not
([Forms]![frmOverallReportCriteria]![CmbPL]) Is Null)) OR
(((qryC1000DataMOPChryslerI.PL)=[forms]![frmOverallReportCriteria]![cmbPL])
AND ((qryC1000DataMOPChryslerI.CT)="TOTALS") AND
(([forms]![frmOverallReportCriteria]![cmbCT]) Is Null)) OR
(((qryC1000DataMOPChryslerI.PL) Is Null) AND
((qryC1000DataMOPChryslerI.CT)="TOTALS") AND
(([forms]![frmOverallReportCriteria]![cmbCT]) Is Null) AND
(([Forms]![frmOverallReportCriteria]![CmbPL])="Totals")) OR
(((qryC1000DataMOPChryslerI.PL) Is Null) AND
((qryC1000DataMOPChryslerI.CT)="TOTALS") AND
(([forms]![frmOverallReportCriteria]![cmbCT]) Is Null) AND
(([Forms]![frmOverallReportCriteria]![CmbPL]) Is Null)) OR
(((qryC1000DataMOPChryslerI.PL) Is Null) AND
((qryC1000DataMOPChryslerI.CT)="TOTALS") AND
(([forms]![frmOverallReportCriteria]![cmbCT])="Totals") AND
(([Forms]![frmOverallReportCriteria]![CmbPL]) Is Null)) OR
(((qryC1000DataMOPChryslerI.PL) Is Null) AND
((qryC1000DataMOPChryslerI.CT)=[forms]![frmOverallReportCriteria]![cmbCT])
AND (([Forms]![frmOverallReportCriteria]![CmbPL]) Is Null))
ORDER BY qryC1000DataMOPChryslerI.Product, qryC1000DataMOPChryslerI.PL,
qryC1000DataMOPChryslerI.CT, Val([MOP]), Val([MIS/Miles]),
qryC1000DataMOPChryslerI.ModelYear, qryC1000DataMOPChryslerI.PL,
qryC1000DataMOPChryslerI.CT, qryC1000DataMOPChryslerI.MOP;


Lydia
--
Access/VB Programmer



"Duane Hookom" wrote:

I guess I should have asked to see the SQL of the query containing the
references to the drop-down boxes since that seems to be where the error is
originating.
--
Duane Hookom
Microsoft Access MVP


"Lydia" wrote:

The following is the row source:
TRANSFORM Sum(qryC1000DataMOP.C1000) AS SumOfC1000 SELECT
qryC1000DataMOP.NMIS FROM qryC1000DataMOP GROUP BY qryC1000DataMOP.NMIS ORDER
BY qryC1000DataMOP.NMIS, qryC1000DataMOP.NMOP PIVOT qryC1000DataMOP.NMOP;

Yes. I did set paramers data types in the parameters window.

Thanks.

Lydia



Access/VB Programmer



"Duane Hookom" wrote:

What is the Row Source of the graph? Did you set the data types of the
parameters?

--
Duane Hookom
Microsoft Access MVP


"Lydia" wrote:

Hi,

I have a graph that is based on a crosstab query and have 4 fields that take
input data or parameter values from four drop-down boxes on a form.

When I run the report just by clicking on the report without using the form
that has four drop-down boxes, it works fine no matter what way I entered
those parameter values. However, if I use the form and not select some values
on the form, the graph stop showing. Graphs will show if I select most of
values on the form.


I just wonder what I did wrong.

Thanks.

Lydia


--
Access/VB Programmer

.

Relevant Pages

  • RE: Graph wont show
    ... "Duane Hookom" wrote: ... I modified my query and it still won't take any null value from the criteria ... I still can't figure out why no selection of criteria results in graph not ...
    (microsoft.public.access.reports)
  • RE: Graph wont show
    ... I modified my query and it still won't take any null value from the criteria ... I still can't figure out why no selection of criteria results in graph not ... "Duane Hookom" wrote: ...
    (microsoft.public.access.reports)
  • Re: Charts and criteria in the underlying Query
    ... I copied and pasted it directly into the criteria of ProducedShift column. ... "Duane Hookom" wrote: ... What's the "request from Query1 for Parameter value"? ...
    (microsoft.public.access.reports)
  • Re: Charts and criteria in the underlying Query
    ... Put criteria back and got all records whth value in new field. ... "Duane Hookom" wrote: ... MS Access MVP ... If the form control value is null, then compare the field value to ...
    (microsoft.public.access.reports)
  • Re: Date programming question.
    ... If you remove the Criteria, ... Allen Browne - Microsoft MVP. ... In the Detail section of the form are 3 more boxes. ... Since the form is in Tabular format it will list all ...
    (microsoft.public.access.modulesdaovba)