John,
The DSum is an expression in an Update Query. I'm hoping to use the query
from a couple of different places as follows:
I have Client Assignments which are made up of many Segments. When a user
completes a Segment they 1) enter the hours worked in a bound text box on a a
"Segment Complete Pop Up" form for quick entry, or 2) open the Assignment
form which has a subform with the Segments list and update the segment from
there.
In either instance I was going to run an update query after the Segment
record was saved that would update the Assignment's Total Actual Hours (thus
far) with the sum of the Segments completed thus far. Now, if the update was
always to take place from the Assignment form with the Segments subform, I
would have used VBA to DSum the Segments and put the total in the
Assignment's bound text box, but because the "pop up" form does not have the
Assignment fields showing, I thought using an update query would work to
update the field in the table. (I've not had much luck in updating table
fields via VBA when the fields were not "on screen" in a form or something.
Because the Assignment Actual Hours is used in a variety of ways in Budget
(Hours) variance analysis, billing (sometimes billed at the Segment Level,
sometimes billed at the Assignment level), and others, I believe saving the
value in the Assignment table would be more functional than always relying on
a unbound computed amount.
Well that was kind of long-winded...
Thank you for your help,
Robin
John W. Vinson said:
What's the context, Robin? Where and how are you using the DSum() expression?
I take it that the form is open at the time you're using this?
John W. Vinson [MVP]
John,
Sorry to have omitted that info.
-The error is: "The expression you entered contains invalid syntax" "You
may have entered a comma without a preceding value or identifier"
And I cannot leave the control with the formula still in it.
-The criteria form fields are all bound, text boxes. (No combos or other
types) No criteria fields are null and all are part of the Primary Key for
the Client Segment table.
-The CSActualHrs is a bound numeric field, Single/Fixed 2
Thank you for your response,
Robin
John W. Vinson said:
Hello,
I'm trying to write a DSum formula with 4 criteria. I've tried several
variations but with no luck. I read Allen Brown's web page on this but can't
quite apply it. Any help would be appreciatied.
My fifth and latest attempt was (all criteria fields are text):
DSum ("[CSActualHrs]" , "ClientSegment",
" [ClientSegment]![ClientID] = """ & Forms![fmCS]![ClientID] """ AND
[ClientSegment]![EngagementID] = """ & Forms![fmCS]![EngagementID] & """ AND
[ClientSegment]![EngagementYr] = """ & Forms![fmCS]![EngagementYr] & """ AND
[ClientSegment]![AssignmentID] = """ & Forms![fmCS]![AssignmentID] & """")
I've used the 3-quote and 4-quote combos on some DLookups with fewer
criteria that work.
Thank you for any help,
Robin
In what way does it "not work"? Error message? #Error as a result? No data?
Incorrect data?
Are any of these fields Lookup fields? Are the form controls combo boxes or
textboxes? If combos, what is the value and datatype of the bound column?
Are any of the criteria NULL?
