dSum Question

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

 

[Dirk Goldgar]

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.

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

[FORMS]![FORMNAME]![STUDENTID]

or

[FORMS]![FORMNAME]![GRADE_YEAR]

?

 

[Guest]

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,

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.

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)

 

[George Nicholson]

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,

 

[Dirk Goldgar]

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.

 

[Dirk Goldgar]

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?

 

[Dirk Goldgar]

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.

 

[Guest]

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

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

(please reply to the newsgroup)

 

[Michel Walsh]

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

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

(please reply to the newsgroup)

 

[Guest]

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

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

(please reply to the newsgroup)