DSUM with multiple criteria including Null

[chrisdd]

Hi,

Can someone please look at the DSUM expression below and tell me where I'm going wrong

I need to search for stock in a database based on the day it was added - this works

Me.Result = DSum("[SubTotal]", "Stock", "[DateAdded]<= # " & Format$(Me.DateInput, "mm/dd/yyyy") & "#")

However whenever I put in criteria to remove stock that has been sold and will therefore have an OrderID asscoiated with it

Me.Result = DSum("[SubTotal]", "Stock", "[DateAdded]<= # " & Format$(Me.DateInput, "mm/dd/yyyy") & "#" and isNull([OrderID]))



I get the access can't find the field error even though the field is in the database.

Thanks

[ArviLaanemets]

What about Nz([OrderID],0) = 0

[CarlettoFed]

The original code

Me.Result = DSum("[SubTotal]", "Stock", "[DateAdded]<= # " &  Format$(Me.DateInput, "mm/dd/yyyy") & "#" and isNull([OrderID]))

must be changed to

Me.Result = DSum("[SubTotal]", "Stock", "[DateAdded]<= # " &  Format$(Me.DateInput, "mm/dd/yyyy") & "# And IsNull([OrderID])")

or

Me.Result = DSum("[SubTotal]", "Stock", "[DateAdded]<= " & CLng(Me.DateInput) & " And IsNull([OrderID])")

if the OrderID field is found in the Stock table.