Home / Questions / 350992
Accepted
Ricardo Martín
Asked: 2020-05-01 06:01:09 +0800 CST 2020-05-01 06:01:09 +0800 CST

VBA Access - DSum function does not recognize double criteria

  • 772

Hi guys! I hope everybody is fine.

I have a table [BALANCE_MOV] (Inventory Movement Balance) with the following fields:

*[ID]: product (text)

*[INCOME]: amount entered for each product (number)

*[ALM_ING]: input warehouses (text)

With which I want to make a query to know THE ENTERED QUANTITY OF EACH ID, ACCORDING TO A PARTICULAR WAREHOUSE NAMED "NNPU" . For this I use the following fields:

*[POINT]: warehouse maintainer (text, in another table)

*[IDP]: products in stock (text, in another query)

*[INCOMES_X_ALMACEN]: sum field...with the problem.

To do the above, I plan to use the DSum function, similar to the SUMAR.IF.SET of excel from what I understand , which works if I execute it with only one criterion : either to add the quantity according to an ID, or the warehouse in question . BUT if I combine them, it DOES NOT work , and it calculates a total for which I cannot discover the reason.

This is the syntax I use; Access does not recognize an error in it, it just does not calculate correctly. Is there something wrong? Thank you guys in advance. 

INGRESOS_X_ALMACEN: DSuma("[INGRESOS]";"[BALANCE_MOV]";"[ID]='" & [IDP] & "'" Y "[ALM_ING]='NNPU'")
vba

1 Answers

  1. Best Answer

    What I don't understand is why it works with only one criteria

    Taking the example of Microsoft

    curX = DSuma("[Freight]", "Orders", "[ShipCountryRegion] = 'UK' Y [ShippedDate] > #1-1-95#")

    We see that the third parameter of the function, the one that performs the filter, is a string. In your case, this stringis not well constructed since it is not correctly concatenated, notice that it Yis left off the hook

    "[ID]='" & [IDP] & "'" Y "[ALM_ING]='NNPU'")

    On the other hand, the operator Yshould be ANDand should be like this:

    INGRESOS_X_ALMACEN: DSuma("[INGRESOS]";"[BALANCE_MOV]";"[ID]='" & [IDP] & "' AND [ALM_ING]='NNPU'")
    • 1