 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / October 2005dSum Question
Hi, I am trying to use the dSum function with 3 criteria, and it doesn't seem to be working. Here is my formula that I use: =dsum("[Student Grade]","Student","[Grade Type]='FINAL' AND FORMS!FORMNAME.[STUDENTID]=[STUDENTID] AND FORMS!FORMNAME.[GRADE_YEAR]=[GRADE_YEAR]"). Please help. Thanks. > Hi, > [quoted text clipped - 7 lines] > > Please help. Thanks. Does it work if you make your form references like this: [FORMS]![FORMNAME]![STUDENTID] or [FORMS]![FORMNAME]![GRADE_YEAR] ?  SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) I tried, and it didn't work. I think the formula is ignoring the Grade Year portion of criteria. What's really bother me is I am not getting any error message. Thanks. > > Hi, > > [quoted text clipped - 17 lines] > > ? > I tried, and it didn't work. I think the formula is ignoring the > Grade Year portion of criteria. What's really bother me is I am not > getting any error message. Check the names of the controls and fields. Do they really have underscores in the name, or is the field named (for example) "Grade Year"? What about the control? SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) > I tried, and it didn't work. I think the formula is ignoring the > Grade Year portion of criteria. What's really bother me is I am not > getting any error message. What exactly makes you conclude that it's not working? Bear in mind that, if any of those fields and its matching control are Null, Null isn't equal to Null so you won't find any matching records. SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) 1) Unless you have fields named FORMS!FORMNAME.[STUDENTID] and FORMS!FORMNAME.[GRADE_YEAR], you have things backward. (Access expects Field = Value syntax, not Value = Field) 2) FORMS!FORMNAME.[GRADE_YEAR], etc is a dynamic variable. You want Access to "look up" that value at runtime. Placing it within double quotes makes it a literal value and Access will use FORMS!FORMNAME.[GRADE_YEAR] literally. You need to use concatenation to construct your criteria. You probably want something more like: =dsum("[Student Grade]","Student","[Grade Type]='FINAL' AND [STUDENTID] = " & FORMS!FORMNAME.[STUDENTID] & " AND [GRADE_YEAR] = " & FORMS!FORMNAME.[GRADE_YEAR]). This assumes [Grade_Year] and [StudentID] are numerical fields. If text, then you need to add single quotes on either side of each concatenation. HTH, SignatureGeorge Nicholson Remove 'Junk' from return address. > Hi, > [quoted text clipped - 7 lines] > > Please help. Thanks. > 1) Unless you have fields named FORMS!FORMNAME.[STUDENTID] and > FORMS!FORMNAME.[GRADE_YEAR], you have things backward. (Access > expects Field = Value syntax, not Value = Field) No, equality is equality, and it doesn't matter which order the operands are in. > 2) FORMS!FORMNAME.[GRADE_YEAR], etc is a dynamic variable. You want > Access to "look up" that value at runtime. Placing it within double > quotes makes it a literal value and Access will use > FORMS!FORMNAME.[GRADE_YEAR] literally. You need to use concatenation > to construct your criteria. No, in the domain aggregate functions Access will interpret this reference as a parameter and resolve it for you. These are not the problems in the OP's expression. SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) Thanks guys. I will try them. > > 1) Unless you have fields named FORMS!FORMNAME.[STUDENTID] and > > FORMS!FORMNAME.[GRADE_YEAR], you have things backward. (Access [quoted text clipped - 13 lines] > > These are not the problems in the OP's expression. Hi, ... I will even add that since you don't need any delimiter, the syntax is even easier letting FORMS!FormName!ControlName "inside" the criteria. And not only delimiters, but for dates, as example, you also have to format the result into a US format (in case the user use another date format, such as in an English Canadian format) and that is not even about thinking about the decimal dot which can be a coma! A nightmare! Much-much, much, better is to leave the FORMS!formName!ControlName INSIDE the criteria string, as Dirk suggested, and you don't have to think to any of these potential problems. Hoping it may help, Vanderghast, Access MVP >> 1) Unless you have fields named FORMS!FORMNAME.[STUDENTID] and >> FORMS!FORMNAME.[GRADE_YEAR], you have things backward. (Access [quoted text clipped - 13 lines] > > These are not the problems in the OP's expression. Thanks guys. I got it working. > > 1) Unless you have fields named FORMS!FORMNAME.[STUDENTID] and > > FORMS!FORMNAME.[GRADE_YEAR], you have things backward. (Access [quoted text clipped - 13 lines] > > These are not the problems in the OP's expression. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.