DSum syntax issue

What is wrong with:

Me.txtShippedQty = DSum("[ShippedQty]", "tblReady", "[ReadyID] = " & Forms!frmPlanted.subfrmReady!txtReadyID) And ("[Bay] = " & Forms!frmPlanted.subfrmReady!txtBay)

--Steve
SteveL13Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Are these values you're trying to get to on a Subform? If so:

Me.txtShippedQty = DSum("[ShippedQty]", "tblReady", "[ReadyID] = " & Forms!frmPlanted.subfrmReady.Form!txtReadyID & " And [Bay] = " & chr(34) & Forms!frmPlanted.subfrmReady.Form!txtBay & chr(34) & ")"

You must add the Form qualifier after the subfrmReady - and also be sure that "subfrmReady" is the name of the Subform CONTROL on frmPlanted. Access will name them different things, depending on how you insert the control, so be sure of that.
"Expected list separator or )"  error
You don't need the & and "" around the last parentheses:

Me.txtShippedQty = DSum("[ShippedQty]", "tblReady", "[ReadyID] = " & Forms!frmPlanted.subfrmReady.Form!txtReadyID & " And [Bay] = " & chr(34) & Forms!frmPlanted.subfrmReady.Form!txtBay & chr(34))

That last parentheses is the mate to the first open parentheses immediately after DSum ...
0
 
Ess KayEntrapenuerCommented:
what are you trying to do
0
 
SteveL13Author Commented:
Sum qty where

Me.txtShippedQty = DSum("[ShippedQty]", "tblReady", "[ReadyID] = " & Forms!frmPlanted.subfrmReady!txtReadyID) And ("[Bay] = " & Forms!frmPlanted.subfrmReady!txtBay)

and

("[Bay] = " & Forms!frmPlanted.subfrmReady!txtBay)

the subform subfrmReady is a subform in frmPlanted
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Ess KayEntrapenuerCommented:
Me.txtShippedQty = DSum

(

     "[ShippedQty]",
       "tblReady",
        "[ReadyID] = " & Forms!frmPlanted.subfrmReady!txtReadyID

)

And ("[Bay] = " & Forms!frmPlanted.subfrmReady!txtBay)




i dont get it, i think you want this instead

Me.txtShippedQty = DSum

(

"[ShippedQty]", "tblReady", "[ReadyID] = " & Forms!frmPlanted.subfrmReady!txtReadyID & "[Bay] = " & Forms!frmPlanted.subfrmReady!txtBay

)

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If [Bay] is a Text field:

Me.txtShippedQty = DSum("ShippedQty", "tblReady", "[ReadyID] = " & Forms!frmPlanted.subfrmReady!txtReadyID) & " AND [Bay] = '" & Forms!frmPlanted.subfrmReady!txtBay) & "'")

Open in new window


Note I changed some of the syntax as well - I added an ampersand and double quote just before the AND keyword. I also removed the square brackets from ShippedQty, since it's already enclosed in double quotes.
0
 
als315Commented:
Me.txtShippedQty = DSum("[ShippedQty]", "tblReady", "[ReadyID] = " & Forms!frmPlanted.subfrmReady!txtReadyID & " And [Bay] = " & Forms!frmPlanted.subfrmReady!txtBay & ")"

Open in new window

if [ReadyID] and [Bay] are numeric
or
Me.txtShippedQty = DSum("[ShippedQty]", "tblReady", "[ReadyID] = " & Forms!frmPlanted.subfrmReady!txtReadyID & " And [Bay] = " & chr(34) & Forms!frmPlanted.subfrmReady!txtBay & chr(34) & ")"

Open in new window

if [ReadyID] is numeric and [Bay] is text
0
 
SteveL13Author Commented:
Both ShippedQty and Bay are numeric.  

This does not work...

Me.txtShippedQty = DSum("[ShippedQty]", "tblReady", "[ReadyID] = " & Forms!frmPlanted.subfrmReady!txtReadyID & " And [Bay] = " & chr(34) & Forms!frmPlanted.subfrmReady!txtBay & chr(34) & ")"

"Expected list separator or )"  error
0
 
Dale FyeCommented:
going back to my recommendation from yesterday and numerous times in the past.  Split your criteria out of the domain function, so that you can test it.

strCriteria = "[ReadyID] = " & Forms!frmPlanted.subfrmReady!txtReadyID) _
                   & " And [Bay] = " & Forms!frmPlanted.subfrmReady!txtBay
debug.print strCriteria
Me.txtShippedQty = DSum("[ShippedQty]", "tblReady", strCriteria)

That should work, if [bay] is numeric.  Use the following if it is text:

strCriteria = "[ReadyID] = " & Forms!frmPlanted.subfrmReady!txtReadyID) _
                   & " And [Bay] = '" & Forms!frmPlanted.subfrmReady!txtBay & "'"
0
All Courses

From novice to tech pro — start learning today.