Date Criteria

[galin]

Hi folks,

I am trying to run a query which has to display Sum after specific
date.
I have
ID, Name, Date, PAYCODE and Paycode_hours

I want to run a query when I specify a date range lets say >1998
the Sum of PAYCODE (which can be REG,OVT,VAC..in my case REG)is
displayed in PAYCODE_Hours.

I specified the date (>1998), then I insert a criteria in the PAYCODE
(REG) and then I group by SUM Paycode_hours. What I am getting is the
hours worked for every single day, but not the SUM. I get the Sum only
if I delete the Date field.

This is my SQL code

SELECT Qkronhist.ID, Qkronhist.NAME, Qkronhist.PAYCODE_1,
Sum(Qkronhist.PAYCODE_1H) AS SumOfPAYCODE_1H
FROM Qkronhist
GROUP BY Qkronhist.ID, Qkronhist.NAME, Qkronhist.DATE,
Qkronhist.PAYCODE_1
HAVING (((Qkronhist.ID)=227) AND ((Qkronhist.DATE)>1997) AND
((Qkronhist.PAYCODE_1)="REG"));

I unchecked the Data box so it woud be invisible.

Please, help how to solve this issue

Galin

 

[Douglas J. Steele]

Is Date the actual name of the field in your table? Is so, try renaming it,
as Date is a reserved word, and using reserved words can lead to all sorts
of problems.

If it's not possible to rename the field, see whether putting square
brackets around the field name helps:

SELECT Qkronhist.ID, Qkronhist.NAME, Qkronhist.PAYCODE_1,
Sum(Qkronhist.PAYCODE_1H) AS SumOfPAYCODE_1H
FROM Qkronhist
GROUP BY Qkronhist.ID, Qkronhist.NAME, Qkronhist.[DATE],
Qkronhist.PAYCODE_1
HAVING (((Qkronhist.ID)=227) AND ((Qkronhist.[DATE])>1997) AND
((Qkronhist.PAYCODE_1)="REG"));

And does your Date field just contain the year? If it's an actual date, then
your comparison isn't going to work: you'll need
(Year(Qkronhist.[DATE])>1997)

 

[Pieter Wijnen]

call me oldfashoined but I'm still faithful to Hungarian notation & try to
avoid using keywords in tables (fields) which are prone to be reserved in
*any* db system
ie
limiting (field/table) names to uppercase letters & underscore

Pieter

Is Date the actual name of the field in your table? Is so, try renaming it,
as Date is a reserved word, and using reserved words can lead to all sorts
of problems.

If it's not possible to rename the field, see whether putting square
brackets around the field name helps:

SELECT Qkronhist.ID, Qkronhist.NAME, Qkronhist.PAYCODE_1,
Sum(Qkronhist.PAYCODE_1H) AS SumOfPAYCODE_1H
FROM Qkronhist
GROUP BY Qkronhist.ID, Qkronhist.NAME, Qkronhist.[DATE],
Qkronhist.PAYCODE_1
HAVING (((Qkronhist.ID)=227) AND ((Qkronhist.[DATE])>1997) AND
((Qkronhist.PAYCODE_1)="REG"));

And does your Date field just contain the year? If it's an actual date, then
your comparison isn't going to work: you'll need
(Year(Qkronhist.[DATE])>1997)