Writing Multiple criteria in DSUM function

[wajidpharmacist007]

I want to calculate previous month total quantity where generic and category fields are the same the quantity should sum up in access query

Last Month Qty: DSum("[Quantity]","[Inventory Transactions Extended]","[Transaction Types].[Add/Remove]='Removal' and [Inventory.Generic] = " & [Generic] and [Inventory].[Category] =’" &[Category]& ”’” and [Inventory Transactions Extended].[Created Date] >#DateSerial(Year(Date()),IIf(Month(Date())=1,12,Month(Date())-1),1)# and [Inventory Transactions Extended].[Created Date] < #DateSerial(Year(Date()),Month(Date()),0)#)

In this Generic field type is number while Category field type is short text. I am trying to use this one but this is not working.
Kindly help me how to figure it out.

Thanks

 

[Gasman]

If you use a domain function, it has to be on a single domain?
You appear to be trying to look up values from more than one.
Make a query to join all tables/queries that you need and use that.
Also put your criteria into a string variable.
Debug.print that until you get it correct, the use that in the function.

 

[plog]

"[Transaction Types].[Add/Remove]='Removal' and [Inventory.Generic] = " & [Generic] and [Inventory].[Category] =’" &[Category]& ”’” and [Inventory Transactions Extended].[Created Date] >#DateSerial(Year(Date()),IIf(Month(Date())=1,12,Month(Date())-1),1)# and [Inventory Transactions Extended].[Created Date] < #DateSerial(Year(Date()),Month(Date()),0)#)

1. Don't try and eat an elephant in one bite. Make it work with one criterion, then add a second, then a third, etc. etc. Start with something that actually works. Then slowly add to it so you know which part is the part that's the trouble.

2. DSum only knows about the datasource you tell it about in the 2nd argument. The only data the criteria has has access to is the data you pass it (e.g. Date() or a field from the query itself). It can't reference by datasource.field notation a filed not in the datasource you told it to add up.

3. & is for concatenating strings, AND is for concatenating logical comparisons within the string.

"Plog Is" & "Awesome"

"(X=True) AND (Y=False)"

I see you tried to use AND outside quote marks to string 2 variables together. Doesn't work like that.

4. I don't know if your date comparisons work in all cases. Not spending a lot of mental energy stepping through it, but I would check the edge cases---does it work in January? Does it work on the first and last day of a month? Again, might be right, but making sure dates find the correct prior month is tricky.

 

[Gasman]

Also if you use DateSerial() you do not need the # character

 

[wajidpharmacist007]

1. Don't try and eat an elephant in one bite. Make it work with one criterion, then add a second, then a third, etc. etc. Start with something that actually works. Then slowly add to it so you know which part is the part that's the trouble.

2. DSum only knows about the datasource you tell it about in the 2nd argument. The only data the criteria has has access to is the data you pass it (e.g. Date() or a field from the query itself). It can't reference by datasource.field notation a filed not in the datasource you told it to add up.

3. & is for concatenating strings, AND is for concatenating logical comparisons within the string.

"Plog Is" & "Awesome"

"(X=True) AND (Y=False)"

I see you tried to use AND outside quote marks to string 2 variables together. Doesn't work like that.

4. I don't know if your date comparisons work in all cases. Not spending a lot of mental energy stepping through it, but I would check the edge cases---does it work in January? Does it work on the first and last day of a month? Again, might be right, but making sure dates find the correct prior month is tricky.

Thanks Alot Plog, I try to apply criteria one by one.

 

[wajidpharmacist007]

I want to take last date of month that was two months before (As this month is July I want to take last date of May), I am using this formula

=DSum("[Quantity]","[Inventory Transactions Extended]","[Inventory Transactions].[Created Date]> DateAdd("d",-1,DateSerial(Year(DateAdd("m",1,Date())),Month(DateAdd("m",-1,Date())),1)) and [Inventory Transactions].[Created Date]< DateSerial(Year(Date()),Month(Date()),0) and [Transaction Types].[Add/Remove]='Removal' and [Inventory.ID] = " & [ID])

But this is returning error. As I replace DateAdd function by mentioning dates in above formula it works well.

=DSum("[Quantity]","[Inventory Transactions Extended]","[Inventory Transactions].[Created Date]> #05-31-2022# and [Inventory Transactions].[Created Date]< DateSerial(Year(Date()),Month(Date()),0) and [Transaction Types].[Add/Remove]='Removal' and [Inventory.ID] = " & [ID])

I don' know how to fix. Please guide.
Thanks

 

[arnelgp]

you try this:

=DSum("[Quantity]","[Inventory Transactions Extended]","[Inventory Transactions].[Created Date]> DateSerial(Year(Date()),Month(Date())-1,0) and [Inventory Transactions].[Created Date]< DateSerial(Year(Date()),Month(Date()),0) and [Transaction Types].[Add/Remove]='Removal' and [Inventory.ID] = " & [ID])

 

[wajidpharmacist007]

you try this:

=DSum("[Quantity]","[Inventory Transactions Extended]","[Inventory Transactions].[Created Date]> DateSerial(Year(Date()),Month(Date())-1,0) and [Inventory Transactions].[Created Date]< DateSerial(Year(Date()),Month(Date()),0) and [Transaction Types].[Add/Remove]='Removal' and [Inventory.ID] = " & [ID])

Thanks Alot Arnelgp this worked well

 

[wajidpharmacist007]

Thanks Alot Plog, I try to apply criteria one by one.

Thanks Alot Plog I applied single single step and finally succeeded. Thanks Alot for your guidance.

 

[daud]

People in the forum are very cooperative. Your assistance can ease people to a big extent please. Keep it on