Crosstab based on date range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have read posts here and read all microsoft help I could find about date
range crosstab queries. I have defined parameters and still get error message
that date range field is not valid . I must be missing something. If anyone
knows of an example I would appreciate a link to it. Thank-you
 
Switch your query to SQL View (view menu, from query design).
Copy the entire statement, and post it here.
 
Here it is.

PARAMETERS shift Text ( 255 ), pacereductionarea Text ( 255 ),
pacebeforereduction Long, totalofpacebeforereduction Long, joindate DateTime;
TRANSFORM Count(PaceTbl.PaceBeforeReduction) AS CountOfPaceBeforeReduction
SELECT PaceTbl.Shift, Count(PaceTbl.PaceBeforeReduction) AS [Total Of
Pacebeforereduction], Var(PaceTbl.Joindate) AS VarOfJoindate
FROM PaceTbl
WHERE (((Var(PaceTbl.Joindate)) Between
[forms]![DateRangeProductionandDelayReport]![TxtjoindateStart] And
[Forms]![DateRangeProductionandDelayReport]![TxtjoindateEnd]))
GROUP BY PaceTbl.Shift
PIVOT PaceTbl.PaceReductionArea;
 
Parameters are generally the references to your form controls or input
parameters.
Try:
PARAMETERS [forms]![DateRangeProductionandDelayReport]![TxtjoindateStart]
DateTime,
[Forms]![DateRangeProductionandDelayReport]![TxtjoindateEnd] DateTime;
TRANSFORM Count(PaceTbl.PaceBeforeReduction) AS CountOfPaceBeforeReduction
SELECT PaceTbl.Shift, Count(PaceTbl.PaceBeforeReduction)
AS [Total Of Pacebeforereduction], Var(PaceTbl.Joindate) AS VarOfJoindate
FROM PaceTbl
WHERE (((Var(PaceTbl.Joindate)) Between
[forms]![DateRangeProductionandDelayReport]![TxtjoindateStart] And
[Forms]![DateRangeProductionandDelayReport]![TxtjoindateEnd]))
GROUP BY PaceTbl.Shift
PIVOT PaceTbl.PaceReductionArea;
 
Thank-you, I definately missed the boat on what parameters were required.
Actually looks like I didn't even make it to the dock. Query works fine now.
Eb1mom
 
Back
Top