DSum and Filter Problems

[dim1962]

Hi,

I have a query created on the table named test.
In this query I have three fields: [Part No], [DateDueNo: DDN], [REQ]
DateDueNo and DDN are numerical fields and represent the Date Due
REQ is the Qty required by the Customer.
I added another field named Q where I like to have the total of REQ accumulated until each Date Due by Part No.
For it, my Q is DSum("REQ","test","[DDN]<" & [DateDueNo]) and work fine if I have only a Part No type. (Please See EX1)


Part No DateDueNo REQ Q
ABC 20090507 6
ABC 20090611 6 6
ABC 20090717 6 12
ABC 20090911 6 18
ABC 20091016 6 24
ABC 20091119 6 30
ABC 20091223 6 36
ABC 20100204 2 42

This is exactly what I need, but when I have in the table more Part No Type, the values what will show in Q will be wrong because will sum the others qty from other Part No (Please see EX2)

Part No DateDueNo REQ Q
ABC 20090507 6
ABC 20090611 6 6
ABC 20090717 6 22
ABC 20090911 6 28
ABC 20091016 6 34
ABC 20091119 6 40
ABC 20091223 6 66
ABC 20100204 2 72
DEF 20090612 10 12
DEF 20091120 20 46

I like to have the values in Q, the same like in first example and than continue with next Part No (DEF for REQ=10, Q=0 / REQ =20, Q=10)
Probably, I have to add a filter to be grouped by Part No, but I tried more possibilities and doesn’t work.
For example I tried to add in condition of DSum “And [Part No]=" & "'" & Me![Part No] & "'” and I had an Error
Please, can you write for me the entire DSUM expression what I need?, or any other solution.

PLEASE HELP!

 

[dim1962]

Again me,

Maybe exist another different way to obtain this sum.
So when I tried

DSum("REQ","test","[DDN]<" & [DateDueNo])

was ok, but if I have more different Part No, the results will be wrong because will sum qty from all the parts. That why I tried it:

DSum("REQ","test","[DDN]<" & [DateDueNo] And [Part No]=Me![Part No])

but doesn’t work.
Can you help me please?

Thanks again

 

[SOS]

Try this:

DSum("REQ","test","[DDN]< #" & [DateDueNo] & "# And [Part No]=" & [Part No])

 

[dim1962]

Thanks!

I tried it (what you recommended) so:

Q=DSum("REQ","test","[DDN]< #" & [DateDueNo] & "# And [Part No]=" & [Part No])

but after run the query, the result is "Error" (Please see Attachment)

Any idea why?

 

[SOS]

Well, your DateDueNo looks like it isn't a date, but text and so is DDN the same data type? If it is text then you would need to change it to quotes from #. And your Part "Number" looks like text too. So you would need:

Q=DSum("REQ","test","[DDN]< '" & [DateDueNo] & "' And [Part No]='" & [Part No] & "'")

 

[dim1962]

Yes DDN and DateDueNo are the same and in the table "test" DDN is a numerical field.

 

[SOS]

Yes DDN and DateDueNo are the same and in the table "test" DDN is a numerical field.

Then if it is numeric then you would need:


Q=DSum("REQ","test","[DDN]< " & [DateDueNo] & " And [Part No]='" & [Part No] & "'")

 

[dim1962]

Thanks a lot!!!

Now is ok!

 

[SOS]

Thanks a lot!!!

Now is ok!

Whew! Glad we got that sorted.