I
inungh
I pass a date paramter criteria to my query.
using following 2 methods.
The first one pass from form and subform which does not return
recoerds, but second one returns.
I just change the date time criteria from ")>=Date()-(select
SystemRetrieve from tblsystem where systemID=1))" to "Between [forms]!
[frmPIPMain]![dtpStatDate] And [forms]![frmPIPMain]![SP GET PIP LIST
BY EMPLOYEE subform]![PlanStartdate]) AND ((IKB.EmployeeID)=[forms]!
[frmPIPMain]![SP GET PIP LIST BY EMPLOYEE subform]![EmployeeNO])"
I have add parameters to the crosstab query.
Your help is great appreciated,
Here attached are the SQL code.
PARAMETERS [forms]![frmPIPMain]![SP GET PIP LIST BY EMPLOYEE subform]!
[EmployeeNO] Text ( 255 ), [forms]![frmPIPMain]![dtpStatDate]
DateTime, [forms]![frmPIPMain]![SP GET PIP LIST BY Employee subform]!
[PlanStartdate] DateTime;
TRANSFORM Count(IKB.Name) AS CountOfName
SELECT IKB.EmployeeID, CDate(Int([Date Closed]))-Weekday(CDate([date
closed]))+6 AS WeekEnding, Count(IKB.Name) AS [Total Of Name]
FROM IKB
WHERE (((CDate(Int([Date Closed]))-Weekday(CDate([date closed]))+6)
Between [forms]![frmPIPMain]![dtpStatDate] And [forms]![frmPIPMain]!
[SP GET PIP LIST BY EMPLOYEE subform]![PlanStartdate]) AND
((IKB.EmployeeID)=[forms]![frmPIPMain]![SP GET PIP LIST BY EMPLOYEE
subform]![EmployeeNO]) AND ((IKB.EDA) Not Like "*R1*" And (IKB.EDA)
Not Like "*R2*" And (IKB.EDA) Not Like "*R3*"))
GROUP BY IKB.EmployeeID, CDate(Int([Date Closed]))-Weekday(CDate([date
closed]))+6
PIVOT IKB.Answer In ("No Answer","Correct","Incorrect","Not
Presented");
PARAMETERS [forms]![frmPIPMain]![SP GET PIP LIST BY EMPLOYEE subform]!
[EmployeeNO] Text ( 255 ), [forms]![frmPIPMain]![dtpStatDate]
DateTime, [forms]![frmPIPMain]![SP GET PIP LIST BY Employee subform]!
[PlanStartdate] DateTime;
TRANSFORM Count(IKB.Name) AS CountOfName
SELECT IKB.EmployeeID, CDate(Int([Date Closed]))-Weekday(CDate([date
closed]))+6 AS WeekEnding, Count(IKB.Name) AS [Total Of Name]
FROM IKB
WHERE (((CDate(Int([Date Closed]))-Weekday(CDate([date closed]))
+6)>=Date()-(select SystemRetrieve from tblsystem where systemID=1))
AND ((IKB.EmployeeID)=[forms]![frmPIPMain]![SP GET PIP LIST BY
EMPLOYEE subform]![EmployeeNO]) AND ((IKB.EDA) Not Like "*R1*" And
(IKB.EDA) Not Like "*R2*" And (IKB.EDA) Not Like "*R3*"))
GROUP BY IKB.EmployeeID, CDate(Int([Date Closed]))-Weekday(CDate([date
closed]))+6
PIVOT IKB.Answer In ("No Answer","Correct","Incorrect","Not
Presented");
using following 2 methods.
The first one pass from form and subform which does not return
recoerds, but second one returns.
I just change the date time criteria from ")>=Date()-(select
SystemRetrieve from tblsystem where systemID=1))" to "Between [forms]!
[frmPIPMain]![dtpStatDate] And [forms]![frmPIPMain]![SP GET PIP LIST
BY EMPLOYEE subform]![PlanStartdate]) AND ((IKB.EmployeeID)=[forms]!
[frmPIPMain]![SP GET PIP LIST BY EMPLOYEE subform]![EmployeeNO])"
I have add parameters to the crosstab query.
Your help is great appreciated,
Here attached are the SQL code.
PARAMETERS [forms]![frmPIPMain]![SP GET PIP LIST BY EMPLOYEE subform]!
[EmployeeNO] Text ( 255 ), [forms]![frmPIPMain]![dtpStatDate]
DateTime, [forms]![frmPIPMain]![SP GET PIP LIST BY Employee subform]!
[PlanStartdate] DateTime;
TRANSFORM Count(IKB.Name) AS CountOfName
SELECT IKB.EmployeeID, CDate(Int([Date Closed]))-Weekday(CDate([date
closed]))+6 AS WeekEnding, Count(IKB.Name) AS [Total Of Name]
FROM IKB
WHERE (((CDate(Int([Date Closed]))-Weekday(CDate([date closed]))+6)
Between [forms]![frmPIPMain]![dtpStatDate] And [forms]![frmPIPMain]!
[SP GET PIP LIST BY EMPLOYEE subform]![PlanStartdate]) AND
((IKB.EmployeeID)=[forms]![frmPIPMain]![SP GET PIP LIST BY EMPLOYEE
subform]![EmployeeNO]) AND ((IKB.EDA) Not Like "*R1*" And (IKB.EDA)
Not Like "*R2*" And (IKB.EDA) Not Like "*R3*"))
GROUP BY IKB.EmployeeID, CDate(Int([Date Closed]))-Weekday(CDate([date
closed]))+6
PIVOT IKB.Answer In ("No Answer","Correct","Incorrect","Not
Presented");
PARAMETERS [forms]![frmPIPMain]![SP GET PIP LIST BY EMPLOYEE subform]!
[EmployeeNO] Text ( 255 ), [forms]![frmPIPMain]![dtpStatDate]
DateTime, [forms]![frmPIPMain]![SP GET PIP LIST BY Employee subform]!
[PlanStartdate] DateTime;
TRANSFORM Count(IKB.Name) AS CountOfName
SELECT IKB.EmployeeID, CDate(Int([Date Closed]))-Weekday(CDate([date
closed]))+6 AS WeekEnding, Count(IKB.Name) AS [Total Of Name]
FROM IKB
WHERE (((CDate(Int([Date Closed]))-Weekday(CDate([date closed]))
+6)>=Date()-(select SystemRetrieve from tblsystem where systemID=1))
AND ((IKB.EmployeeID)=[forms]![frmPIPMain]![SP GET PIP LIST BY
EMPLOYEE subform]![EmployeeNO]) AND ((IKB.EDA) Not Like "*R1*" And
(IKB.EDA) Not Like "*R2*" And (IKB.EDA) Not Like "*R3*"))
GROUP BY IKB.EmployeeID, CDate(Int([Date Closed]))-Weekday(CDate([date
closed]))+6
PIVOT IKB.Answer In ("No Answer","Correct","Incorrect","Not
Presented");