Open report based on DAO recordset - with parameters

[RCurtin]

Hi,
Here is my code:

    WeekEndDate = CDate(Format(InputBox("Please enter the weekend date.", "Enter weekend date"), "long date"))
 
    Set qdefPayReport = CurrentDb.QueryDefs("UnionPayCyclesAll")
    qdefPayReport.Parameters("Weekend") = WeekEndDate
    Set rsPayreport = qdefPayReport.OpenRecordset()
    DoCmd.OpenReport "Pay Report", acPreview

As you can see the users enters a date into an input box - this is a parameter in the query 'UnionPayCyclesAll'. This works fine - it creates the recordset fine. But how do I open the report based on this recordset. When I run the code above - it asks me for the [Weekend] parameter again at the line
DoCmd.OpenReport "Pay Report", acPreview

I just want the user to enter the date once into the input box and for the report to open?

 

[RCurtin]

Does anyone have any ideas?

I don't think that I can use do it by using a where condition in the DoCmd.OpenReport line because my query uses 3 other queries. The query is called "UnionPayCyclesAll" and unions 3 queries that calculate the weekly pay, the bi-weekly and the monthly pay. The [weekend] parameter is used in the three initial queries.

The reason that I want to have the user click a button and enter the date into an input box is so that I can open two reports using this date. I want to open the main pay report and another report to show a list of employees that were assigned to jobs during the specified period where we have not received the hours they worked.

Maybe I am going about this in the wrong way. Would really appreciate any pointers.

 

[Rich]

Use a text box on a form to pass the parameters not an input box, keep the form open until the report is loaded

 

[RCurtin]

Hi Rich,
Thanks for that but I'm still not sure how I should be passing this information to the report. Here is my code now:

    DoCmd.OpenReport "Pay Report", acPreview, , "Weekend =#" & CDate(Format(txtDate.Value, "long date")) & "#"[CODE]

When you press the button to open the report it still asks for the Weekend parameter again? Does it matter that the parameter is not in the final query but one of the queries that one is based on? Or should I go with using a DAO recordset (as in my first post)?

 

[RCurtin]

By the way here is the SQL of the query

[COLOR="SeaGreen"]SELECT calWeeklyPay.EmployeeNum, calWeeklyPay.CandPostDetailsID, calWeeklyPay.NH_Pay, calWeeklyPay.OT1_Pay, calWeeklyPay.OT2_Pay, calWeeklyPay.GrossPay
FROM calWeeklyPay
WHERE (((calWeeklyPay.EndDate)>=[Weekending]-6 Or (calWeeklyPay.EndDate) Is Null) AND ((calWeeklyPay.PayFrequency)=1))[/COLOR]UNION [COLOR="RoyalBlue"]SELECT calBIWeeklyPay.EmployeeNum, calBIWeeklyPay.CandPostDetailsID, Sum(calBIWeeklyPay.NH_Pay) AS SumOfNH_Pay, Sum(calBIWeeklyPay.OT1_Pay) AS SumOfOT1_Pay, Sum(calBIWeeklyPay.OT2_Pay) AS SumOfOT2_Pay, Sum(calBIWeeklyPay.GrossPay) AS SumOfGrossPay
FROM calBIWeeklyPay
WHERE (((calBIWeeklyPay.PayFrequency)=2))
GROUP BY calBIWeeklyPay.EmployeeNum, calBIWeeklyPay.CandPostDetailsID, calBIWeeklyPay.PayCycle[/COLOR]
UNION [COLOR="Orange"]SELECT calMonthlyPay.EmployeeNum, calMonthlyPay.CandPostDetailsID, Sum(calMonthlyPay.NH_Pay) AS SumOfNH_Pay, Sum(calMonthlyPay.OT1_Pay) AS SumOfOT1_Pay, Sum(calMonthlyPay.OT2_Pay) AS SumOfOT2_Pay, Sum(calMonthlyPay.GrossPay) AS SumOfGrossPay
FROM calMonthlyPay
GROUP BY calMonthlyPay.EmployeeNum, calMonthlyPay.CandPostDetailsID, calMonthlyPay.PayCycle[/COLOR];

Here is the SQL of calWeeklyPay - one of the queries the final one is based on - this is the one with the Weekend parameter:

PARAMETERS [B]Weekend[/B] DateTime;
SELECT M_CandidatePostDetails.CandPostDetailsID, M_Hours.WeekEnding, M_CandidatePostDetails.EndDate, M_CandidatePostDetails.EmployeeNum, M_PayRates.PayFrequency, DatePart("ww", [B][Weekend] [/B])AS ExchangeRateWeek, [NormalRate]*[ExchangeRate] AS NormalRateEuro, [OT1]*[ExchangeRate] AS OT1_Euro, [OT2]*[ExchangeRate] AS OT2_Euro, [NormalRateEuro]*[NormalHours] AS NH_Pay, [OT1Hours]*[OT1_Euro] AS OT1_Pay, [OT2Hours]*[OT2_Euro] AS OT2_Pay, [NH_Pay]+[OT1_Pay]+[OT2_Pay] AS GrossPay
FROM (M_CandidatePostDetails LEFT JOIN M_Hours ON M_CandidatePostDetails.EmployeeNum = M_Hours.EmployeeNum) LEFT JOIN (L_Currency RIGHT JOIN M_PayRates ON L_Currency.CurrencyID = M_PayRates.Currency) ON M_CandidatePostDetails.CandPostDetailsID = M_PayRates.CandidatePostID
WHERE (((M_Hours.WeekEnding)=[B] [Weekend][/B] ) AND ((M_PayRates.PayFrequency)=1));

So I need to pass the weekend parameter to a report from a form. I'd rather not have to include the SQL in the VBA.

 

[RCurtin]

Got it working

OK - should have thought of this. I changed the parameter in calWeeklyPay to [Forms]![frmStartup]![txtDate] instead of [Weekend]. The fact that I had a query based on other queries was confusing me.

I also needed to delete Weekend from the parameter list (On menu bar in design view: Query | Parameters..) and the other thing was to have # signs before and after the date in the textbox.

I also found a very good tutorial about parameter queries:
http://office.microsoft.com/en-us/assistance/HA011170771033.aspx