Header, Detail Problem

[Grumm]

I have a query that ask the user to enter Me!MaxWeek. The query correctly queries the data and brings bback dat Where Week <=Me!(MaxWeek).

This information is held in the detail section. What I want to do is display the Max Week or the number the user entered in the header. I have tried a number of ways but cant seem to get it to work.

Any Ideas????

 

[PaulBricker]

If your query is a parameter query you would put a textbox in the Header section of the report that duplicates the parameter in your query. If your query parameter is
=[Enter MaxWeek]
then the Control Source for the textbox in your Report is going to say
=[Enter MaxWeek]

If you don't use a parameter, post a little more info about how your user enters the value for MaxWeek.


Paul

 

[Grumm]

Hi - Given that a go but still cant seem to solve problem.

This is the sql:
SELECT Contract.ProjectID, First(Company.Logo) AS FirstOfLogo, Contract.ProjectName, ContractWorks.ItemNumber, ContractWorks.ItemDescription, LookUpDetail.Description, ContractWorks.ItemCost, Sum(Nz([TotalCost],0)) AS SumCost
FROM (Company INNER JOIN (((Contract INNER JOIN (LookUpDetail AS LookUpDetail_1 INNER JOIN (LookUpDetail INNER JOIN ContractWorks ON LookUpDetail.LookUpDetailID = ContractWorks.WorkGroupID) ON LookUpDetail_1.LookUpDetailID = ContractWorks.WorkTypeID) ON Contract.ProjectID = ContractWorks.ProjectID) LEFT JOIN AllocationHeader ON ContractWorks.ProjectItemID = AllocationHeader.ProjectItemID) LEFT JOIN ContractSchedule ON AllocationHeader.ProjectWeekID = ContractSchedule.ProjectWeekID) ON Company.CompanyID = Contract.CompanyID) LEFT JOIN AllocationDetails ON AllocationHeader.AllocationID = AllocationDetails.AllocationID
WHERE (((ContractSchedule.ProjectWeek)<=[Me]![ToProjectWeek]))
GROUP BY Contract.ProjectID, Contract.ProjectName, ContractWorks.ItemNumber, ContractWorks.ItemDescription, LookUpDetail.Description, ContractWorks.ItemCost, AllocationHeader.ProjectItemID
HAVING (((Contract.ProjectID)=[Forms]![ManagementInformationMain]![ProjectID]))
ORDER BY ContractWorks.ItemNumber;


When you run the report the user is prompted for a Me!ToProjectWeek.

I want this value to appear in the header section of the report.

Cheers

 

[PaulBricker]

Well, assuming you are getting the value for ToProjectWeek from an open form then you would set the control source of the textbox in your Report Header to

=Forms!FormName!ToProjectWeek

changing the FormName argument to your form name.

Paul

 

[Grumm]

Hi Paul - thanks for your help so far. I have the Me! embeded in the sql and the user is prompted via enter parameter value from the Query not the form - does this make a difference. I have a button on a form that fires up the query - it is only then that the query asks for the ToProjectWeek.

Not sure this makes sence.

 

[PaulBricker]

When prompted, exactly what does the parameter box say? Is it Me!ToProjectWeek?


Paul

 

[Grumm]

The windows box is called: Enter Parameter Value and the text is Me!ToProjectWeek with the entry box below.

Cheers

 

[PaulBricker]

Try a textbox with this as the Control Source
=Me!ToProjectWeek

I've never done it this way but it should, in theory, work.

Paul

 

[PaulBricker]

I'm curious why you are doing it this way and not just using a straight parameter like
<=[Please enter ToProjectWeek Value]

rather than the way you have it now?

Paul

 

[Grumm]

Hi Paul - I have tried putting a text box in the header and entering Me!ToProjectWeek in the control and it asks me gor a further parameter.

Your second comment is interesting as I am no expert in the report side and could have approached this the wrong way.

What is the difference netween what I have done and what you suggested in <=[Please enter ToProjectWeek Value]

What is a straight parameter

Again thanks for your time

 

[PaulBricker]

Well, usually you use the Me! keyword when you want the query to look at a value on a Form (and avoid the user having to enter anything). So if you have an open form with a date field that has a value in it, you put
=Forms!FormName!DateFieldName
on the criteria line and when the query runs, it looks at the Form for the date value in the textbox and the user doesn't have to input anything.
A straight parameter, is one that is descriptive so that the user knows right away what value is being looked for. In this case,
<= [Please Enter ToProjectWeek Value]
is a lot more user friendly than
<= Me!ToProjectWeek

They both do exactly the same thing when run but the one is easier to understand the other.
Secondly, the Me! keyword is an Access Reserved word and that may be causing problems getting your parameter to show in the Report.
The Control Source for your Textbox has to have the = sign in front of the expression to work. So your Control source would have to be
=Me!ToProjectWeek

but as I say, the Me keyword may be messing things up. You can try changing the parameter and see if that helps.

Paul

 

[Grumm]

Paul, The problem I have with this one is that the form does not have any dates or weeks on it - it is basically just a screen with a load oflinks to reports that contain parameters.

I will try and get round this by selecting the MAX Project week in the query. I know its not pretty but it should work.

I am using form based parameters foir this form for the project only.

Thanks for your help

Grum