?
Solved

Dsum and Max

Posted on 2016-09-19
10
Medium Priority
?
65 Views
Last Modified: 2016-09-19
Experts, is the below correct?  I dont get a syntax.  I get a #error when I add the Max part.  I had no issues with the formula until I tried to add Max.  

=DSum(Max("[Cumulative Drawn]"),"[Q_Available]","[FacilityType]=" & [facID])

thank you
0
Comment
Question by:pdvsa
[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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 18

Expert Comment

by:John Tsioumpris
ID: 41805204
Max as you are using it is wrong ...the closest is Dmax but i guess this is not you want....
Are you trying to get the sum of Max Values?
0
 

Author Comment

by:pdvsa
ID: 41805280
Hi John,
<Are you trying to get the sum of Max Values?
I am trying to get the Max of [Cumulative Drawn]  where [FacilityType]=[facID]
0
 
LVL 18

Expert Comment

by:John Tsioumpris
ID: 41805292
If only Max is what you want then
DMax("[Cumulative Drawn]"),"[Q_Available]","[FacilityType]=" & [facID])

Open in new window

0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 38

Expert Comment

by:PatHartman
ID: 41805294
=Sum(DMax("[Cumulative Drawn]"),"[Q_Available]","[FacilityType]=" & [facID])

This may be what you are looking for.  The Sum() is summarizing the data from the recordsource of the form or report.  The DMax() is a domain function that is pulling up the max value for the specified key.
0
 

Author Comment

by:pdvsa
ID: 41805334
OK I tried both however I get a "wrong number of arguments" error... thank you...
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41805336
Needs an ending ")" since you have two functions nested.  the square brackets fool me into thinking they are parentheses.
0
 

Author Comment

by:pdvsa
ID: 41805351
Pat, I acttually tried that and the error is "too many closing parent".  thats what it tells me.  Let me know what you think.  I am about to close down the computer though so might not be able to respond for a bit.
0
 
LVL 8

Accepted Solution

by:
Anders Ebro (Microsoft MVP) earned 1000 total points
ID: 41805358
John had a closing parenthesis in the middle of the statement. Try
DMax("[Cumulative Drawn]","[Q_Available]","[FacilityType]=" & [facID])

Open in new window

0
 
LVL 38

Assisted Solution

by:PatHartman
PatHartman earned 1000 total points
ID: 41805371
Looks like Anders found the missing, ")".  It was in the wrong spot.  Remove it from

"[Cumulative Drawn]")

and move it to the end of the expression.

Matching parens in long statements is a pain if you don't have intellisense to help you.  I generally copy the expression and paste it into notepad.  Then I look for all the left parens and count them.  Then look for all the right parens and count them.  Usually going through the exprssion this way highlights  extra/missing parens and forces you to look closely enough to "see" when they are misplaced.  If I still can't find the error, I break the expression apart and construct the expression (and test it) inside out.  So in this case, get it to work with the DMax().  then add the Sum(..) round the working DMax()

Having us try to find your typos is frustrating for all of us.
0
 

Author Closing Comment

by:pdvsa
ID: 41805826
Thank you Anders.  If there is any objection please let me know.  Grateful for your responses and tips how to test.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

762 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