I am using Microsoft Access 2007 and I have a query which looks something like this;
PARAMETERS Identifier TEXT(255);
SELECT
DateDiff('h', (SELECT Min(SampleDateTime) FROM Specimen WHERE UserID=[Identifier]), SampleDateTime) AS ElapsedTime, Specimen.ValueA
FROM Specimen
WHERE Specimen.UserID=[Identifier]
ORDER BY Specimen.SampleDateTime
Now, this query works fine if I open the query and then enter the appropriate 'Identifier' value in the form that pops up. However, I would like to then use this query to generate a chart in a report that lists all of the studies. ie. The report will be made of 1 page per record, and each page will contain 1 chart, which shows the above data plotted as a line chart.
However, if I pop a chart in and use the wizard to create the chart, when I get to the stage where I would like to assign a fiend from the current record in the report to a value in the chart, I am unable to assign a report variable to the parameter I described above 'Identifier'. Now, normally, I would build the query to pull down all of the data, and then assign a 'filter' on that data I guess using the Chart Wizard. However, as I have a sub-query, this isn't going to work in this case.
What I believe I'm looking for, is a way of assigning the parameter 'Identifier' from the current record parameters in a multi-record report.
Best Answer
You have have to try something a little loopy, such as the following:
Reports have a property called Tag
Open the Report in Design View
Open the Property Sheet and Select the 'Other' Tab
The Tag property is on the bottom of the list
You can access that Tag as follows:
Simply replace the
[Identifier]
with eitherReports("Report Name").Tag
orMe.Tag
That way, if you open the query, you will still be prompted to input the UserID
When you open the Report, set
Me.Tag
to whatever number you want. How to you set it in the first place ?On the property sheet, Click
Event Tab
. In theOnLoad Event
, click the three dots (ellipses). Menu pop ups. ClickCode Builder
. VBA opens on with :Now put that setting in (say the UserID is from a form):
Give it a Try !!!