Solved

Dsum and Max

Posted on 2016-09-19
10
59 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 16

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 16

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 36

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 36

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 250 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 36

Assisted Solution

by:PatHartman
PatHartman earned 250 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 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