Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DSum syntax issue

Posted on 2014-01-17
8
Medium Priority
?
304 Views
Last Modified: 2014-01-18
What is wrong with:

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

--Steve
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 39788490
what are you trying to do
0
 

Author Comment

by:SteveL13
ID: 39788498
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
 
LVL 15

Expert Comment

by:Ess Kay
ID: 39788501
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 85
ID: 39788528
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
 
LVL 40

Expert Comment

by:als315
ID: 39788532
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
 

Author Comment

by:SteveL13
ID: 39788565
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39788567
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39789969
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question