Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

Dsum and Max

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
pdvsa
Asked:
pdvsa
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
John TsioumprisSoftware & Systems EngineerCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
John TsioumprisSoftware & Systems EngineerCommented:
If only Max is what you want then
DMax("[Cumulative Drawn]"),"[Q_Available]","[FacilityType]=" & [facID])

Open in new window

0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
PatHartmanCommented:
=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
 
pdvsaProject financeAuthor Commented:
OK I tried both however I get a "wrong number of arguments" error... thank you...
0
 
PatHartmanCommented:
Needs an ending ")" since you have two functions nested.  the square brackets fool me into thinking they are parentheses.
0
 
pdvsaProject financeAuthor Commented:
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
 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
John had a closing parenthesis in the middle of the statement. Try
DMax("[Cumulative Drawn]","[Q_Available]","[FacilityType]=" & [facID])

Open in new window

0
 
PatHartmanCommented:
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
 
pdvsaProject financeAuthor Commented:
Thank you Anders.  If there is any objection please let me know.  Grateful for your responses and tips how to test.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now