edit crosstab query by form

[MICKI0220]

I have a crosstab query that I need the date to be taken from a form when it runs.
This is the current code.

TRANSFORM Sum(HrsForCrossTab.Hours) AS SumOfdHoursWorked_qty
SELECT HrsForCrossTab.EmployeeId, HrsForCrossTab.JobNo
FROM HrsForCrossTab
GROUP BY HrsForCrossTab.EmployeeId, HrsForCrossTab.JobNo
PIVOT "Date" & DateDiff("d",#8/5/2012#,[Date_hr]+1) & [EarningCode];

How do I get the date that is in this code to be whatever is in a forms text box such as [frmTest]![testdate]

Thanks in advance

Micki

 

[dhookom]

You must set the data type of the parameters with SQL like:
[CODE sql]
PARAMETERS [Forms}![frmTest]![testdate] DateTime;
TRANSFORM Sum(HrsForCrossTab.Hours) AS SumOfdHoursWorked_qty
SELECT HrsForCrossTab.EmployeeId, HrsForCrossTab.JobNo
FROM HrsForCrossTab
GROUP BY HrsForCrossTab.EmployeeId, HrsForCrossTab.JobNo
PIVOT "Date" & DateDiff("d",[Forms]![frmTest]![testdate],[Date_hr]+1) & [EarningCode];
[/code]
You might also want to set the column headings property of the crosstab query.

Duane
Hook'D on Access
MS Access MVP