Select the DSum and hit F1. This comes directly from help.
The following example returns the summation of the Freight field for orders shipped to the United Kingdom. The domain is an Orders table. The criteria argument restricts the resulting set of records to those for which ShipCountry equals UK.
Dim curX As Currency
curX = DSum ("[Orders]![Freight] ", "[Orders]", "[ShipCountry] = 'UK'"
What information do you want the sum of? Assuming "[retainage]" is a table you did not specify the field. Assuming it is a field, you did not specify a table.
What are the conditions which must be met?
"[sub-po number]=" & [sub-po number] & " AND [date]>'" & [date paid] & "'"
1) Why would you have the requirement that it equals itself. This appears to say if a field in a table equals itself. If this is supposed to be equal to a control with the same name this can confuse Access, and anyone else.
2) If [date] is a field (an assumption since it enclosed in brackets) and [date paid] is also a field (an assumption since it is in brackets, then are these from different tables or what. If [date paid] is a control it would help to reference it as Me.[date paid]. Again if [date] is a field it could confuse Access since 'date' is a keyword for the function 'Date'. Then if the [date] is a field it must be a text field the way you have described it. If it is a date field in the table then you need to look for
AND [date]>#" & [date paid] & "#"
3) You have no variable for an assignment.
Tips:
1) Name controls without spaces. While Access takes spaces you frequently end up having problems in the long run.
2) Prefix controls with the type of control: Like 'txt' for textboxes and 'cbo' for control boxes.
3) Prefix controls in code for a form with Me. which will resolve it's location to Access and cause your code to speed up.
4) Do not name fields the same as keywords.
Steve King
Growth follows a healthy professional curiosity
