Access Graph wizzard and data in report

[apestaart]

I have made a report with a graph using the Graph Wizzard in Access. The wizzard let you chose a query. The query I chosed is based upon another query asking for the "starting date" and "ending date" for a period of time.
The graph shows days of illness during that period per departments. That works fine but I want to add in the report header the period, so "starting date" and "ending date" and I can't find the way to do it.
Who has some expirience with this matter and can halp me solving this problem?
Regards Apestaart.

 

[jjb373]

apestaart,

In your report header, add a blank textbox then delete the label associated with that textbox. Right click on the textbox and choose the Properties option.

In the Properties for the textbox, choose the data tab and under the Data tab type in the Control Source:

= [starting date] & " to " & [ending date]

Make sure you use the exact same criteria for starting date and ending date as you did in the query.

That should do the trick.

 

[apestaart]

Hallo jjb373 ,
Thanks for your respons. I have tried your suggestion already but the problem is that the system is asking for the same data twice. Once from the query and once from the reportheader.
Once the query has asked for the parameters (I have put those into the parameterlist of the query) the parameters are known by the system and I think there should be an other way the put the values in the formheader.
Other solutions?

Regards,

apestaart

 

[jjb373]

Just so you know...That solution I gave you should not prompt the user to enter the parameter values again even though that formula is in the report header. What that formula is doing is --> going back to the query (which prompts the user for the start and end date) and pulling whatever the user entered into the textbox.

The user should not have to enter anything into the textbox on the report header.

Cheers,

jjb373

 

[apestaart]

Hi jjb373,
I have tried it again. In the textfield in the formheader I find "#Name?". That means I think undefined.

This is the SQL view of my query:

PARAMETERS [Periode van] DateTime, [Periode tot] DateTime;
SELECT TabRegios.RegioID, TabRegios.Regio, TabStations.StationID, TabStations.Station, TabStations.Beheerder, TabStations.Telefoon, Verzuim.[Datum ziekmelding], Verzuim.Reden, Verzuim.Oorzaak, Verzuim.[Datum herstelmelding], Verzuim.[Aantal uren ziek]
FROM ((TabRegios INNER JOIN TabStations ON TabRegios.RegioID = TabStations.RegioID) INNER JOIN Werknemers ON TabStations.StationID = Werknemers.StationID) INNER JOIN Verzuim ON Werknemers.RelID = Verzuim.RelID
WHERE (((Verzuim.[Datum ziekmelding])>[Periode van]) AND ((Verzuim.[Datum herstelmelding])<[Periode tot]));

"Periode van" means Date from and "Periode tot" means Date to.

From this query I used the values [Aantal uren ziek],[Regio] and [Station]in the Graph wizzard to build a graph in the detail section of the report.

The Rowsource for the Graph looks like this :

TRANSFORM Sum([Report Query].[Aantal uren ziek]) AS [SumOfAantal uren ziek]
SELECT [Report Query].Regio
FROM [Report Query]
GROUP BY [Report Query].Regio
PIVOT [Report Query].Station;

When I open the report it is asking for the two parameters.

What went wrong I don't know.
Hope you can help me, although the sql is partely in Dutch.

Regards apestaart.

 

[jjb373]

I thought you were trying to add this to a report. Your original post says:
I have made a report with a graph using the Graph Wizzard in Access.......That works fine but I want to add in the report header the period, so "starting date" and "ending date"

If you added my solution to a Report's - Report Header, then it will work just as you want.

But it seems as though you are trying to add this to a Form??? Is this correct?

Cheers,

JJB373

 

[PHV]

You may try this in the Graph's query:
PARAMETERS [Periode van] DateTime, [Periode tot] DateTime;
TRANSFORM ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[apestaart]

Sorry jjb373, I ment report, it should be a report.
I will try PHVs tip first and will come back to you.
Apestaart