So this is my first time posting, so please bear with me. I am trying to do a DSum for the first time and obviously it is beyond me. i don't exactly know where i went wrong, so any help you can provide is greatly appreciated.
i have attached an image of what i currently have, i hope that sheds some light. But currently i would like to sum up all the [Ord_Qty] by [ABC] classification. Order Qty resides on one table, and ABC on another (dont know if that causes a problem). But what i currently have below is not working, or giving me the expected results.
Ord_Qty = Number
ABC = Short Text
Ord_Qty: DSum("[Ord_Qty]","qsel_Test","[ABC]="&[ABC])
There is a date element i want to incorporate and the reason i am not using simple group and sum function. But if you have a better idea please feel free to chime in, im not fixed on one method.
ideally, i would like this data to show me the totals for "last" month. My historicals include everything since 08/01/2015. But i would like the totals to come up with just 01/01/2017. I will never have the order for the month i am currently in just FYI. That doesn't come into the data set till the month is over. Obviously, i am trying to avoid having to go in there every time and make it "rolling" and automated.
So for the date portion, i did :
DateAdd("m",-1,DateSerial(Year(Date()),Month(Date()),1))
So i was thinking of doing something like this but i cant get the dsum function to work regardless of the date element:
DSum("[Ord_Qty]","qsel_Test","[ABC]="&[ABC] AND [Standard_Date]>=DateAdd("m",-1,DateSerial(Year(Date()),Month(Date()),1))
my next iteration is to incorporate a "rolling" 3 month (Nov,Dec,Jan). I figured if i can get the last month down, the rolling 3 month should be simple right? Anyhow, if ANYBODY can help i would really appreciate it.
i have attached an image of what i currently have, i hope that sheds some light. But currently i would like to sum up all the [Ord_Qty] by [ABC] classification. Order Qty resides on one table, and ABC on another (dont know if that causes a problem). But what i currently have below is not working, or giving me the expected results.
Ord_Qty = Number
ABC = Short Text
Ord_Qty: DSum("[Ord_Qty]","qsel_Test","[ABC]="&[ABC])
There is a date element i want to incorporate and the reason i am not using simple group and sum function. But if you have a better idea please feel free to chime in, im not fixed on one method.
ideally, i would like this data to show me the totals for "last" month. My historicals include everything since 08/01/2015. But i would like the totals to come up with just 01/01/2017. I will never have the order for the month i am currently in just FYI. That doesn't come into the data set till the month is over. Obviously, i am trying to avoid having to go in there every time and make it "rolling" and automated.
So for the date portion, i did :
DateAdd("m",-1,DateSerial(Year(Date()),Month(Date()),1))
So i was thinking of doing something like this but i cant get the dsum function to work regardless of the date element:
DSum("[Ord_Qty]","qsel_Test","[ABC]="&[ABC] AND [Standard_Date]>=DateAdd("m",-1,DateSerial(Year(Date()),Month(Date()),1))
my next iteration is to incorporate a "rolling" 3 month (Nov,Dec,Jan). I figured if i can get the last month down, the rolling 3 month should be simple right? Anyhow, if ANYBODY can help i would really appreciate it.
