date time range criteria

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");
 
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");
Click to expand...

it is me again, It is a typo error.
Sorry, Thanks again,
 
J

Jerry Whittle

I find it best to create a query with the needed tables, fields, and criteria
(including parameters) first to gather up the records. Get it working right.

Then create a crosstab query based on this first query. Trying to do too
much in a crosstab seems to bring out strange problems.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

inungh said:
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");
Click to expand...
 
Top