Improve company productivity with a Business Account.Sign Up

x
?
Solved

Need help with DSum syntax

Posted on 2011-03-24
4
Medium Priority
?
446 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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

606 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