Using variables with DSum() in the critera section 1

[deulla]

I was playing with some code and I tried to use variables in the DSum() function...I managed to get the expression and the domain to work, but for the life of me I can't get the critera to understand the variable.


Dim tce, aNme, nmi, cn, c3
cN = "f3"
tce = "table1"
aNme = Array("[f3]&quot
nmi = aNme(0)

If IsNull(DSum("[f3]", "table1", "[f3] = 3&quot) = True Then
MsgBox "empty"
Else:
---->>> cn = DSum([nmi], [tce], &quot;cN = 1&quot<<<---
End If

In this code the first DSum works...(no variables)
The second uses 3 variables, nmi, tce and cN. nmi and tce are recognized and work. cN returns everything except what the function wants. I tried (cN) [cN] &quot;cN&quot; 'cN' all with and without the '=1' after.
I'm beginning to think that VB access will not allow a variable here no matter what.

 

[Steve101]

Deulla,

The DSum function is designed to sum up an expression from a Domain, which loosely interpreted, means to aggregate data from within a table or query. As such, your Expression and Criteria should both reference items within the referenced domain.

Notwithstanding this, all three arguments within the DSum function can be variables; for example:

Dom = &quot;tblMyTable&quot;
Expr = &quot;[f1] + [f2]&quot;
Crit = &quot;[f3] >= 123 AND [f4] = 'Freddy'&quot;
PersonTotal = dsum(Expr, Dom, Crit)

should compute according to the Dom, Expr, Crit expressions. This example assumes that f1, f2, f3, f4 exist in the table tblMyTable, with f1, f2,f3 being numeric, f4 being Text.

You can build these expressions any way you like in code, providing they result in essentially meaningful SQL components.

I'm not sure why you've attempted to use aNme and the array function here, but they are not appropriate in this situation.

Hope that this helps,

Cheers,
Steve

 

[deulla]

I guess at times I just need someone to point out the obvious to me.

Your solution works great, thanks Steve101

Deulla