We help IT Professionals succeed at work.

Getting an error on another DSUm

SteveL13
SteveL13 asked
on
=Nz(DSum("Qty","tblWarehouseTransTranx","PartN = " & Chr(34) & [txtPartN] & Chr(34) & " And WarehouseID = 'AT'"),0)

Can someone help?
Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:


=Nz(DSum("Qty","tblWarehouseTransTranx","PartN = " & Chr(34) & [txtPartN] & Chr(34) & " And WarehouseID = " & Chr(34) & "AT" & Chr(34) ),0)

mx

Author

Commented:
That did not work.
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
Most likely:

[txtPartN]

should be:

Me.[txtPartN]

  but without some context, it's hard to say what your problem is.

Jim.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Nz(DSum("Qty", "tblWarehouseTransTranx", "PartN = " & Chr(34) & Me.txtPartN & Chr(34) & " And WarehouseID = " & Chr(34) & "AT" & Chr(34)), 0)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
IF PartN is numeric then

Nz(DSum("Qty", "tblWarehouseTransTranx", "PartN = " & Me.txtPartN  & " And WarehouseID = " & Chr(34) & "AT" & Chr(34)), 0)
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:

or better yet:

Nz(DSum("[Qty]","tblWarehouseTransTranx","PartN = " & Chr$(34) & Me.[txtPartN] & Chr$(34) & " And WarehouseID = 'AT'"),0)

Jim.

Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:

 and BTW, warehouse ID is a text field right?

Jim.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:

@JD ... < what was the link where you asked what the Top 10 questions that are asked in EE ?>

thx.mx

Author

Commented:
So far nothing works and WarehouseID is a text field.  I'm putting this in the control source of the field so I don't need the Me.
NorieAnalyst Assistant

Commented:
Have you tried using single quotes instead of double quotes?
Database Architect / Application Developer
Top Expert 2007
Commented:
Then this should work

=Nz(DSum("Qty", "tblWarehouseTransTranx", "PartN = " & Chr(34) & [txtPartN] & Chr(34) & " And WarehouseID = " & Chr(34) & "AT" & Chr(34)), 0)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
And what exactly is(are) the error you are getting ?

Author

Commented:
#Error
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Then some table or field name is likely misspelled ...
They syntax is correct ... something else is going on.

mx
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Or, using the function I provided this morning;

=Nz(DSum("Qty","tblWarehouseTransTranx","PartN = " & fnWrap([txtPartN]) & " And WarehouseID = " & fnWrap("AT")),0)


The syntax you accepted from mbizUp earlier this morning was:

= NZ(DSum("Qty", "tblForecastImports", "PartN = " & CHR(34) & [txtPartN] & CHR(34) & " And Planned = FALSE And WarehouseID = 'AM'"),0)

So modifying this should have been pretty easy.  When you said "that did not work", what did you mean, to whom were you replying.  It would be helpful, if when you respond to one of us, you indicate who you are replying to, and even include the ID # of the post, so we know who and what you are replying to.

Where is the control that you are using this as the controlsource for?  Is it in the same section of the report as the txtPartN field?




NorieAnalyst Assistant

Commented:
Is txtPartN (No?) a control on a form or a field?

If it's a control and this is in a form try using Me.txtPartN.Value instead of [txtPartN].
Most Valuable Expert 2012
Top Expert 2013

Commented:
For a control source property, your syntax looks good.

Like mx said, Check the table and field names.  Also test it in the Query Builder to make sure it runs.

SUM(Qty)
FROM tblWarehouseTransTranx
WHERE PartN = 'place your actua lPN here' And WarehouseID = 'AT'

Open in new window


Also double-check your control source for an extra = sign at the beginning.  I think Access 2007 throws those in automatically - which may cause problems if you copy/paste control sources (eg from EE questions)