DSum with multiple criteria

[RichardM]

I have a problem using DSum in query with multiple criteria.
I have a query with following fields OrderNo and ItemGroupCode. On third
field I added following function:
PriceSum: DSum("[Price]";"OrderItems";("[ItemGroupCode]= '" & [ItemGroupCode]
& "'" And "[OrderNumber]=" & [OrderNumber]))
This should go into query OrderItems and sum up the Price for in query
specified OrderNumber (number field) and ItemGroupCode (Text field).
In some odd reason it sums sup all rows not according to criteria specified
rows. If I Dsum with one criteria it works but not with two.

Could please any tell me where is the problem.

Many Thanks
Rich

 

[MGFoster]

I have a problem using DSum in query with multiple criteria.
I have a query with following fields OrderNo and ItemGroupCode. On third
field I added following function:
PriceSum: DSum("[Price]";"OrderItems";("[ItemGroupCode]= '" & [ItemGroupCode]
& "'" And "[OrderNumber]=" & [OrderNumber]))
This should go into query OrderItems and sum up the Price for in query
specified OrderNumber (number field) and ItemGroupCode (Text field).
In some odd reason it sums sup all rows not according to criteria specified
rows. If I Dsum with one criteria it works but not with two.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It is incorrectly formatted. Try this:

DSum("Price", "OrderItems", "ItemGroupCode = '" & ItemGroupCode & "' AND
OrderNumber = " & OrderNumber)

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSfAyoYechKqOuFEgEQKUrgCdERqNtCCeXpNa8JuaGEmcpj8AgKUAoOVc
sHAesemZYuLU2O+kLzk0pl13
=eh7B
-----END PGP SIGNATURE-----

 

[Allen Browne]

Try something like this:

PriceSum: DSum("[Price]", "OrderItems",
"([ItemGroupCode] = '" & [ItemGroupCode] &
"') AND "([OrderNumber] = " & Nz([OrderNumber],0) & ")")

 

[RichardM]

Thanks a lot, it worked very well.

Rich

I have a problem using DSum in query with multiple criteria.
I have a query with following fields OrderNo and ItemGroupCode. On third

[quoted text clipped - 5 lines]

In some odd reason it sums sup all rows not according to criteria specified
rows. If I Dsum with one criteria it works but not with two.

It is incorrectly formatted. Try this:

DSum("Price", "OrderItems", "ItemGroupCode = '" & ItemGroupCode & "' AND
OrderNumber = " & OrderNumber)

HTH,