Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is there a relationship defined between the tables? –  Eric Humphrey - lotsahelp 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
add comment

1 Answer

up vote 3 down vote accepted

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 !!!

share|improve this answer
    
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! –  John Wordsworth Mar 27 '11 at 11:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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