Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need help with DSum syntax

Posted on 2011-03-24
4
Medium Priority
?
440 Views
Last Modified: 2013-11-05
I have a form field using the following calculation...

Me.txtTotThisDayThisCat = (Me.txtQty + Nz(DSum("Qty", "tblProjectDetail", "[CapCat] = '" & Me.txtCapCat & "' And [DropDueDate] = #" & Me.txtDropDueDate & "#"), 0))

This works fine except I need to add one more criteria to the end of the code to EXCLUDE records that have been flagged as complete.  Here is what I have but the calculation is returning the same result as the first line of code.  Can someone see what I'm doing wrong?

Me.txtTotThisDayThisCat = (Me.txtQty + Nz(DSum("Qty", "tblProjectDetail", "[CapCat] = '" & Me.txtCapCat & "' And [DropDueDate] = #" & Me.txtDropDueDate & "#"), 0) And [Complete] = False)

--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
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35206173
I presume you want this:

Me.txtTotThisDayThisCat = (Me.txtQty + Nz(DSum("Qty", "tblProjectDetail", "[CapCat] = '" & Me.txtCapCat & "' And [DropDueDate] = #" & Me.txtDropDueDate & "# And [Complete] = False)"), 0)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35206210
slight correction, is [complete] a boolean field ?

Me.txtTotThisDayThisCat = (Me.txtQty + Nz(DSum("Qty", "tblProjectDetail", "[CapCat] = '" & Me.txtCapCat & "' And [DropDueDate] = #" & Me.txtDropDueDate & "# And [Complete] = False")), 0)
0
 

Author Comment

by:SteveL13
ID: 35206261
[Complete] is a Yes/No field.  Neither one of the two suggestions removed the records that are complete from the calculation.  ?????
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 35206319
wrong place for the closing ")", try this

Me.txtTotThisDayThisCat = (Me.txtQty + Nz(DSum("Qty", "tblProjectDetail", "[CapCat] = '" & Me.txtCapCat & "' And [DropDueDate] = #" & Me.txtDropDueDate & "# And [Complete] = False"), 0))
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 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