need help with dsum syntax please

[JumpinJimRivers]

I can't ever get these things right.


DSum("[retainage]","[sub billing items monthly]","[sub-po number]=" & [sub-po number] & " AND [date]>'" & [date paid] & "'&quot


thanks in advance

 

[jimmythegeek]

With dates, you have to use pound signs:

DSum("[retainage]","[sub billing items monthly]","[sub-po number]=" & [sub-po number] & " AND [date]>#" & [date paid] & "#&quot Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[scking]

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'&quot

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] & "'&quot
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] & "#&quot

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

 

[JumpinJimRivers]

Thanks Jimmy, that's what I needed to know.