Dum Help with Date criteria

[Naveen Marapaka]

Hi, kindly advise the solution here.



I have Table : Stock_Utilization

Fields : Out_Date, Dom_Regular_Pouch



I have written below code for a button in Vba and the result I am getting Dsum value without taking the date criteria.

what's wrong here.

[CODE] [/MsgBox Nz(DSum("DOM_REGULAR_POUCH", "Stock_Utilization", (Month([OUT_DATE]) = Month(Now())) And (Year([OUT_DATE]) = Year(Now())))) CODE]



regards

[Naveen Marapaka]

[CODE] MsgBox Nz(DSum("DOM_REGULAR_POUCH", "Stock_Utilization", (Month([OUT_DATE]) = Month(Now())) And (Year([OUT_DATE]) = Year(Now())))) [/ CODE]

[amrut]

Can you mention what values do you want to sum instead of code in plain English ? Search DSum in top right corner of VBA window ("Type a question for help")

[Naveen Marapaka]

ok, I will try to explain taking sample data.

Dom_regular_pouch has data as 50,100,54,8,6,68 and corresponding Out_date as 4/1/2014 , 4/25/2014, 5/1/2014, 5/1/2014, 5/2/2014, 5/3/2014

so my code should give me result as 136 which is sum of values which are current month & year

but I am getting value as 286 which is sum of all values without taking/checking date criteria which I mentioned in code.

so I want Dsum code with correct code which will check date criteria of current month & year


regards

[June7]

Criteria argument must be within quote marks, same as field and table arguments. The intrinsic Access functions should still evaluate.

Try:

MsgBox Nz(DSum("DOM_REGULAR_POUCH", "Stock_Utilization", "Format([Out_date],'yyyymm') = Format(Date(),'yyyymm')")

If you want user specified criteria, enter date into textbox on form and concatenate reference to textbox.

MsgBox Nz(DSum("DOM_REGULAR_POUCH", "Stock_Utilization", "Format([Out_date],'yyyymm') = '" & Format(Me.tbxDate,"yyyymm") & "'")

[Naveen Marapaka]

Criteria argument must be within quote marks, same as field and table arguments. The intrinsic Access functions should still evaluate.

Try:

MsgBox Nz(DSum("DOM_REGULAR_POUCH", "Stock_Utilization", "Format([Out_date],'yyyymm') = Format(Date(),'yyyymm')")

If you want user specified criteria, enter date into textbox on form and concatenate reference to textbox.

MsgBox Nz(DSum("DOM_REGULAR_POUCH", "Stock_Utilization", "Format([Out_date],'yyyymm') = '" & Format(Me.tbxDate,"yyyymm") & "'")

thank you its working for me now

Nz(DSum("DOM_REGULAR_POUCH", "STOCK_UTILIZATION_AND_OUTGOING", "Month([OUT_DATE]) = Month(Now()) And Year([OUT_DATE]) = year(Now())"))