By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Problems with DSum criteria

patjones
Expert 100+
P: 931
Hi:

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

Expand|Select|Wrap|Line Numbers
  1. 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
Sep 19 '07 #1
Share this Question
Share on Google+
3 Replies


patjones
Expert 100+
P: 931
Hi:

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

Expand|Select|Wrap|Line Numbers
  1. 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?
Sep 19 '07 #2

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. DSum("DateDiff('d', [tblWC]![fldDateOfNoPayBegin], [tblWC]![fldDateReturned])", "qryTest")
I hope this helps other people at least! : - )
Sep 19 '07 #3

Expert 100+
P: 296
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.
Sep 19 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.