Solved

Dsum and Max

Posted on 2016-09-19
10
50 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 35

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 35

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 7

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 35

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

778 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