dSum Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
AccessHelp said:
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.
Click to expand...

Does it work if you make your form references like this:

[FORMS]![FORMNAME]![STUDENTID]

or

[FORMS]![FORMNAME]![GRADE_YEAR]

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

Dirk Goldgar said:
AccessHelp said:
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.
Click to expand...

Does it work if you make your form references like this:

[FORMS]![FORMNAME]![STUDENTID]

or

[FORMS]![FORMNAME]![GRADE_YEAR]

?


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
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,
 
George Nicholson said:
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)
Click to expand...

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.
Click to expand...

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.
 
AccessHelp said:
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.
Click to expand...

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?
 
AccessHelp said:
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.
Click to expand...

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.
 
Thanks guys. I will try them.

Dirk Goldgar said:
George Nicholson said:
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)
Click to expand...

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.
Click to expand...

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
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

Dirk Goldgar said:
George Nicholson said:
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)
Click to expand...

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.
Click to expand...

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Thanks guys. I got it working.

Dirk Goldgar said:
George Nicholson said:
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)
Click to expand...

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.
Click to expand...

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top