I am using Microsoft Access 2007 and I have a query which looks something like this;

PARAMETERS Identifier TEXT(255);

  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.

  • Is there a relationship defined between the tables? Mar 24 '11 at 13:42
  • I think you're gonna have to go old school ADO on this instead of relying on the wizards.
    – jcolebrand
    Mar 24 '11 at 15:05

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:

Reports("Report Name").Tag

Simply replace the [Identifier] with either Reports("Report Name").Tag or Me.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 the OnLoad Event, click the three dots (ellipses). Menu pop ups. Click Code Builder. VBA opens on with :

Private Sub Report_Load()

End Sub

Now put that setting in (say the UserID is from a form):

Private Sub Report_Load()

   Me.Tag = Forms("Form Name")("Field Name")

End Sub

Give it a Try !!!

  • Just to note, I had problems initially getting this to work as it didn't seem to like having "Reports("Report Name").Tag" in an SQL query. However, after some digging, I found that the following worked; [Reports]![My Report Name]![Tag]. After a bit more bashing around, I then found that I can actually access the properties of the item being viewed as it's also in a text box on the page. My final solution was to use the string [Reports]![My Report Name]![Text Field Name] in my query. It now uses the TextField on that record to complete the query. Couldn't have got there without you! Mar 27 '11 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.