Solved

Dsum and Max

Posted on 2016-09-19
10
42 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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 13

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 13

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
 
LVL 34

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
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.

 
LVL 34

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 6

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 34

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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.

911 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

19 Experts available now in Live!

Get 1:1 Help Now