Report Parameters for Chart

I

Indigo

Registered User.
Local time
Today, 15:12
Joined
Nov 12, 2008
Messages
241
This one has me stumped. I am running Access 2003 and have created a report with a column chart. The chart is based on the following query:

Code: 
SELECT BufferMgmt.BufferMgmtID, BufferMgmt.DateEntered, BufferMgmt.GLLink, GLInfo.AMArea, BufferMgmt.Shift, ZoneProcesstbl.Process, [FirstBreak]+[LunchBreak]+[SecondBreak]+[PostShift] AS TotalTime
FROM (BufferMgmt INNER JOIN ZoneProcesstbl ON BufferMgmt.ProcessID = ZoneProcesstbl.ZoneProcessID) INNER JOIN GLInfo ON BufferMgmt.GLLink = GLInfo.GLNumber
WHERE (((BufferMgmt.DateEntered) Between [Forms]![HoldingInfo]![TxtReportStart] And [Forms]![HoldingInfo]![TxtReportEnd]) AND ((GLInfo.AMArea)=[Forms]![ReportFrm]![cboAMZone]));

with the following record source for the chart x-tab:

Code: 
PARAMETERS [Forms]![HoldingInfo]![TxtReportStart] DateTime, [Forms]![HoldingInfo]![TxtReportEnd] DateTime, [Forms]![ReportFrm]![cboAMZone] Text ( 255 );
TRANSFORM Sum(qryBufferTotal.TotalTime) AS SumOfTotalTime
SELECT qryBufferTotal.Process
FROM qryBufferTotal
GROUP BY qryBufferTotal.Process
PIVOT qryBufferTotal.Shift;

Worked just fine, but now the users want to have the option of ALL as well as selecting a specific AMArea. So I did some research and changed my query as follows:

Code: 
PARAMETERS [Forms]![HoldingInfo]![TxtReportEnd] DateTime, [Forms]![HoldingInfo]![TxtReportStart] DateTime, [Forms]![ReportFrm]![cboAMZone] Text ( 255 );
SELECT BufferMgmt.BufferMgmtID, BufferMgmt.DateEntered, BufferMgmt.GLLink, GLInfo.AMArea, BufferMgmt.Shift, ZoneProcesstbl.Process, [FirstBreak]+[LunchBreak]+[SecondBreak]+[PostShift] AS TotalTime
FROM (BufferMgmt INNER JOIN ZoneProcesstbl ON BufferMgmt.ProcessID = ZoneProcesstbl.ZoneProcessID) INNER JOIN GLInfo ON BufferMgmt.GLLink = GLInfo.GLNumber
WHERE (((BufferMgmt.DateEntered) Between [Forms]![HoldingInfo]![TxtReportStart] And [Forms]![HoldingInfo]![TxtReportEnd]) AND ((([GLInfo].[AMArea])=[Forms]![ReportFrm]![cboAMZone]) Is Null)) OR (((BufferMgmt.DateEntered) Between [Forms]![HoldingInfo]![TxtReportStart] And [Forms]![HoldingInfo]![TxtReportEnd]) AND ((GLInfo.AMArea) Like [Forms]![ReportFrm]![cboAMZone]));

To account for no selection made on the ReportFrm [cboAMZone].
Query works great but will not translate to the X-tab as the chart record source. If no selection is made on [Forms]![ReportFrm]![cboAMZone] I get a blank report.

Can anyone help me see what I am missing? Thank you
 
Thanks, I read through your thread and it had to do with Between dates - I have that..... my problem is the AMZone criteria. I'm not clear on how this post will help me?
 
It shows you how to set up your criteria so that if nothing is entered in the parameter it pulls all the records. Your query doesn't do that.
 
So instead of the WHERE OR statement in my SQL it should be HAVING OR?
 
Changing it to:

Code: 
SELECT BufferMgmt.BufferMgmtID, BufferMgmt.DateEntered, BufferMgmt.GLLink, GLInfo.AMArea, BufferMgmt.Shift, ZoneProcesstbl.Process, [FirstBreak]+[LunchBreak]+[SecondBreak]+[PostShift] AS TotalTime
FROM (BufferMgmt INNER JOIN ZoneProcesstbl ON BufferMgmt.ProcessID = ZoneProcesstbl.ZoneProcessID) INNER JOIN GLInfo ON BufferMgmt.GLLink = GLInfo.GLNumber
GROUP BY BufferMgmt.BufferMgmtID, BufferMgmt.DateEntered, BufferMgmt.GLLink, GLInfo.AMArea, BufferMgmt.Shift, ZoneProcesstbl.Process, [FirstBreak]+[LunchBreak]+[SecondBreak]+[PostShift]
HAVING (((BufferMgmt.DateEntered) Between [Forms]![HoldingInfo]![TxtReportStart] And [Forms]![HoldingInfo]![TxtReportEnd]) AND ((([GLInfo].[AMArea])=[Forms]![ReportFrm]![cboAMZone]) Is Null)) OR (((BufferMgmt.DateEntered) Between [Forms]![HoldingInfo]![TxtReportStart] And [Forms]![HoldingInfo]![TxtReportEnd]) AND ((GLInfo.AMArea) Like [Forms]![ReportFrm]![cboAMZone]));

I still do not get the desired results. The chart is blank if no selection is made on Forms!ReportFrm!cboAMZone
 
Here's the HAVING part. Notice the way I've grouped the statements in parentheses:
Code: 
HAVING [COLOR=Red]([/COLOR]
        [COLOR=DarkGreen]([/COLOR]BufferMgmt.DateEntered BETWEEN Nz([Forms]![HoldingInfo]![TxtReportStart], [Forms]![HoldingInfo]![Txt[COLOR=Red][B]Alt[/B][/COLOR]ReportStart]) AND 
                                        Nz([Forms]![HoldingInfo]![TxtReportEnd], [Forms]![HoldingInfo]![Txt[B][COLOR=Red]Alt[/COLOR][/B]ReportEnd])[COLOR=Blue][COLOR=DarkGreen])[/COLOR] [/COLOR]AND 
       [COLOR=Blue] ([/COLOR][GLInfo].[AMArea] = [Forms]![ReportFrm]![cboAMZone] OR [Forms]![ReportFrm]![cboAMZone] Is Null[COLOR=Blue])[/COLOR]
      [COLOR=Red] )[/COLOR]
I've only spaced it out like that for brevity.

Also do you notice the textboxes with Alt? You need to create two textboxes, one for TxtAltReportStart and TxtAltReportEnd where TxtAltReportStart will return the min value of the DateEntered field and TxtAltReportEnd will return the max value of the DateEntered field.

For min use DMin() and for max use DMax(). Then hide those textboxes.
 
Okay - I see what you are trying to do, ....

Here is my new SQL:

Code: 
PARAMETERS [Forms]![HoldingInfo]![TxtReportEnd] DateTime, [Forms]![HoldingInfo]![TxtReportStart] DateTime, [Forms]![ReportFrm]![cboAMZone] Text ( 255 );
SELECT BufferMgmt.BufferMgmtID, BufferMgmt.DateEntered, BufferMgmt.GLLink, GLInfo.AMArea, BufferMgmt.Shift, ZoneProcesstbl.Process, [FirstBreak]+[LunchBreak]+[SecondBreak]+[PostShift] AS TotalTime
FROM (BufferMgmt INNER JOIN ZoneProcesstbl ON BufferMgmt.ProcessID = ZoneProcesstbl.ZoneProcessID) INNER JOIN GLInfo ON BufferMgmt.GLLink = GLInfo.GLNumber
GROUP BY BufferMgmt.BufferMgmtID, BufferMgmt.DateEntered, BufferMgmt.GLLink, GLInfo.AMArea, BufferMgmt.Shift, ZoneProcesstbl.Process, [FirstBreak]+[LunchBreak]+[SecondBreak]+[PostShift]
HAVING (((BufferMgmt.DateEntered) Between Nz([Forms]![HoldingInfo]![TxtReportStart],[Forms]![HoldingInfo]![TxtAltReportStart]) And nz([Forms]![HoldingInfo]![TxtReportEnd],[Forms]![HoldingInfo]![TxtAltReportEnd])) AND ((GLInfo.AMArea)=[Forms]![ReportFrm]![cboAMZone])) OR ((([Forms]![ReportFrm]![cboAMZone]) Is Null));

But I am still getting no results if [Forms]![ReportFrm]![cboAMZone] Is Null
 
It's not grouped properly. Follow what was previously given.
 
I'm sorry.... what am I missing?

Code: 
SELECT BufferMgmt.BufferMgmtID, BufferMgmt.DateEntered, BufferMgmt.GLLink, GLInfo.AMArea, BufferMgmt.Shift, ZoneProcesstbl.Process, [FirstBreak]+[LunchBreak]+[SecondBreak]+[PostShift] AS TotalTime
FROM (BufferMgmt INNER JOIN ZoneProcesstbl ON BufferMgmt.ProcessID = ZoneProcesstbl.ZoneProcessID) INNER JOIN GLInfo ON BufferMgmt.GLLink = GLInfo.GLNumber
GROUP BY BufferMgmt.BufferMgmtID, BufferMgmt.DateEntered, BufferMgmt.GLLink, GLInfo.AMArea, BufferMgmt.Shift, ZoneProcesstbl.Process, [FirstBreak]+[LunchBreak]+[SecondBreak]+[PostShift]
HAVING ((BufferMgmt.DateEntered Between Nz([Forms]![HoldingInfo]![TxtReportStart],[Forms]![HoldingInfo]![TxtAltReportStart]) AND Nz([Forms]![HoldingInfo]![TxtReportEnd],[Forms]![HoldingInfo]![TxtAltReportEnd])) AND ([GLInfo].[AMArea] = [Forms]![ReportFrm]![cboAMZone] OR [Forms]![ReportFrm]![cboAMZone] Is Null));

The report is still blank when cboAMZone is Null.
 
Let me see the Control Source of TxtAltReportStart and TxtAltReportEnd.
 
TxtAltReportStart=DMin("DateEntered","BufferMgmt")
TxtAltReportEnd=DMax("DateEntered","BufferMgmt")
 
What portion of each lines is the Control Source?

Also, you can't be using BufferMgmt. That's your query.
 
=DMin("DateEntered","BufferMgmt")
=DMax("DateEntered","BufferMgmt")

BufferMgmt is the source table name... what would I use?
 
Ah, so it is!

Are the Alt textboxes returning the correct values?

Also, if you completely remove all the criteria does it display ALL records?
 
Unfortunately, no. I would have to build one just with this function in it as I cannot share the data that is in this database. As I said in my earlier posts, I can get the query to work fine, but I cannot get it to translate to the crosstab SQL that supports the chart in the report.
 
I can't help any further if you're not able to produce a representative db. Of course sample (or bogus) data was what I was after.
 
Thanks - I'll have to get back to you when I have a chance to pull something together.....
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom