DSum in a Query

[Guest]

For months I've been trying to work with Dsum. But, I'm just not getting it.
Pls help !

I have a query named qryAttend1 with 3 fields: ss, Chg, qDate

The original table containing all the info is called Attendance, but I
subqueried the info in order to change the field names so they don't match
the originating Table.

Employee absences drop off after a year and they also can earn time back,
but I need the query to calculate depending on the total absences before the
current absence. So, I tried to use the following expression as the 4th
field in the query. But, I keep getting "compile error, in query expression
'DSum ..."

PTotalSum("[Timecharged]","Attendance","[AttnSSNumber]=[ss] and [Date] <
[qdate] and > date()-365")

I've also tried the expression with just the SSnumber and get the same error.

Is the syntax wrong, am I putting it in the wrong place....???

The total sql of the query looks like this:

SELECT qryAttendance.ss, qryAttendance.qdate, qryAttendance.chg,
qryAttendance.LD, qryAttendance.ND,
DSum("[Timecharged]","Attendance","[AttnSSNumber]=[ss] and [Date] < [qdate]
and > date()-365") AS PTotal
FROM qryAttendance
ORDER BY qryAttendance.qdate;

 

[Tom Lake]

Employee absences drop off after a year and they also can earn time back,

but I need the query to calculate depending on the total absences before
the
current absence. So, I tried to use the following expression as the 4th
field in the query. But, I keep getting "compile error, in query
expression
'DSum ..."

PTotalSum("[Timecharged]","Attendance","[AttnSSNumber]=[ss] and [Date] <
[qdate] and > date()-365")

I've also tried the expression with just the SSnumber and get the same
error.

Is the syntax wrong, am I putting it in the wrong place....???

Try this:

PTotalSum("[Timecharged]","Attendance","[AttnSSNumber]='" & [ss] & "' and
[Date] < #" & [qdate] & "# and [Date] > date()-365")

Tom Lake

 

[Guest]

Thank you Tom, you have saved my sanity for one more day.

Employee absences drop off after a year and they also can earn time back,
but I need the query to calculate depending on the total absences before
the
current absence. So, I tried to use the following expression as the 4th
field in the query. But, I keep getting "compile error, in query
expression
'DSum ..."

PTotalSum("[Timecharged]","Attendance","[AttnSSNumber]=[ss] and [Date] <
[qdate] and > date()-365")

I've also tried the expression with just the SSnumber and get the same
error.

Is the syntax wrong, am I putting it in the wrong place....???

Try this:

PTotalSum("[Timecharged]","Attendance","[AttnSSNumber]='" & [ss] & "' and
[Date] < #" & [qdate] & "# and [Date] > date()-365")

Tom Lake