CODEWITHSUNDEEP
ms-accessvba
Ish
Ish

Reputation: 671

DSum function in vba

I want to display sum of a column in a textbox when I hit a button. But it is giving me a compile error: "Wrong number of arguements or invalid property assignment"

The below code is implemented in vba.

Here is the code that I used:

Text19 = Nz(DSum("Total_Units", "6_Provincial_SUB", , "[BudgetYear] =" & [Combo5] & " And [Program_Name] ='" & Replace([Combo7], "'", "''") & "'"), 0)

Upvotes: 0

Views: 2815

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112299

DSum has three parameters. You have four. Drop the extra comma

Text19 = Nz(
    DSum(
        "Total_Units",
        "6_Provincial_SUB", <==== Here I dropped a comma (,)
        "[BudgetYear] =" & [Combo5] & " And [Program_Name] ='" &
            Replace([Combo7], "'", "''") & "'"
    ),
    0
)

When things like this happen, I try to find the problem by indenting the expression like above in order to find matching braces etc. Without line continuation character "_" this will not work of cause, but it gives you an idea of the structure of the expression.

I have these functions in my library. They help me in the creation of SQL strings

Public Function SqlStr(ByVal s As String) As String
'Input: s=""      Returns: NULL
'Input: s="abc"   Returns: 'abc'
'Input: s="x'y"   Returns: 'x''y'

    If s = "" Then
        SqlStr = "NULL"
    Else
        SqlStr = "'" & Replace(s, "'", "''") & "'"
    End If
End Function

Function Build(ByVal s As String, ParamArray args()) As String
'Build("FirstName = {0}, LastName = {1}","John","Doe") --> 
'"FirstName = John, LastName = Doe".
'"\n" is expanded with vbCrLf.

    Dim i As Long

    s = Replace(s, "\n", vbCrLf)
    For i = 0 To UBound(args)
        s = Replace(s, "{" & i & "}", Nz(args(i)))
    Next i
    Build = s
End Function

By using them, your SQL would be constructed like this

sql = Build("[BudgetYear] = {0} AND [Program_Name] = {1}", _
            Combo5, SqlStr(Combo7))

Upvotes: 2

Me.Name
Me.Name

Reputation: 12544

You have a comma too many after the domain parameter:

  "6_Provincial_SUB", ,

That would make the space the criteria parameter, and the actual criteria an unknown fourth parameter.

Upvotes: 0

Related Questions