DSUM with dual criteria

[Peter Durman]

I am writing a short procedure to query DSUM subtotals of a table TaRegister_New and then write the answer in a text box. I am having trouble with the dual criteria. Putting the criteria into a sting made checking easier. I have been trying for a couple of days.



strSQL = "([IDOwner] = " & intOwner & " ) AND ([ID_Company] = " & intCompany & ")"
strSQL2 = "([IDOwner] = " & intOwner & " )"
strSQL3 = "([ID_Company] = " & intCompany & ")"

dblCumulativeShares = DSum("nz(ShNumBuy) * Nz(Split)", "TaRegister_New", strSQL)

I want the answer from strSQL, the dual criteria. There is no error message, but the result is 0, the criteria is wrong.
Using strSQL2 or strSQL3, i get a correct answer for that query, but without the dual criteria, the answer is meaningless.
Using Deebug.Print, strSQL prints out as
([IDOwner] = 2 ) AND ([ID_Company] = 166)
, with the code correctly picking up values for IDOwner and ID_Company. They are integers.

I have tried different formatting for the Criteria, but I'm getting nowhere. I am doing something wrong with the format of AND.
Any help appreciated.

[Gicu]

Please try this:

DSum("nz(ShNumBuy) * Nz(Split)", "TaRegister_New", "[IDOwner] = " & intOwner & " AND [ID_Company] = " & intCompany)

Cheers,

[Peter Durman]

Thanks. No it still doesn't work. No error code, just gives an answer of zero.

[Welshgasman]

So paste that into a sql window.
What do you get?

[Peter Durman]

Yes I tried.
I generated the SQL in a query, then substituted hard values (2 and 166) for variables.
Now it doesn't work

[Peter Durman]

Went back and reviewed. The code is correct. The CALL procedure entered the wrong data. Thank you for the prompt.