Dsum Date criteria

[haroon.mansha]

Hi All
I have a form with subform . I want to calculate purchase quantity with criteria of product and quanties before sale date. If i use with specified date it gives correct result.but if i use field address it ignores date criteria

Nz (dsum ("pqty","purchasequery","product=" & [sales.product] & "and clerancedate<=#31/07/2014#"),0)

Nz (dsum ("pqty","purchasequery","product=" & [sales.product] & "and clerancedate<=#" & [forms!salesm!sdate] & "#"),0)
But it is not working

Sdate is date and it is on main form

 

[JHB]

If you say the first is working, then try:

Nz (dsum ("pqty","purchasequery","product=" & [sales.product] &  " and clerancedate<=#" & forms!salesm!sdate & "#"),0)

Or

Nz (dsum ("pqty","purchasequery","product=" & [sales.product] &  " and clerancedate<=#" & forms!salesm.Form!sdate & "#"),0)

 

[haroon.mansha]

Thanks for helping. Infact it is not giving error but it is not showing correct result. It is returning 0

 

[haroon.mansha]

Infact it is not summing at all. Means last expression I used ignoring date criteria but it was giving sum. While now it is giving 0. While it should give result 10 according to data I used

 

[haroon.mansha]

If I used first impression with date written it was working fine

 

[JHB]

Okay, then try:

Nz (dsum ("pqty","purchasequery","product=" & [sales.product] &  " and clerancedate<=#" & Format([forms!salesm!sdate],"mm/dd/yyyy") & "#"),0)

 

[haroon.mansha]

Working like a charm.thanks again