Help with Dsum/Query in form

[nicksource]

Just a quick one,

I have a TextBox in my form, that currently has this DSum in it:

=DSum("QtyAdd","Orders","ProdID")-DSum("QtySub","Output","ProdID")

(ProdID is another TextBox in the form, QtyAdd is from the Orders table, and QtySub is from the Output table)

I'm also wanting to query it with more WHERE statements but finding I can only do this once with a DSum.

I'm wanting to effectively do this:

=DSum("QtyAdd","Orders","ProdID AND StockItem = Yes AND Arrival = Yes")-DSum("QtySub","Output","ProdID AND Dispatch = Yes")

So, WHERE the field StockItem and Arrival = Yes in the Orders table and WHERE Dispatch = Yes in the Output table.

Does anyone know how I can achieve this?

I was thinking an SQL query but don't know how to display a Query into a TextBox.

Thanks.

 

[pbaldy]

You can have multiple criteria, but you're not comparing ProdID to anything, so that part is not valid. Here's a good reference:

http://www.mvps.org/access/general/gen0018.htm

 

[neileg]

What Paul means is that

ProdID AND Dispatch = Yes

should be

ProdID= Yes AND Dispatch = Yes

 

[nicksource]

Still can't get it to work, remember that ProdID is a TextBox in the form. Here's what I tried (the form is open to ProdID '2')...

=DSum("QtyAdd","Orders","ProdID = 2 AND StockItem = Yes AND Arrival = Yes")-DSum("QtySub","Output","ProdID = 2 AND Dispatch = Yes")

 

[pbaldy]

ProdID would have to be a field in the Orders table (as would StockItem and Arrival). Is it? If so, is it a numeric field or text?

 

[nicksource]

Yep, it's a number field also.

 

[pbaldy]

Can you post a sample db? I'd be using True instead of Yes, but I think it works either way, so that should be working.

 

[neileg]

Ah, are your text boxes named the same as the fields they are bound to? Access form wizard does this by default but is casues all sorts of problems.

If you want to manipulate the values in the text boxes, you don't want to use DSum. Can you explain what you are trying to do.

 

[gemma-the-husky]

im not sure you can do this in quite this way

what data type is stockitem
(is it a yesno - if so then perhaps you need ot be using true/false)

-therefore in the where bit, you may need

"ProdID = 2 AND StockItem = true"

or even

"ProdID = 2 AND StockItem = " & true

im not sure without playing with it to see what happens

--------
also i assume prodid is numeric - if its not numeric, than that bit is probably going to fail as well.

------
try doing a normal design query that works with these criteria, and see how the sql was created - that should help idenitfy what the where clause should look like