DSum syntax issue

SteveL13
SteveL13 used Ask the Experts™
on
What is wrong with this:  (used on a form.  And AppliedToOpenOrdRecID and txtOpenOrderRecID are both numeric values)

Me.txtInventoryAppliedQty = Nz(DSum("[Quantity Out]", "tblInventoryDetail", "[AppliedToOpenOrdRecID] = Me!txtOpenOrderRecID, 0)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
you lack the "&" symbol and a closing parenthesis ")"  before ",0)"

if "[AppliedToOpenOrdRecID]" is number

Me.txtInventoryAppliedQty = Nz(DSum("[Quantity Out]", "tblInventoryDetail", "[AppliedToOpenOrdRecID] =" & Me.txtOpenOrderRecID), 0)

if "[AppliedToOpenOrdRecID]" is Text

Me.txtInventoryAppliedQty = Nz(DSum("[Quantity Out]", "tblInventoryDetail", "[AppliedToOpenOrdRecID] ='" & Me.txtOpenOrderRecID & "'"), 0)


note: at this point for so many instances, you should have learned this syntax already.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Steve,

<<note: at this point for so many instances, you should have learned this syntax already.>>

 I'm sorry, but I have to say I would second that.  You've been asking this same question over and over for some time now.  

 We have pointed out what your doing wrong, and also have suggested many times now that you do it this way:

 Dim strWhere as string

strWhere = "[AppliedToOpenOrdRecID] = " & Me!txtOpenOrderRecID
Me.txtInventoryAppliedQty = Nz(DSum("[Quantity Out]", "tblInventoryDetail",strWhere), 0)

which your still not doing.

Doing this allows you to put a break point or a stop in place and inspect strWhere to see exactly what the statement sees when it executes if your having a problem.

If you do this, it will help a lot and in that you'll be able to see where your mistake is.  Please start doing this.

Also please read:

https://www.experts-exchange.com/articles/12/Dlookup-and-the-Domain-Functions.html

about half way down, that article deals with putting things inside a string (which is what the WHERE argument is), including other strings.

While we want to help in every way possible, most of us like to see some effort on your part in trying to learn what you have been having a problem with.

Thanks,
Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial