dmax value

[sdel_nevo]

Hi All

I am struggling with a dmax, i have a continuous form called "stock levels" that has this code in an unbound textbox called "stock level on hand"

=Nz(DSum("[stock holding]","stock take","productID=" & [ProductID]),0)+Nz(DSum("qty","purchaseOrders Details","productID=" & [ProductID]),0)-Nz(DSum("qty","Orders Details","productID=" & [ProductID]),0)

basically this take the value from the stock holding adds it to the sum of the qty on the purchase orders and then subtracts the amount on invoices

this works really well, but the problem i have is that i need to change this part =Nz(DSum("[stock holding]","stock take","productID=" & [ProductID]),0)

i think i need a Dmax, but i can't workout what i need to do to get the "[Stock Holding]" value based from the last "[Stock take Date]" I can't seem to work out how i can set the criteria as both the productid and the "[stock take date]"

hope this makes sense

many thanks

steve

[June7]

That will be tricky, like:

=Nz(DSum("[stock holding]","[stock take]","productID=" & [ProductID] & " AND [stock take date]=#" & DMax("[stock take date]","[stock take]","ProductID=" & [ProductID]) & "#"),0)

I am surprised your expression is working because table names with spaces are not enclosed by [].

[sdel_nevo]

Hi June

Thats brill, works like a charm many thanks

Steve