Solved

Need help with DSum syntax

Posted on 2011-03-24
4
387 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
  • 2
4 Comments
 
LVL 142

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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero earned 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now