Problems with DSum criteria

[patjones]

Hi:

This is sort of the second part to an earlier question I asked about summing. It's pretty simple. My code is:

Me!txtLostDaysNoPaySeasonal = DSum("DateDiff('d', [tblWC]![fldDateOfNoPayBegin], [tblWC]![fldDateReturned])", "tblWC", "[fldPayDistCode] = 'BK21312'")

In other words, it's supposed to take the difference in dates for each record, then sum that difference over all records that match the criteria. The result goes in a locked text box on my main form.

Instead, I get an error that says The expression you entered as a query parameter produced this error: '[fldPayDistCode]'

This is the exact syntax shown in the Access help pages from MS, but it's not working for me. Any ideas?

Pat

[patjones]

Hi:

This is sort of the second part to an earlier question I asked about summing. It's pretty simple. My code is:

Me!txtLostDaysNoPaySeasonal = DSum("DateDiff('d', [tblWC]![fldDateOfNoPayBegin], [tblWC]![fldDateReturned])", "tblWC", "[fldPayDistCode] = 'BK21312'")

In other words, it's supposed to take the difference in dates for each record, then sum that difference over all records that match the criteria. The result goes in a locked text box on my main form.

Instead, I get an error that says The expression you entered as a query parameter produced this error: '[fldPayDistCode]'

This is the exact syntax shown in the Access help pages from MS, but it's not working for me. Any ideas?

Pat

OK, well I figured out the problem...[fldPayDistCode] resides in tblEmployees, not tblWC. But that presents another problem. tblWC and tblEmployees are connected via a one-to-many relationship through the field "fldERN" (that's Employee Reference Number for short). However, I want to pick out records in tblWC based on criteria for fldPayDistCode which which doesn't have anything to do with fldERN except that they are in the same table.

Can I still do this via the DSum function?

[patjones]

OK, well I figured out the problem...[fldPayDistCode] resides in tblEmployees, not tblWC. But that presents another problem. tblWC and tblEmployees are connected via a one-to-many relationship through the field "fldERN" (that's Employee Reference Number for short). However, I want to pick out records in tblWC based on criteria for fldPayDistCode which which doesn't have anything to do with fldERN except that they are in the same table.

Can I still do this via the DSum function?

Figured this one out also. I simply built a query called "qryTest" which picks out [tblEmployees]![fldPayDistCode] = "BK21312" and then based the DSum function on that query:

DSum("DateDiff('d', [tblWC]![fldDateOfNoPayBegin], [tblWC]![fldDateReturned])", "qryTest")

I hope this helps other people at least! : - )

[mlcampeau]

I'm glad you got it to work! Sorry none of the "Experts" got around to answering your question before you figured it out. Thanks for posting your results for other members to see.