By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,056 Members | 1,320 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,056 IT Pros & Developers. It's quick & easy.

DSum

P: 59
I have the the following DSum in a query
Expand|Select|Wrap|Line Numbers
  1. Totals: DSum("[RentPerAnnum]","Lease","[PropertyRef]='" & [PropertyRef] & "'")
This works well and gives me the total sum in one field of Leases attached to the PropertyRef. However, what I would like is for it only to sum the live records. I have a field [Archived] as a Yes/No check box. I cannot seem to get it to only sum the fields where [Archived] is checked as No. I've tried
Expand|Select|Wrap|Line Numbers
  1. Totals: DSum("[RentPerAnnum]","Lease","[PropertyRef]='" & [PropertyRef] & "'" AND [Archived]='False')
but it doesn't work. I've tried other ways but they don't work either. Is there another way to express a check box in the query code?
Mar 27 '17 #1

✓ answered by PhilOfWalton

Expand|Select|Wrap|Line Numbers
  1. Totals: Format(DSum("[RentPerAnnum]", "Lease", "[PropertyRef]='" & [PropertyRef] & "' AND [Archived] = False"),"Currency")
  2.  
  3.  
Phil

Share this Question
Share on Google+
5 Replies


PhilOfWalton
Expert 100+
P: 1,430
Assuming Archived is a True / False field, you are trying to compare it with a string 'False'

Try
Expand|Select|Wrap|Line Numbers
  1. Totals: DSum("[RentPerAnnum]", "Lease", "[PropertyRef]='" & [PropertyRef] & "' AND [Archived] = False")
  2.  
Note also the change of punctuation by the word AND

Phil
Mar 28 '17 #2

P: 59
Hi Phil, worked perfectly. Thank you. Is there a way to format it into currency in the code? I cannot do this on the query or text box within the form?
Mar 28 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
Expand|Select|Wrap|Line Numbers
  1. Totals: Format(DSum("[RentPerAnnum]", "Lease", "[PropertyRef]='" & [PropertyRef] & "' AND [Archived] = False"),"Currency")
  2.  
  3.  
Phil
Mar 28 '17 #4

P: 59
Thank you Phil. When your solutions are produced, it makes them look so simple!
Mar 28 '17 #5

PhilOfWalton
Expert 100+
P: 1,430
Been playing with Access for nearly 25 years. Getting too long in the tooth to learn new tricks.

Phil
Mar 28 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.